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

sybrandb wrote:
> 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.

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10743/logical.htm

Availability and Optimization of Free Space in a Data Block

Two types of statements can increase the free space of one or more data blocks: DELETE statements, and UPDATE statements that update existing values to smaller values. The released space from these types of statements is available for subsequent INSERT statements under the following conditions:

    *

      If the INSERT statement is in the same transaction and subsequent to the statement that frees space, then the INSERT statement can use the space made available.

    *

      If the INSERT statement is in a separate transaction from the statement that frees space (perhaps being run by another user), then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.

Released space may or may not be contiguous with the main area of free space in a data block. Oracle coalesces the free space of a data block only when (1) an INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece, and (2) the free space is fragmented so the row piece cannot be inserted in a contiguous section of the block. Oracle does this compression only in such situations, because otherwise the performance of a database system decreases due to the continuous compression of the free space in data blocks.

> 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.

Please explain what you mean by "rebalancing of indexes".

> 3 If you populate an empty index you force Oracle to rebalance the
> B*-tree often.

Please explain whay you mean by "rebalance the B*-tree".

> 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

-bdbafh Received on Mon Jun 19 2006 - 12:43:24 CDT