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

mccmx_at_hotmail.com wrote:
> 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

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?
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.
So your 'appromixate' calculation is very inaccurate. You would need to measure the UNDO generated by an one record transaction, and work from there.
And, oh yes, I can imagine this generates 4 Gb undo, especially when you should be inserting into empty indexes

-- 
Sybrand Bakker
Senior Oracle DBA
.