Monitoring index usage

Oracle gives you a nice way of determining which indexes are used or not, so you get rid of the indexes that are not being used.
Index monitoring can be started or stopped with ALTER INDEX command.

ALTER INDEX index_name MONITORING USAGE;
ALTER INDEX index_name NOMONITORING USAGE;

After you have started monitoring an index’s usage you can observe the statistics in v$object_usage dictionary view.

Example:

First you have to enable index monitoring.

SQL> alter index emp_email_uk monitoring usage;

Index altered.

SQL>

You can check if the monitoring is started for the index in v$object_usage view.

SQL> select index_name,monitoring,used from v$object_usage where index_name ='EMP_EMAIL_UK';

INDEX_NAME      MONITORING      USED
--------------- --------------- ---------------
EMP_EMAIL_UK    YES             NO

SQL>

As you can see the index is monitored but the USED column shows that the index has not been used yet. Next, execute your query or workload for which you want to determine index usage. In this example I am using simple query against EMPLOYEES table.

SQL>
SQL> select first_name,last_name from employees where email = 'AHUNOLD';

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Alexander            Hunold

SQL>

Now, let’s check again v$object_usage view.

SQL> select index_name,monitoring,used from v$object_usage where index_name ='EMP_EMAIL_UK';

INDEX_NAME      MONITORING      USED
--------------- --------------- ---------------
EMP_EMAIL_UK    YES             YES

SQL>

After you are done stop the monitoring with following statement.

SQL> alter index emp_email_uk nomonitoring usage;

Index altered.

SQL>

One thought on “Monitoring index usage

  1. hi,
    sometimes v$object_usage does not retrieve all monitoring indexes.
    You can use this:

    select o.owner, o.object_name,ou.start_monitoring,ou.end_monitoring,
    decode(ou.flags,1,’YES’,’NO’) used
    from sys.object_usage ou, dba_objects o
    where ou.obj#=o.object_id;

Leave a Reply