How to find Unused indexes?

What is index monitoring?

Oracle databases consist of several custom indexes which are of different types useful for fast result retrieval of query. There are indexes which are not at all used and created just with an assumption that optimizer going to consider it in it's plan and our application will speed up. But creating unnecessary indexes for tables consume space. So, how to figure out these unused indexes in our database?

One of the best ways to identify this is index monitoring despite its disadvantages. Index monitoring gives the complete list of used/unused indexes that we enabled for. We can enable index monitoring on any index of any schema.

Enabling index monitoring:

ALTER INDEX sales.ground_idx MONITORING USAGE;

From the moment we enabled the index monitoring it starts gathering information and puts in V$OBJECT_USAGE table. We can't conclude the index usability on one single day. index monitoring has to be enabled for some more days and in the long run only we can decide whether index can be deleted or not.

select   * from  V$OBJECT_USAGE where index_name = 'GROUND_IDX';

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
GROUND_IDX	GROUND_SELLER_DETAILS	YES	NO	7/18/2014 10:43

After few days we are going to disable the index monitoring and will see the report

Disabling index monitoring:

ALTER INDEX sales.ground_idx   NOMONITORING USAGE;

This is the result we get from v$object_usage with end_monitoring(date on which we stopped the monitoring)

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
GROUND_IDX	GROUND_SELLER_DETAILS	YES	NO	7/18/2014 10:43	9/18/2014 7:24

Now the span of two months none of my application/users used this index. So I can drip this index now.

Note: Even though I have enabled the index monitoring I am not getting any result from V$OBJECT_USAGE. I don’t find rows in this table .Why?

Reason:  As per oracle documents, V$OBJECT_USAGE displays the index usage information belong to that schema only.so any other schema apart from sales won’t get the above result. So we have to login to individual schemas to check their respective index usage details.

Scripts:

    a) Enabling index monitoring on bulk number of indexes

spool start_index_monitoring.sql

SELECT 'alter index ' || owner || '.' || index_name || ' monitoring usage;' FROM dba_indexes WHERE owner NOT IN ('SYS', 'SYSTEM','SYSMAN','DBSNMP');

SPOOL off

@start_index_monitoring.sql

b)Disabling  index monitoring on bulk number of indexes 

spool stop_index_monitoring.sql

SELECT 'alter index ' || owner || '.' || index_name || ' nomonitoring usage;' FROM dba_indexes WHERE owner NOT IN ('SYS', 'SYSTEM','SYSMAN','DBSNMP');

SPOOL off

@stop_index_monitoring.sql