Re: Row Chaining/Migration & UNDO

maks70_at_comcast.net (MAK) wrote in message news:<b7178504.0402021334.18a29c39_at_posting.google.com>...
> 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

What you are describing here is 'Row Migration'. 'Row Chaining" is where a single row does not fit into an entire data block, so oracle stores the row in multiple blocks. An example of this would be a table that has BLOB in it that's storing a picture file or something. You mentioned that this table is 18M rows, but, how many MB or GB is it?

After performing the update, did you re-run stats and look at the chained_rows column? Note that this column shows both 'Row Migration' and 'Row Chaining'. You mentioned that the rows are tightly packed and without looking at your system, I would guess that this number has increased and you now have a truckload of migrated rows. And I would guess that this is why you generated the amount of redo you did. When Oracle performs the redo, it stores a before and after image of the changed data. To fix the row migration, you can do an 'Alter Table Move' to a new tablespace(I've done this and it's much easier than deleting and then re-inserting the rows that are migrated. Note that if you do the alter table, you will have to rebuild all your indexes on this table as Oracle will mark them as unusable.

HTH,
Pete's

The opinions here are mine and not that of my employer. Use any advice at your own risk. Received on Tue Feb 03 2004 - 08:45:38 CST