How To Find The Unused Indexes In Oracle Database

Getting rid of unused indexes on the database will not only free up space but it will boost the DML performance as well. But if you mistakenly get rid of a used index this may impact the performance. So let's go through the following steps which will let you do this task in a safe way.

Start With The Biggest Indexes In the Database:
I usually start this job by monitoring the biggest indexes in the database which can release much space and relief much of DML overhead on their underlying tables if those indexes got dropped.
This query will show the biggest 100 indexes:

set pages 200 lines 168
col owner for a40
col SEGMENT_NAME for a45
col TABLESPACE_NAME for a35
Select * from (select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 SIZE_MB from dba_segments where SEGMENT_TYPE like 'INDEX%' order by 5 desc)where rownum <101 order by 5;

In order to get Oracle to monitor those indexes to figure out if they are used or not we should use this command:

ALTER INDEX <OWNER>.<INDEX_NAME> MONITORING USAGE;

Monitor The Biggest Indexes:
Now Let's enable monitoring on the biggest 100 indexes on the database using the output from this command:

set pages 200 lines 168
col Enable_Monitoring_Command for a100
Select * from (select 'ALTER INDEX '||OWNER||'.'||SEGMENT_NAME||' MONITORING USAGE;' Enable_Monitoring_Command,BYTES/1024/1024 SIZE_MB  from dba_segments where SEGMENT_TYPE like 'INDEX%' order by 2 desc) where rownum <101 order by 2 desc;

Executing the commands generated under "Enable_Monitoring_Command" will put the concerned indexes under monitoring.

Check the Monitored Indexes:

As a rule of thumb, you should keep those indexes under monitoring for the longest period as possible to avoid dropping an index that is being used somewhere in a weekly/monthly report.

From time to time, you have to check the monitored indexes that are reported as "USED" and remove them from the monitoring:

set linesize 200 pages 1000

col Index_NAME for a40

col TABLE_NAME for a40

col Monitored  for a9

col Used for a4

        select io.name Index_NAME, t.name TABLE_NAME,decode(bitand(i.flags, 65536),0,'NO','YES') Monitored,

        decode(bitand(ou.flags, 1),0,'NO','YES') USED,ou.start_monitoring,ou.end_monitoring

        from 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#

order by 3 desc,4;

To Un-Monitor indexes reported as "USED", use this command:

select 'ALTER INDEX <Index_owner>.'||io.name||' NOMONITORING USAGE;' INDEXES_IN_USE
from 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 decode(bitand(i.flags, 65536),0,'NO','YES')='YES' and decode(bitand(ou.flags, 1),0,'NO','YES')='YES' order by 1;

After keeping the rest of indexes under monitoring for a sufficent time (at least one month), do the following checks:

Make sure the "UNUSED" indexes are not associated with constraints:

SELECT OWNER,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM DBA_CONSTRAINTS
WHERE INDEX_NAME IN (select io.name from 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 decode(bitand(i.flags, 65536),0,'NO','YES')='YES' and decode(bitand(ou.flags, 1),0,'NO','YES')='NO');

Check that UNUSED Indexes are not appearing in Exec Plans:

Make sure the "UNUSED" indexes are not used in any recent/old SQL Statements Execution plans:

select max(TIMESTAMP),object_name from v$SQL_PLAN
where OBJECT_NAME in ('<INDEX_NAME>')
group by object_name;

select max(TIMESTAMP),object_name from DBA_HIST_SQL_PLAN
where OBJECT_NAME in ('<INDEX_NAME>')
group by object_name;

Make sure the "UNUSED" indexes are not used in any recent SQL Statements Execution plans:

Set the UNUSED Index into INVISIBLE mode for sometime:

After making sure that the "UNUSED" indexes are not associated with any constraints and they are not used anymore in execution plans, you can put them into the INVISIBLE mode for some time:

 select 'ALTER INDEX P_FZ.'||io.name||' INVISIBLE;' SET_INVISIBLE

 from 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 decode(bitand(i.flags, 65536),0,'NO','YES')='YES' and decode(bitand(ou.flags, 1),0,'NO','YES')='NO';

Setting indexes into INVISIBLE mode will make them invisible to the application and thus will not be used by any query, This is much safer and faster than dropping the index, in case you came to know that those indexes are still being used, you can easily set them back to VISIBLE mode rather than re-create them from scratch in case you dropped them. 

Lastly, Drop the UNUSED Indexes:
Once we are definitely sure that the "UNUSED" indexes are not used, then we can drop them.

This command will drop all UNUSED monitored indexes:

select 'DROP INDEX P_FZ.'||io.name||' ;' DROP_INDEX

from 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 decode(bitand(i.flags, 65536),0,'NO','YES')='YES' and decode(bitand(ou.flags,1),0,'NO','YES')='NO';