Detecting and Fixing Row Migration

In my previous posting, I discussed how migrated rows led to latch connection problems on a system. In this entry I will explain how I identified and removed the migrated rows, and correctly set PCTFREE on each table so that the problem will not recur.

Instead, you must use the ANALYZE command if you want to know about chained and migrated rows. DBMS_STATS only collects the statistics required by the cost-based optimizer. It doesn't populate the other statistics, including CHAIN_CNT and AVG_FREE_SPACE. So I wrote a simple script (available on my website) to work though a set of tables, and list the chained or migrated rows into a table. I sometimes choose to analyse only certain tables. The criteria in the comment are examples of criteria I add to the driving query to restrict the list of tables to be analysed.

The last column of the report is a calculated new value for PCTFREE. The amount of in a block that can be used to insert new rows (100-PCTFREE) space used is scaled back by the proportion of migrated rows.

NB: This approach doesn't take chained rows into account. Chained rows are too big to fit in a single block under any circumstances, as opposed to migrated rows that could have fitted in a block had there been space. However, Oracle counts both types and stores the result in the column CHAIN_CNT.

The script then generates certain commands for each table. First the table is rebuilt by moving it to the same tablespace.

I specify PCTFREE as 1. Usually, the whole table does not need to be rebuilt with the new higher value for PCTFREE. Most of the rows are fully populated and generally will not grow further because they have already migrated. Setting a high value for PCTFREE will simply result in leaving a lot of free space, and result in Oracle reading more blocks for the same data. Instead, I set a low value for PCTFREE in order to pack the data into the minimum number of blocks.

The new value for PCTFREE is then applied to the table. This approach is not foolproof. It is possible for existing rows, that have now been packed into the minimum number of blocks, to grow and to be migrated. However, I have found that this happens relatively rarely.

If necessary, I also reduce PCTUSED such that the sum of the new PCTFREE and the old PCTUSED does not exceed 90. The old value is shown in the comment for reference.