Thursday, September 24, 2009

Index monitoring to identify unused indexes.

# enable index monitoring
# note issues have been reported where index monitoring gives false positives from stats collections, and false negatives when the index is accessed via foreign key.

set pagesize 0 linesize 200
spool monitoring.sql
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM','DBSNMP','DVSYS','MDSYS','WMSYS','OLAPSYS','LBACSYS','XDB','EXFSYS','CTXSYS',
'STRMADMIN','ORDSYS','TSMSYS','OUTLN','DMSYS','PERFSTAT','WK_TEST','WKSYS','ODM','UTPLSQL','OWBRT_SYS','OWBREP','DISCO_EUL','REP_OWN_PRF')
and index_type != 'LOB'
order by 1;
spool off;

# identify indexes which are monitored, but have not been used

select u.name owner
, io.name index_name
, t.name table_name
, decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring
, decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used
from
sys.user$ u
, sys.obj$ io
, sys.obj$ t
, sys.ind$ i
, sys.object_usage ou
where
i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#
and decode(bitand(i.flags, 65536), 0, 'NO', 'YES') = 'YES'
and decode(bitand(ou.flags, 1), 0, 'NO', 'YES') = 'NO'
order by 1,2,3;