Re: chained rows

From: Joan Hsieh <jhsieh_at_infonet.tufts.edu>
Date: Tue, 13 Jun 2000 15:39:08 -0400
Message-Id: <10527.109062@fatcity.com>

REM #BOH
/*
The following script lists the number of chained or migrated rows read via an index as well as the number of chained rows on a per table basis. To obtain the chained rows per table, you must have analyzed your tables with compute statistics .
*/
REM #EOH
#

ttitle 'Tables Experiencing Chaining'
col num_rows format 9999999
col owner format a15
col table_name format a20
col % chained format 99
select owner, table_name,

       nvl(chain_cnt,0)     "Chained Rows", num_rows,
     round((chain_cnt/num_rows)*100,00)  "% chained"
         from all_tables

 where owner not in ('SYS', 'SYSTEM')
   and nvl(chain_cnt,0) > 0
 order by owner, table_name;

select substr(name,1,30), value
  from v$sysstat
where name = '^Ñtable fetch continued row'

ORACLE-L_at_fatcity.com wrote:
>
> -- Hi all,
>
> Our monitoring tool reports that there are chained rows in the database.
> We analyze the schema once a week and get no rows returned from:
>
> select owner, table_name, chain_cnt/num_rows*100 "chain%"
> from dba_tables
> where num_rows <> 0 and chain_cnt <> 0;
>
> The tech person from the monitoring tool's company told us that they
> determined chained rows by 'select value from v$sysstat where name
> ='table fetch continued row';
>
> We do have a value returned from that query:
>
> VALUE
> ----------
> 24132
>
> My question is: how to find out which table has the chained row?
>
> TIA
> Robin
>
> ****************************************************************
> * Robin Li *
> * Technical Specialist, DBA Phone#: (212) 297-3073 *
> * FCG Management Services Fax#: (212) 297-4231 *
> * 333 East 38th Street, 2nd fl. E-mail: rli_at_nyp.org *
> * New York, NY 10016 *
> ****************************************************************
> --
> Author: Robin Li
> INET: rli_at_nyp.org
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Tue Jun 13 2000 - 14:39:08 CDT