Oracle: Monitoring indexes

Maintaining indexes consumes resources like CPU and I/O. Knowing if an index is used can also be useful to gain disk space. You should keep in mind that the oracle engine needs a representative amount of time to be accurate (days, weeks or months depending on the life cycle of your application).


Start monitoring an index:

alter index OWNER.INDEX_NAME monitoring usage

Stop monitoring an index:

alter index OWNER.INDEX_NAME nomonitoring usage

See the list of monitored indexes for a specific owner:

SELECT table_name,
       index_name,
       monitoring
FROM   v$object_usage
WHERE  table_name in (select table_name from dba_tables where owner=UPPER('&owner'))

Show the index usage for a specific owner:

SELECT table_name,
       index_name,
       used,
       start_monitoring,
       end_monitoring
FROM   v$object_usage
WHERE  table_name in (select table_name from dba_tables where owner=UPPER('&owner'));