How to Monitor Oracle Index Usage ?

How to Monitor Oracle Index Usage
************************************

We can use the following command to determine whether a particular index is being used by Oracle.
    ALTER INDEX <index_name> MONITORING USAGE;
If the specified index is used before monitoring is disabled, then the USED column of the relevant V$OBJECT_USAGE row, is set to YES.

Let's see this in action.
First, we create a table using the CONNECT BY LEVEL clause to generate a thousand rows, with an index on the ID column.
   

 CREATE TABLE index_usage AS
    SELECT rownum id, 'xyz' || rownum name
    FROM dual
    CONNECT BY LEVEL <= 1000;

    Table created.

    CREATE INDEX index_usage_ix1 ON INDEX_USAGE (id);
    Index created.

Then we start monitoring the usage of the index and display the results from the V$OBJECT_USAGE view.
    

    ALTER INDEX index_usage_ix1 MONITORING USAGE;
    Index altered.

    SELECT monitoring, used, start_monitoring, end_monitoring
    FROM v$object_usage
    WHERE index_name = 'INDEX_USAGE_IX1';
    MONITORING USED START_MONITORING    END_MONITORING      
    ---------- ---- ------------------- ------------------- 
    YES        NO   03/22/2008 11:44:20
    1 rows selected

Next, we access the table using the index, disable the monitoring and display the results again.
    S

ELECT *
    FROM index_usage
    WHERE id = 123;
    ID                     NAME                                        
    ---------------------- -------------------
    123                    xyz123
    1 rows selected
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=10)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'INDEX_USAGE' (TABLE) (Cost=2 Card=1 Bytes=10)
       2    1     INDEX (RANGE SCAN) OF 'INDEX_USAGE_IX1' (INDEX) (Cost=1 Card=1)

    

ALTER INDEX index_usage_ix1 NOMONITORING USAGE;
    Index altered.

    SELECT monitoring, used,start_monitoring, end_monitoring
    FROM v$object_usage
    WHERE index_name = 'INDEX_USAGE_IX1';
    MONITORING USED START_MONITORING    END_MONITORING      
    ---------- ---- ------------------- ------------------- 
    NO         YES  03/22/2008 11:44:20 03/22/2008 11:47:35
    1 rows selected

This shows us that the index was used during our monitoring period.

This feature can help us to identify unused indexes which are candidates for removal. 

Removing unnecessary indexes can improve performance by reducing overhead during updates.