Re: Transactions requires much more UNDO space than expected....

mccmx_at_hotmail.com wrote:
> > What does the UNDO record contain? The undo record contains change
> > vectors, ie information about *what* has changed. Is that the rowid
> > plus a 'small overhead' only?
>
> As far as I was aware, all that Oracle needs to maintain when inserting
> a row is the rowid of the newly inserted row so that it can delete it
> if the transaction is rolled back. So, yes that is just the ROWID and
> overhead.
>
> > No, it constitutes the changes at block level.
> > Same for indexes, if the index needs to be re-organized internally (ie
> > a block splits into two, or a level is added), all of that goes into
> > the UNDO.
>
> Fair point.
>
> > So your 'appromixate' calculation is very inaccurate. You would need to
> > measure the UNDO generated by an one record transaction, and work from
> > there.
>
> So are you saying that I should measure the UNDO generated for the
> insertion of one row and then multiply that up by 12 Million..?
>
> > And, oh yes, I can imagine this generates 4 Gb undo, especially when
> > you should be inserting into empty indexes
> >
>
> Can you clarify what you mean by that statement, do you expect more
> UNDO generated when inserting into an empty index than a partially
> populated index. If so, why..?
>
> Thanks for your feedback....
>
> Matt

1 As far as I am aware blocks don't contain 'holes'. So if a record is deleted the block will be reorganized. Also the rowid is not stored at all.
Each block contains a 'row directory', and Oracle always reads a complete block.

2 Observing the redo per record is still unreliable (because of the rebalancing of indexes), but it is the most reliable method. So indeed you need to multiply the redo per record by the number of records. Would be nice to know whether you are less off from the actual redo by this calculation.

3 If you populate an empty index you force Oracle to rebalance the B*-tree often. If you create the index after the table has been populated, the index will be balanced right away. Always create indexes in a separate pass after importing data. One other advantage is this can be a nologging operation.

-- 
Sybrand Bakker
Senior Oracle DBA