How to monitor index usages in the database

You maintain a large database that contains thousands of indexes. As part of your proactive
maintenance, you want to determine if any indexes are not being used. You realize that unused indexes have a detrimental impact on performance, because every time a row is inserted, updated, and deleted, the corresponding index has to be maintained. This consumes CPU resources and disk space. If an index isn’t being used, it should be dropped.

So here is the solution:

Use the ALTER INDEX...MONITORING USAGE statement to enable basic index monitoring. The following example enables index monitoring on an index named F_REGS_IDX1:

SQL> alter index f_regs_idx1 monitoring usage;

The first time the index is accessed, Oracle records this; you can view whether an index has been accessed via the V$OBJECT_USAGE view. To report which indexes are being monitored and have ever been used, run this query:

SQL> select index_name, table_name, monitoring, used from v$object_usage;

If the index has ever been used in a SELECT statement, then the USED column will contain the YES value. Here is some sample output from the prior query:

INDEX_NAME   TABLE_NAME                MON   USED
-------------------------      ------------------------            --------          ------
F_REGS_IDX1     F_REGS                           YES           YES

Most likely, you won’t monitor only one index. Rather, you’ll want to monitor all indexes for a user. In this situation, use SQL to generate SQL to create a script you can run to turn on monitoring for all indexes. Here’s such a script:

set pagesize 0 head off linesize 132
spool enable_mon.sql
select
'alter index ' || index_name || ' monitoring usage;'
from user_indexes;
spool off;

To disable monitoring on an index, use the NOMONITORING USAGE clause—for example:

SQL> alter index f_regs_idx1 nomonitoring usage;

How It Works

The main advantage to monitoring index usage is to identify indexes not being used. This allows you to identify indexes that can be dropped. This will free up disk space and improve the performance of DML statements.

The V$OBJECT_USAGE view shows information only for the currently connected user. You can verify this behavior by inspecting the TEXT column of DBA_VIEWS for the  $OBJECT_USAGE definition:

SQL> select text from dba_views where view_name = 'V$OBJECT_USAGE';

Notice the following line in the output:

where io.owner# = userenv('SCHEMAID')

That line instructs the view to display information only for the currently connected user. If you’re logged in as a DBA privileged user and want to view the status of all indexes that have monitoring enabled (regardless of the user), execute this query:

select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io
,sys.obj$ t
,sys.ind$ i
,sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#;

The prior query removes the line from the query that restricts output to display information only for the currently logged-in user. This provides you with a convenient way to view all monitored indexes.