Index Usage Monitoring

Sometimes we need to know whether an index is being used or not. Oracle thought about this as well and provides us with this option.

ALTER INDEX my_schema.idx_name MONITORING USAGE; -- enable
ALTER INDEX my_schema.idx_name USAGE; -- disable

-- check stats 11g
SELECT index_name,
table_name,
monitoring,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE index_name = 'IDX_NAME'
ORDER BY index_name;

-- check stats in v12.1 +
SELECT index_name,
table_name,
monitoring,
used,
start_monitoring,
end_monitoring
FROM dba_object_usage
WHERE index_name = 'IDX_NAME'
AND owner = 'MY_SCHEMA'
ORDER BY index_name;

Just be mindful that after enabling the monitoring the optimizer will invalidate all plans associated with the table that particular index is on. Meaning, all queries will have to go through the hard parse again. After that, there will be no additional cost worth mentioning.