Reporting Oracle chained row fetch data
Oracle Tips by Burleson Consulting
December 18, 2003
One important area of
Oracle DBA work is tracking chained row fetches ('table fetch continued
row' name in v$sysstat and stats$sysstat).
migrated/chained rows always cause double the I/O for a row fetch and the primary
job of the Oracle tuning professional is to reduce disk I/O. Also
As we know, we can get
"table fetch continued row" (chained row fetch) when any of these
Raw long raw, BLOB or
CLOB columns - These may manifest as chained row fetches if the
avg_row_len > db_block_size.
Tables with > 255
columns - These are stored in 255 row-pieces, and show as migrated/chained rows.
PCTFREE too small -
You did not allow enough room on the data block for the row to expand
(via SQL Update statements), causing rows to chain onto adjacent blocks.
As we know, we can reduce
migrated/chained rows by reorganizing the table with the dbms_redefinition
utility or CTAS. We can also reduce large-object related row
chaining by moving the object into a tablespace with a 32k blocksize.
It is tempting to gather the "table fetch continued row" statistic from
v$sysstat or stats$sysstat. For example, consider
column table_fetch_continued_row format 999,999,999
snap_time > sysdate-&1
newmem.snap_id = sn.snap_id
oldmem.snap_id = sn.snap_id-1
oldmem.name = 'table fetch continued row'
newmem.name = 'table fetch continued row'
newmem.value-oldmem.value > 0
avg(newmem.value-oldmem.value) > 10000
Note that the output below only reports total chained row fetches per hour,
regardless of the amount of total rows read. The existing report is
because it only reports the total chained row fetches per hour, a
meaningless number because we don't know if it is 1% or 80% of the total row
yr. mo dy Hr. TABLE_FETCH_CONTINUED_ROW
2003-10-23 08 4,462,409
2003-10-23 09 2,962,667
2003-10-23 10 7,178,844
In the example above, the 4 million chained row fetches would be
fine if we read 10 trillion rows, bad if we read 5 million rows.
In Oracle, we can get the total row fetches by
summing the v$sysstat value for name = 'table scan rows gotten'
plus 'table fetch
A more meaningful report is shown below.
Here, we only alert for those hours when total chained row fetches
exceed 5% of total row fetches.
Table Fetch migrated/chained rows Continued Row
yr. mo dy Hr. Fetched Row Percent
---------------- ----------- -------------- -----
2003-10-23 08 53,372,282 4,462,409 6%
2003-10-24 09 46,282,383 2,962,667 14%
2003-10-28 10 14,373,264 7,178,844 50%
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.