doc

    Name:   chaining.sql

    Author: Mark Gurry


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 . 

#

ttitle 'Tables Experiencing Chaining'
select owner, table_name,
       nvl(chain_cnt,0)     "Chained Rows"
         from all_tables
 where owner not in ('SYS', 'SYSTEM')
   and nvl(chain_cnt,0) > 0
 order by owner, table_name;

 
select 'Chained or Migrated Rows ='|| value
  from v$sysstat
where name = ‘table fetch continued row’;