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.