Re: UNTO TBS behavior in 9i

This has evolved into an interesting discussion. I have more questions:

"If you are doing multiple commits during the insert run, each committed batch remains in UNDO for the duration of 2 hours. You should reduce UNDO_RETENTION."

If that is the case, when the current INSERT process needs space it should be able to grab the existing space in UNDO TBS. It should overwrite it even though it is within the UNDO_RETENTION period because oracle has run out of space in UNDO TBS. Correct?

"You will RECONSTRUCT a consistent version of The block by applying all "undo vectors" to a current or a consistent version of the block."

If I understand this correctly, this means when an older un-updated version of the block is needed oracle gets the current updated version from memory or disk. Then it applies the undo vectors from the UNDO tbs. If it was a case of DELETE transaction which deleted say 6 rows out of 10 in a block, to produce a consistent version oracle brings the current version of the block which has 4 rows and then applies the change vectors from the UNDO tbs to 'INSERT' the deleted 6 rows to produce a consistent version? Does this mean Oracle stores the 6 deleted rows in UNDO tbs? Or it just stores the ROWIDs in UNDO and selects the old values from the 'deleted spots' in the data block which are still not overwritten?

Another clarification: You have mentioned the vectors are applied to a current or consistent version of the block. I can understand the current version, but why take a consistent version and then apply changes again?

On 1/26/07, Daniel W. Fink <daniel.fink_at_optimaldba.com> wrote:
>
> It has been a few years since I did that work, so my recollection may be a
> little rusty. If I am wrong or missing something, fellow oracle-l
> mates...jump in!
>
> An insert append uses new blocks, so there is nothing in the block to
> start with. When the insert append is finished, these 'new' blocks are
> associated with the table by updating the high water mark/segment
> information. To undo the insert append, Oracle never associates the block to
> the table. If one of those blocks is needed later, it is 'renewed' (assuming
> it was written to disk).
>
> Regards,
> Daniel Fink
>
> NEW pop.tiscali.de wrote:
>
> 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
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l