Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Row Chaining

Re: Row Chaining

From: Mark Powell <Mark.Powell_at_eds.com>
Date: 27 May 1998 15:34:09 GMT
Message-ID: <01bd8985$b7398de0$a12c6394@J00679271.ddc.eds.com>


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
where ( chain_cnt / num_rows ) > .002
and num_rows > 0
/

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US