Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Row Chaining
Jerry, you are correct that in the real word it is not necessary to
eliminate ALL chained rows. You should eliminate chained rows from heavily
read tables where the row is likely to be accessed and some predetermined
limit, say 1/2 of 1%, has been reached, and from lightly read tables as
time permits.
The v$sysstat table provides the system wide count of how many chained rows were fetched where the name = 'table fetch by continued row'. You can calculate a system wide percentage to monitor, and below is a query find tables with chained rows that exceed some percentage. You may want to adjust the test percentage:
set echo off
rem
rem SQL*Plus script to find all tables where the number of chained rows
rem exceed some percentage, originally 0.005 or 1 in 200.
rem
rem 19980123 Dbagrp M D Powell
rem
column owner format a12
column CHAINED format 990.99 heading "Percentage"
select table_name "Table Name", owner "Owner", num_rows "Num Rows", chain_cnt "Chain Count", round((chain_cnt/num_rows) * 100,2) "CHAINED"from sys.dba_tables
Jerry Veno <jerry.veno_at_alliedsignal.com> wrote in article >>
> I'd like to hear opinions as to when row chaining is a problem and when
> I should be concerned. The books I've looked at say if any chaining at
> all occurs to fix it, but in real life, I don't think that's feasible.
> Any ideas?
>
Received on Wed May 27 1998 - 10:34:09 CDT