Object Management - Excessive Chaining / Migration
Chained / Migrated rows may cause I/O
performance issues since extra I/O is required to get a row. The Analyze command can be used to
determine a list of Chained / Migrated rows. This should not be
performed during regular business hours, but should be scheduled to
occur during a maintenance window.
A second methodology to determine if
these types of rows are causing problems is by reviewing the
v$sysstat statistics for 'table fetch continued row' and comparing it
to 'table fetch by rowid'. The v$sysstat is a snapshot at a single
point in time. If the percentage of rows that are continued is small
(less than 1%) then they most likely are not causing a significant performance
degradation. If the comparison percentage is significant or increases over time it may
be necessary to review each table for chained rows using the Analyze
command.
select a.value as "Continued",
b.value as "Regular", ROUND((a.value/b.value) * 100,2) as
Pctage
from v$sysstat a
, v$sysstat b
where a.name = 'table fetch continued
row'
and b.name = 'table fetch by rowid';
The following documents from Oracle
Support discuss chained / migrated rows and how to deal with them.
- Row Chaining and Row Migration [ID 122020.1]
- How to Identify, Avoid and Eliminate Chained and Migrated Rows ? [ID 746778.1]
- Monitoring Chained Rows on IOTs [ID 102932.1]
- Does Gather_table_stats Detect Chained Rows [ID 373091.1]
- Intra Block Chaining on Tables With > 255 Columns [ID 1062906.6]
- Updating a Row with More Than 255 Columns Causes Row Chaining [ID 238519.1]
- Analyze Table List chained rows Into chained_rows Gives ORA-947 [ID 265707.1]
