Friday, March 14, 2008

Tables with chain rows

Tables with chain rows are usually potentially performance bottlenecks , as oracle when needs to fetch rows from these tables my scan more than one block to get one row.
These tables are tables with rows that are highly updated changing row length. The above query finds and rebuild tables with chained rows and corresponding indexes when the chained rows count are above 10% of the table's total rows. It also gathers statistics after the rebuild.

select
  'alter table '||owner||'.'||table_name||' move pctfree 20;'
from
  dba_tables
where
  chain_cnt > 0
and owner = 'MYUSER'
and table_name='TEST'
and round((chain_cnt/num_rows)*100) >=10
union all
select
  'alter index '||owner||'.'||index_name||' rebuild'||
  case when index_type='NORMAL'
    then ' online;'
    else ';'
  end
from
  dba_indexes
where (table_owner,table_name) in
  (
    select
      owner,
      table_name
    from
      dba_tables
    where
      chain_cnt > 0
    and owner = 'MYUSER'
    and table_name='TEST'
    and round((chain_cnt/num_rows)*100) >=10
  )
union all
select
  'exec dbms_stats.gather_table_stats(ownname=>'''||owner||''',tabname=>'''||
  table_name||''',cascade=>true,estimate_percent=>5);'
from
  dba_tables
where
  chain_cnt > 0
and owner = 'MYUSER'
and table_name='TEST'
and round((chain_cnt/num_rows)*100) >=10 ;

2 comments:

  1. this doesnt rebuild any more than 1 index per table ....

    ReplyDelete
  2. Maybe you have different table and index owner.
    Furthermore you helped me to change the script in order not to repeat table rebuild and stats gathering.

    Thanks

    ReplyDelete