Index usage monitoring in Oracle

Indices (or indexes, if you will) are always good, right? Well, Tom Kyte would disagree with you and so would I. In fact, indices will always impede data manipulation performance, so they should be used only where they are useful in queries. But determining if an index is useful can be sometimes pretty difficult, mainly in a complex application with thousands of different queries, some even dynamically generated.

In this case, one of the approaches is to create indices on all columns where it possibly may make sense and monitor, if they are actually ever used by the CBO planner. I will show you how to do that. This method was tested in 11g, but should work in 10g as well.

The magic is done by an ALTER INDEX indexname MONITORING USAGE.

Starting the monitoring

You will need an ALTER INDEX privilege on te indices you want to monitor, so preferrably you do it as a SYSDBA. Fire up your SQL*Plus and connect to the database. First, we will create a script to enable the monitoring. Replace the MYUSER with whatever user you want to monitor the indices for.

set heading off
set echo off
set feedback off
set pages 10000
spool startmonitor.sql
select 'alter index '||owner||'.'||index_name
 ||' monitoring usage;'
from dba_indexes
where owner ='MYUSER';
spool off

Then we simply run the script from within the console.

SQL> @startmonitor.sql

You might see some error messages when the script tries to alter a LOB index, which is not possible, just ignore that.

Now you want to keep your application running for some time. At least for it to perform all kinds of work and jobs it normally does. So for example if you have jobs planned for every hour, leave it running like that for at least an hour. If you have daily jobs, leave it on for a whole day.

Stop the monitoring

In the meantime you can prepare a script to turn monitoring off.

set heading off
set echo off
set feedback off
set pages 10000
spool stopmonitor.sql
select 'alter index '||owner||'.'||index_name
 ||' nomonitoring usage;'
from dba_indexes
where owner ='MYUSER';
spool off

After you reach your desired monitoring period, again, simply run the off-script:

SQL> @stopmonitor.sql

Results

Now, for the results. The index usage overview is stored in the v$object_usage system view. The tricky part here is, that you only see the indices for a currently logged in user. So, even if you were doing the monitoring under a SYS or some other user, now you must login as the user whose indices you were monitoring, in our example MYUSER.

When you do that, identifying those harmful indices is as simple as this:

SELECT INDEX_NAME, TABLE_NAME
  FROM V$OBJECT_USAGE
 WHERE USED='NO';

Some of those indices might be primary keys, so you don't want to be dropping those, but all other unused indices should go. Unless, of course, you know you will need them in a very near future.

UPDATE:
You can query the monitoring results for all users too, but you have to build the query yourself from system tables:

SELECT U.NAME OWNER
,      IO.NAME INDEX_NAME
,      T.NAME TABLE_NAME
,      DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING
,      decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used
,      ou.start_monitoring
,      ou.end_monitoring
from
sys.user$ u
,   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#
and u.user# = io.owner#