Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: UNTO TBS behavior in 9i

Re: UNTO TBS behavior in 9i

From: NEW pop.tiscali.de <adolph.tony_at_tiscali.de>
Date: Fri, 26 Jan 2007 22:04:36 +0100
Message-ID: <004101c7418d$96a33120$0700a8c0@tonypc>


Hi Daniel,

I read through your paper. Thanks for the info. To be honest, 50% of it went over my head :-(

From experence though, I know that insert append creates must less UNDO than an insert into a table that's had many deletes. My assumption (obvious wrong - thank's for the pointers from the better informed), was that the before image was saved. This made sense to me: there is no before image for an insert append, just the blk addresses would be needed for a delete in case of a rollback - hence the decreased UNDO. I see from your paper that this is not the case. So can you please explain to me, why an insert append causes less undo.

Tony

  UNDO (both manual in 8i/9i/10g and auto in 9i/10g) entries are not before images of the blocks. They are actually change vectors containing only enough information to undo the change. For example, for an insert the undo vector is basically just the row address and a delete indicator. There are index entries and block header entries (still not complete block images) as well.

  For more details, you can read "Automatic Undo Internals" at http://www.optimaldba.com/papers/AutomaticUndoInternals.pdf

  Regards,
  Daniel Fink

  NEW pop.tiscali.de wrote:
Hi all,

I think the retention setting is a red herring here. I *think* its a real basic issue. Someone posted the suggestion to use the append hint. I reckon this should work. You need to remember that for an insert the UNDO is a copy of the before image and if your inserting into 30G of blocks that have *some space* free, then all of these blocks need to be saved as UNDO.

Maybe your PCTFREE settings need looking at. Has a lot of data been deleted - this would leave a lot of free blocks that will end up in the UNDO. Perhaps you can rebuild the table before the insert ...or even truncate it?

Just ideas, hope they help
Cheers
Tony   

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 26 2007 - 15:04:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US