An UPDATE statement increases the amount of DATA in a ROW so that the Row NO LONGER FITS in to its DATA BLOCK.
Oracle tries to find another Blockwith enough free space to hold the entire row.If such block is available Oracle moves the entire ROW to the NEW BLOCK.Oracle keeps the original Row piece of a Migrated row row to POINT to the NEW BLOCK containing the actual row.The ROWID of the MIGRATED rows does not change.INDEXES are not updated and they point to the ORIGINAL row LOCATION.
Migration and Chaining have negative effects on performance.
INSERT and UPDATE statements that cause migration and chaining perform very poorly since due to additional PROCESSING.
Queries that use an Index to select migrated or chained rows must perform additional I/O's.
Migration is caused due to less PCTFREE set.There is not enough place in the BLOCK for UPDATES.To avoid migration all Tables that are updated should have there PCTFREE set in such a way that there is enough space within the BLOCKS for UPDATES.
How to Identify Row Chaining and row Migration
we can detect the Migrated or chained rows in a TABLE or CLUSTER by using the following methods.
Using ANALYZE,REPORT.TXT and V$VIEWS.
Before doing this Analyze create the table that can hold chained rows.Execute UTLCHAIN.SQL script found in the ($ORACLE_HOME)/rdbms/admin directory, this can be run to create the CHAINED_ROWS table or else create a simillar table with same column and datatypes and sizes as the CHAINED_ROWS table.
SQL>ANALYZE TABLE SCHEMA_NAME.TABLE_NAME LIST CHAINED ROWS;
SQL>SLECT owner_name,table_name,head_rowid from chained_rows where TABLE_NAME = 'YOUR_TABLE_NAME';
REPORT.TXT and V$VIEWS:
The Migrated or chained rows can be detected by checking the "table fetch continued row" statistic in V$SYSSTAT or in REPORT.TXT.
Note:REPORT.TXT is a file created during UTLBSTAT and UTLESTAT analyze.
How to Eliminate Migrated and Chained Rows:
It is VERY important to understand the distinction between migrated and chained rows. Row chaining is UNAVOIDABLE but row migration can be MANAGED and resolved through reorganization.
Note:Chaining if its going to be in all Tables then it becomes design issue and properly the DBlock size is done.
Else to avoid row chaining for very few occasions if you increase the DBlock size the effects of it as follows.
Space in the Buffer cache will be wasted if you are doing random access to small rows and have a large block size .For Eg an Block size of 8 KB and a 50 byte row size the wasted space will be 7950 bytes in the buffer cache in an random access.
The steps invloved are:
1)Analyze the table ....list chained rows
2)Copy the rows to another table
3)Delete the rows from the original table
4)insert the rows from step 2 back to original table.
Step 4 eliminates the Migrated rows because Migration only occurs during an UPDATE.
The migrated rows can be cleaned up with SQL plus script also.
Regular pruning of chained rows will improve the performance of your I/O subsystem.
Increase the PCTFREE to avoid migrated rows and read the
excellent article telling how to reorganize the problems of the nature we are dealing.