 |
|
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
see:
As we know, we can get
"table fetch continued row" (chained row fetch) when any of these
conditions exist:
-
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
this report.
column table_fetch_continued_row format 999,999,999
select
to_char(snap_time,'yyyy-mm-dd HH24'),
avg(newmem.value-oldmem.value)table_fetch_continued_row
from
perfstat.stats$sysstat oldmem,
perfstat.stats$sysstat newmem,
perfstat.stats$snapshot sn
where
snap_time > sysdate-&1
and
newmem.snap_id = sn.snap_id
and
oldmem.snap_id = sn.snap_id-1
and
oldmem.name = 'table fetch continued row'
and
newmem.name = 'table fetch continued row'
and
newmem.value-oldmem.value > 0
having
avg(newmem.value-oldmem.value) > 10000
group by
to_char(snap_time,'yyyy-mm-dd HH24')
;
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
silly
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
fetches:
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
by rowid'.
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
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
and include the URL for the page.
