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

<mccmx_at_hotmail.com> wrote in message
news:1150713626.701409.142440_at_u72g2000cwu.googlegroups.com...
> Oracle 9.2.0.6 EE on W2K.
>
> As part of a table maintenance operation I am copying the contents of a
> working table into a main table with the following command:
>
> INSERT INTO PS_TL_PAYABLE_TIME SELECT * FROM PS_TY_TL_PAY_T_TMP;
>
> Based the the following info:
>
> PS_TY_TL_PAY_T_TMP row count: 12 Million
> PS_TY_TL_PAY_T_TMP avg row len: 191 bytes
>
> Indexes on the main table (ps_tl_payable_time):
>
> INDEX_NAME COLUMN_NAME
> PSATL_PAYABLE_TIME SEQ_NBR
>
> PSBTL_PAYABLE_TIME FROZEN_DATE
>
> PSCTL_PAYABLE_TIME EMPLID,TRC,DUR
>
> PSDTL_PAYABLE_TIME DUR,TRC
>
> PS_TL_PAYABLE_TIME EMPLID, EMPL_RCD,DUR,SEQ_NBR
>
> I would have exptected the UNDO required for this statement to be the
> ROWID (10 bytes) for every row in the table plus the ROWID for every
> row in the index plus a small overhead.
>
> That works out as:
>
> 12,000,000 * 10 bytes = 120Mb for the table
> 12,000,000 * 10 bytes = 120Mb for each index
>
> => Approx 720Mb of UNDO.
>
> Does that 'approximate' calculation seem correct..? Because in
> practice that query manages to fill up a 4Gb UNDO tablespace...
>
> Am I missing something here...?!?!
>
> Thanks
>
> Matt
>

A bit late getting to this, but

    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.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html