Monitoring Indexes

Index management is always key point of performance tuning in a database. It should be considered that optimum number of indexes are created.Sometimes an index is created and used once then it is forgotten. If large number of insert and update statements are committed, IO waits waits may be seen.

Using index monitoring, unused indexes can be identified.

SQL> alter index <index_name> monitoring usage;

Index monitoring period should be long enough because sometimes indexes are created only for batch jobs, so if the batch job is not started index may be seen unused.

To disable monitoring specific index:

SQL> alter index <index_name> nomonitoring usage;

To view which indexes are monitored:

SQL> select * from v$object_usage;