chainning.sql

rem    Name:   chaining.sql
rem
rem
rem The following script lists the number of chained or migrated rows 
rem read via an index as well as the number of chained rows on a per table
rem basis. To obtain the chained rows per table,  have analyzed 
rem 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';