What is row migration?
If an UPDATE statement increases the amount of data in a row
so that the row no longer fits into its data block, Oracle tries to find
another block with enough free space to hold the entire row. If such a block
is available, Oracle moves the entire row to the new block while the original
row piece is kept to point to the new block containing the actual data. The
rowid of the migrated row does not change, and indices are not updated - so
they still point to the original location.
What is the result of this?
Whenever data of a migrated row are to be read, this requires an additional
read (one for the original location just holding the pointer to the real
location, and one for the real data). Thus, migrated rows can cause acute
performance degration - the more you have the more you will feel the results.
This means, they should be corrected immediately if they are being reported.
What factors can influence this?
First, the settings for PCTUSED and PCTFREE are
the most common causes. If there was too less PCTFREE reserved
at table creation, INSERT statements occupied space in the blocks
no longer available for UPDATE s at a later time. The result is
described above: the updated row may have to be migrated.
So how to fix this?
For this, you first have to analyse your tables:
ANALYZE TABLE tablename COMPUTE STATISTICS;
SELECT num_rows,chain_cnt FROM dba_tables WHERE table_name='tablename';
|
utlchain.sql then may help you to automatically eliminate migration.
Make sure to correct PCTFREE before running this script - otherwise it is
very likely that this problem will re-occur soon.
|