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.
Oracle 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
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.