1mb del = 1bm archive? -archive generation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: 1mb del = 1bm archive? -archive generation

  1. #1
    Join Date
    Jun 2006
    Posts
    101

    1mb del = 1bm archive? -archive generation

    Lets say we have a table which is 3GB in size and contains 18 millions rows can we say deleting
    9millions rows from this table generates 2GB worth of archive logs(assuming every row is equal in size) ?

    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    no, not at all

  3. #3
    Join Date
    Jun 2006
    Posts
    101
    sorry I meant deleting 9million ( half of the records) should generate 1.5GB worth of archive!

    hmmm
    any possible way of precisely estimating or figuring out how much worth of archive it will generate?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    again, no

  5. #5
    Join Date
    Jun 2006
    Posts
    101
    any idea on whether its gone be less than 1.5GB or more ? for sure?

    what does oracle keeps as part of archive logs when deleting data ?
    the actual DML and row id's for the deleted rows?
    Last edited by khoshtip_uk; 10-24-2007 at 10:50 AM.

  6. #6
    When you delete, the before-image of the block is copied to the undo tablespace and a change is written to the archivelog that the row was deleted. The 'before' image of the block is not written to the archivelog.

  7. #7
    Join Date
    Nov 2005
    Posts
    32
    any possible way of precisely estimating or figuring out how much worth of archive it will generate?
    One possibility for you would be to estimate the redo in your test system as follows:

    From within your session:

    -- Get the starting redo size value
    SQL> select value start_value from v$mystat where statistic# = 115;

    SQL> Run_your_deletes
    -- Get the Ending redo size value

    SQL> select value end_value from v$mystat where statistic# = 115;

    Your end_value - start_value should give you an idea of how much redo (in bytes) your deletes would generate.

    Alternatively, you could do this as well on your test system to get an appoximate idea:

    From within your session:

    -- Get the starting redo size value
    SQL> select value start_value from v$mystat where statistic# = 115;

    SQL> Delete_one_row

    -- Get the mid redo size value
    SQL> select value mid_value from v$mystat where statistic# = 115;

    SQL> commit;

    -- Get the Ending redo size value
    SQL> select value end_value from v$mystat where statistic# = 115;

    ((mid_value - start_value) * (9 million) ) + ((end_value - mid_value) * no_of_commits_done) should give you an idea on how much redo might be generated for your deletes.

    Good luck.....

    http://www.dbaxchange.com
    Last edited by dbaxchangedba; 10-26-2007 at 03:36 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width