Row Chaining/Migration & UNDO

Hello Experts!!

I am using 9.2.0.3 database on AIX and using AUM(Automatic UNDO... )

I have table with 18M rows and its size is 23GB. Most blocks allocated to this table are probably tightly packed. i.e. no room for UPDATEs to column values. ( longer than what currently exist ).

I ran a single column ( larger % of values were NULL ) update statement. This update statement ran for long long time and generated 20GB of undo. This was the only statement running on the database.

I looked at the statspack report. Most waits were 'db file %' reads. There was high count for "table fetch continued row". Which lead me to believe that there was row chaining going on..

Now question is, Should oracle generate this much UNDO with just single column value being updated?

As I understand row chaining, If oracle does not find a space for the updated column in the existing block, it removes the column value from that block and copies value in the new block and put the pointer in the old block. So in this case , Oracle should have generated undo for two transactions 1) Update column value from NULL to 50 character update & 2) Update of pointer in old block & copy of old column value to new block ) and may be some other system related update. In any case this should not have generated 20GB of undo space.

Can some body explain why its generating this huge amount of UNDO?

Thanks
Max Received on Mon Feb 02 2004 - 15:34:57 CST