How To Monitor Index Usage In Oracle

Most of the time we are skeptical of the index usage. Though the index is created for the table, we may not be able to determine if it is actually used.
Inorder to determine if the index is used, we can use the MONITORING USAGE clause in the alter index statement.

ALTER INDEX ind1 MONITORING USAGE;

We can monitor the index for an hour, day, week or any period of the time.

Once the monitoring is started, whenever the index is accessed, it will be marked as used in the V$OBJECT_USAGE view.

SELECT v.index_name, v.table_name,
v.monitoring, v.used,
start_monitoring, end_monitoring
FROM v$object_usage v, user_indexes u
WHERE v.index_name = u.index_name;

Each time the MONITORING USAGE clause is a specified, the V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify NOMONITORING USAGE, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next ALTER INDEX … MONITORING USAGE statement is issued, the view information is left unchanged.

This is a very efficient method to find if the index is used. If not used for a long period, probably we can plan for dropping the index depending on the application.But monitoring index usage imposes potential performance problem. So we have to be very careful while using this clause.

Article by Divya

Like Tech Doubts on FB for similar updates in your FB wall : https://www.facebook.com/TechDoubts