Skip to main content

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;


Popular posts from this blog

Find and replace with sed command in Linux

Find and replace feature is always handy. It can turn into a torture when it comes to change or delete a simple constant string in a text file. There is a handy tool in linux for doing these kind of tihngs. Actually sed is not a text editor but it is used outside of the text file to make changes.