Finding Unused Indexes
I have inherited a large Peoplesoft database that has just over 28,000 indexes. I have a feeling that not all of these indexes are being used. I found a couple of solutions via a Google of UNUSED INDEXES ORACLE.
You can generate a script to ALTER all the indexes in a schema and then turn on monitoring. After you turn on monitoring and let it run for some usage you then can query v$object_usage to see if that index is being used or not.
My question is if I turn on monitoring how much overhead is this going to add? I don't feel comfortable with turning on monitoring for over 28,000 objects without first knowing the impact. I'll have to let monitoring run for at least a month because we run differently monthly jobs. I wish there was a simple way to see when an index was last used.
I'm running 10.2.0.3 on Solaris.
SCRIPTS below:
select
'alter index '||owner||'.'||index_name||' monitoring usage;'
from
dba_indexes
where
owner='PSDBOWNR';
spool off;
RUN QUERIES then run:
select index_name, monitoring, used from v$object_usage;