WHY IS INDEX MONITORING REQUIRED?

Keeping a lot of indexes on a table, can affect the performance of the transaction like insert, update, delete on the table. i.e if we are inserting a new ROW, a new entry will be added to the indexes on that table. So the best way is to remove the unused indexes from the tables. But how will you find out what are the unused indexes?? Well, index monitoring feature will come to our rescue.

ALTER INDEX MONITORING USAGE; — Enable monitoring of the index
ALTER INDEX NOMONITORING USAGE; — Disable monitoring of the index

v$object_usage view tracks information about the index usage.

DEMO:

Created an index:

— Run a query that will use the index .

We can see, v$object_usage is not reporting any.

Lets enable monitoring for the index.

MONITORING: YES ( It shows monitoring is enabled )
USED                :  NO (It shows index has not been used yet)

Lets run the same query that uses index:

Now USED column is changed to YES. I.e someone has used the IND_EMP index.

To view the information in the V$OBJECT_USAGE view you have to be connected as the user owner of the objects you want to monitor, otherwise, you might not find the monitoring information you are looking for.

As i have tried to access v$object_usage for the user dbatest(not the owner of that index), it is not returning any rows.

Well there is a workaround :

Create the view as below:( AS SYS USER)

Now try to view the information from ALL_OBJECT_USAGE table from dbatest user:

Voilla!!, I can see it 🙂

How to Disable monitoring:

CONCLUSION: So to find to unused indexes in the database, Enable monitoring for the indexes in the database for a period of time, then get the usage report either from v$object_usage or ALL_OBJECT_USAGE. Analyze the report with application team and take a decision to remove the unused ones.