-------------------------------------------------------
--  what : rochaing.sql 
--  when : 15-MAR-2006
--   why : V$ queries for row chaining and migration
-- where : Tested with 10g 
--   how : Check for chained or migrated rows in general
--         and by table name. Cribbed from akadia.com.
--   who : David Clement
--   fix :
-----------------------------------------------------------

-- Check for 'table fetch continued row'. 
-- It should be a low percentage of the table fetches reported.
-- Remember v$sysstat is everything since the last startup.

select name, value
from v$sysstat
where name like '%table%'
/

-- Check results of last analysis of a table.
-- This is supposed to be automatic under 10g.
select chain_cnt, 
round (chain_cnt / num_rows * 100, 2) pct_chained,
avg_row_len,
pct_free,
pct_used
from user_tables
where table_name = '&1'
/