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

> Undo records have about 80 bytes overhead
>
> Undo records for array inserts can include all the
> changes to a single block, rather than having one
> undo record per row.
>
> You comment about 'the undo for the table' is just
> the rowid is close - the undo operation for a table
> insert is roughly 'go to block X rowindex entry Y and
> clear the index entry', which effectively means the undo
> is simply the rowid.
>
> At 191 bytes per row, you get about 40 rows per block,
> So you get an overhead of about 80 bytes, plus about
> 2 bytes per row (since the block id only needs to be
> stored once in the undo). Total - 160 bytes for every
> 40 rows in your table.
>
> BUT
> Index undo consists of the index key value, plus undo record
> header overhead. (The index entry may move due to block
> splits between the moment you insert it and the moment you
> decide to rollback - so you need to be able to relocate it by
> index key access, not by absolute location).
>
> Oracle can use the same 'multiple entries per undo record'
> for multiple changes to the same block. But by the time you
> get into large volumes of data, arriving OUT OF ORDER,
> then you may end up changing just one row in a leaf block
> at a time - so you get an undo overhead of 80 bytes per
> index entry. (and then there's extra undo because of the
> cost of copying block data on block splits).
>
> If you work out
> (80 + key-length) * rows in table
> for every index, you may find you get a lot closer to you
> "unexpected" 4 GB.
>
>

Jonathan,

Thanks for the detailed explanation.... (if only I could locate this kind of information in the Oracle documentation...!)

So it appears that the amount of UNDO generated during batch loads is greatly increased if there are a large number of composite indexes (as in my case) on the table.

I re-ran the archiving process after dropping the indexes and the UNDO dropped dramatically (now only 200Mb), and the process ran much faster.  Since all that UNDO generation is also protected via REDO entries, the overall I/O on the system dropped substantially.

I guess this explains why it is a good idea to do imports in 2 passes (table data first and indexes second).

Thanks very much for your time..

Matt Received on Mon Jun 26 2006 - 08:12:49 CDT