Oracle Row Chaining and Migration

Row chaining occurs when a row can't physically fit into an Oracle block. Another block is required to store the remainder of the row. Chaining can cause serious performance problems and is especially prevalent with those storing multimedia data or large binary objects (blobs). You should pay special attention to the DB_BLOCK_SIZE parameter when you create your database. Block sizes of 4 kilobytes or more are the norm, not the exception. 

Migration of an Oracle row occurs when a row is updated in an Oracle block and the amount of free space in the block is not adequate to store all of the row's data. The row is migrated to another physical block in the table. The problem is that the indexes that refer to the migrated row are still pointing to the block where the row used to be, and hence the table reads are doubled. Note however that full table scans will scan blocks as they come and will perform the same number of reads whether the rows are migrated or not. 

If a table has chaining problems, you can rebuild the table, specifying a larger value for the PCTFREE parameter. If the bulk of the rows currently in the table have already been updated to their full lengths, a lot of space will be wasted. The free space will be reserved for rows that will not expand any further. To eliminate this waste, you can create the table with a smaller PCTFREE parameter, load the existing data, and then run the ALTER command on the table with a larger PCTFREE.

The following query can be used to identify tables with chaining problems:

TTITLE 'Tables Experiencing Chaining'
SELECT owner, table_name,
       NVL(chain_cnt,0) “Chained Rows”
  FROM all_tables
 WHERE owner NOT IN (‘SYS’, ‘SYSTEM’)
       AND NVL(chain_cnt,0) > 0
ORDER BY owner, table_name;

The above query is useful only for tables that have been analyzed. Note the NVL function to replace a NULL with a zero — tables that have not been analyzed will appear to have been.

The following steps explain how to list all of the chained rows in any selected table:

1. Create a table named CHAINED_ROWS using the following script (taken from Oracle's utlchain.sql script):

   CREATE TABLE chained_rows (
     owner_name VARCHAR2(30),
     table_name VARCHAR2(30),
     cluster_name VARCHAR2(30),
     partition_name VARCHAR2(30),
     subpartition_name VARCHAR2(30),
     head_rowid ROWID,
     analyze_timestamp DATE
   );

2. Issue the ANALYZE command to collect the necessary statistics:

    

   ANALYZE TABLE <table_name> LIST CHAINED ROWS;

3. Query the CHAINED_ROWS table to see a full listing of all chained rows, as shown below:

   SELECT * 
     FROM chained_rows
    WHERE table_name = ‘ACCOUNT’;

   Sample Output:
   Owner_name   Table_Name    Cluster_Name    Head_Rowid   Timestamp
   —————————————————————–
   QUEST        ACCOUNT       00000723.       0012.0004    30-SEP-93
   QUEST        ACCOUNT       00000723.       0007.0004    30-SEP-93

   The following is an example of how to eliminate the chained rows:

   CREATE TABLE chained_temp AS 
       SELECT * FROM  
        WHERE rowid IN (SELECT head_rowid 
                          FROM chained_rowS 
                         WHERE table_name = ‘‘);
   DELETE FROM     WHERE rowid IN (SELECT head_rowid 
                         FROM chained_rows 
                        WHERE table_name = ‘‘);
   INSERT INTO     SELECT * FROM chained_temp;

4. Drop the temporary table when you are convinced that everything has worked properly.

    

   DROP TABLE chained_temp;

5. Clean out the CHAINED_ROWS table:

   DELETE FROM chained_rows
         WHERE table_name = ‘‘;

Even when you analyze your tables without the LIST CHAINED ROWS option (i.e., ANALYZE <table_name> COMPUTE STATISTICS;), a column of USER | ALL | DBA_TABLES called CHAIN_CNT stores the number of chained and migrated rows at the time the ANALYZE was run. Likewise, when you use DBMS_STATS to gather statistics in Oracle 9i (remember the ANALYZE command is deprecated for statistics collection in Oracle 9i) the CHAIN_CNT column is populated.

If you are using the rule-based optimizer and you have set OPTIMIZER_MODE to CHOOSE (the default), don't forget to remove the statistics from your tables and indexes using ANALYZE <table_name> DELETE STATISTICS;. 

You can also obtain the overall number of chained and migrated rows read by your instance since startup time using the V$SYSSTAT table.

SELECT SUBSTR(name, 1, 30) "Parameter", value "Value"
  FROM v$sysstat
 WHERE name = ‘table fetch continued row’;