Index montoring

You easily forget about all the options you have. I came across a situation, where 39 indexes where created on a single table. This extreme number was grown over the years. There was no administration on which indexes were needed for what purpose. Good luck!

Luckily, you have index monitoring that can be turned on.

alter index MY_IDX_1 monitoring usage;

If you wait for a considerable amount of time, you will see that v$object_usage will contain information on whether the index was used:

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

Note that it is important to log on as the index owner and not as sysdba to see any information in this view. The USED column will probably (maybe) switch from NO to YES if the index is being used.

If you turn off the monitoring:

alter index MY_IDX_1 nomonitoring usage;

the END_MONITORING date field will be filled.

You should not turn off monitoring too soon, there might be monthly reports or hardly used screens that incidentally need the index.

There is a drawback on this. During statististics the index will be used. This will blurr your results. A solution might be to lock statistics during monitoring, using

exec dbms_stats.lock_table_stats(ownname=>'SCOTT',tabname=>'DEPT');

but on heavily changing tables locking statistics might be no such a good idea.