Monitoring Index Usage

Monitoring Index Usage

Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.

To start monitoring the usage of an index, issue this statement:

ALTER INDEX index MONITORING USAGE;

Later, issue the following statement to stop the monitoring:

ALTER INDEX index NOMONITORING USAGE;

The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED column whose value is YES or NO, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING column (YES/NO) to indicate if usage monitoring is currently active.
Each time that you specify MONITORING USAGE, 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.

Example:
conn home/pwd
select 'ALTER INDEX HOME.'||index_name||' MONITORING USAGE;' stmt
from dba_indexes
where owner='HOME'
and index_name like 'A%'

ALTER INDEX HOME.AUST_FINANCE_PK MONITORING USAGE;

select * from V$OBJECT_USAGE
where index_name='AUST_FINANCE_PK'

select 'ALTER INDEX HOME.'||index_name||' NOMONITORING USAGE;' stmt
from dba_indexes
where owner='HOME'
and index_name like 'A%'

ALTER INDEX HOME.AUST_FINANCE_PK NOMONITORING USAGE;