Collecting index stats


A quick script to collect INDEX_STATS figures for multiple indexes, because a normal 'analyze index ... validate structure' always replaces the content of index_stats when it is run. The script below also adds the number of columns in the index to assist when looking at the compression statistics

create table IDXSTAT
as select * from index_stats;

alter table idxstat add colcnt number;

begin
for i in ( select index_name from dba_indexes
           where owner = '...') loop
  execute immediate 'analyze index iips.'||i.index_name||' validate structure';
  insert into idxstat
  select x.*, cnt from index_stats x,
   ( select count(*) cnt from dba_ind_columns
     where index_owner = '...'
     and index_name = i.index_name );
  commit;
end loop;
end;
/

select name, LF_BLKS, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE, COLCNT
from idxstat