How is an index's usage monitored?

How is an index's usage monitored?

Rating:

The V dynamic performance view displays statistics about index usage. It can be used to determine whether or not an index is used during database transactions. If it is found that the index is not used, it can be dropped. Dropping unused indexes eliminates unnecessary overhead and consequently enhances the performance of Data Manipulation Language (DML) statements.

As soon as the ALTER INDEX IndexName MONITORING USAGE; statement is executed for an index, Oracle starts monitoring the index's usage. The ALTER INDEX IndexName NOMONITORING USAGE; statement is used to stop the usage monitoring.

The V dynamic performance view contains a column named USED whose value is YES or NO, depending on whether or not the index has been used during the monitoring period. The view also contains the start and stop times of the index's usage monitoring period. The view's MONITORING column indicates whether or not the index's usage is currently being monitored.

Each time the ALTER INDEX IndexName MONITORING USAGE; statement is executed for an index, the V dynamic performance view is reset for the index. The previous usage information is cleared, and a new start time is recorded in the view. When the ALTER INDEX IndexName NOMONITORING USAGE; statement is executed for the index, the usage monitoring stops and the stop time is recorded in the view.

Rating: