Monitor Index Usage

1 Enable index monitoring
ALTER INDEX my_index_i MONITORING USAGE;


2 Disable index monitoring
ALTER INDEX my_index_i NOMONITORING USAGE;


3 Information about the index usage can be displayed using the V$OBJECT_USAGE view

SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   v$object_usage
WHERE  index_name = 'MY_INDEX_I'
ORDER BY index_name;

4 Script to generate index monitoring command

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL temp.sql

SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" MONITORING USAGE;'
FROM   dba_indexes i
WHERE  owner      = UPPER('&1')
AND    table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

SPOOL OFF

SET PAGESIZE 18
SET FEEDBACK ON

5 Script to generate disabling index monitoring command

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL temp.sql


SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" NOMONITORING USAGE;'
FROM   dba_indexes i
WHERE  owner      = UPPER('&1')
AND    table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));


SPOOL OFF


SET PAGESIZE 18
SET FEEDBACK ON