Index usage monitoring in oracle database

Suppose your index is not being used by SQL query with cost-based optimizer. Index monitoring allow you to easy identify the unused indexes (that are not being used) in oracle database.

You can use alter index index_name monitoring command to enable the oracle index usage monitoring.

Index usage monitoring

Syntax to enable monitoring: –

SQL>alter index <index-name> monitoring usage;

Once you issue alter index index_name monitoring usage command, oracle place an entry in v$object_usage view.

SQL> show user

USER is "APPS"

SQL> ALTER INDEX GL.GL_JE_HEADERS_N2 MONITORING USAGE;


Index altered.

Run the below query to identify.

SELECT table_name,
       index_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   v$object_usage
WHERE  index_name = '&Index_name'
ORDER BY <index_name>;

The data will remain in the V$OBJECT_USAGE view until another monitoring ‘window’ will be started at which point it will be replaced.

Syntax to disable monitoring-

SQL> alter index <index-name> no monitoring usage.

SQL> ALTER INDEX GL.GL_JE_HEADERS_N2 NOMONITORING USAGE;

To enable/disable monitoring index usage in bulk execute the below.

set pages 0


spool enable_oracle_index_monitoring.sql


select 'set echo on' from dual;


select 'alter index   ' || index_name || '  monitoring usage ;'
from user_indexes
order by 1;


spool disable_oracle_index_monitoring.sql


select 'set echo on' from dual;
select 'alter index   ' || index_name || '  nomonitoring usage ;'
from user_indexes
order by 1;


spool off

Warnings!!!

Be careful when dropping ‘unused’ oracle indexes from the output of v$object_usage because index might be rarely used. So, you have to leave monitoring on for some months to proper investigation about oracle indexes has actually not using in database.

Useful Post:-

ORA-00001 unique constraint violated |Autoconfig error