Re: UNTO TBS behavior in 9i
- From: "NEW pop.tiscali.de" <adolph.tony@xxxxxxxxxx>
- To: <daniel.fink@xxxxxxxxxxxxxx>
- Date: Fri, 26 Jan 2007 22:04:36 +0100
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
----- Original Message -----
From: Daniel W. Fink
To: adolph.tony@xxxxxxxxxx
Cc: oracle-l
Sent: Thursday, January 25, 2007 2:55 PM
Subject: Re: UNTO TBS behavior in 9i
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
Other related posts:
