Oracle Database Management - Part 2

This is the second in a series of articles on Oracle Database Management that will cover some of the things a DBA should be looking at on a regular basis.

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.

1. Row Chaining and Row Migration [ID 122020.1]
2. How to Identify, Avoid and Eliminate Chained and Migrated Rows ? [ID 746778.1]
3. Monitoring Chained Rows on IOTs [ID 102932.1]
4. Does Gather_table_stats Detect Chained Rows [ID 373091.1]
5. Intra Block Chaining on Tables With > 255 Columns [ID 1062906.6]
6. Updating a Row with More Than 255 Columns Causes Row Chaining [ID 238519.1]
7. Analyze Table List chained rows Into chained_rows Gives ORA-947 [ID 265707.1]