Developers keep adding indexes to the database to improve the performance. Later they find it difficult to remove the unused indexes. Index monitoring feature of Oracle can be used to identify the unused indexes and remove them.Oracle gives the capability to add monitoring to the indexes.
alter index indexname monitoring usage; The above command will mark the index for monitoring. It will add a record in the V$OBJECT_USAGE table. The columns in this table are
- Index Name
- Table Name
- Used (this says if the index is used after the monitoring was enabled)
- Monitoring (this says that monitoring is enabled or not)
- Start_monitoring (start time of monitoring)
- End_monitoring (end time of monitoring)
When the index is getting used, the USED flag against the index name will be marked to YES. So the best approach is to enable monitoring on all indexes in the database including the primary keys. After this step, access the application through the screens. Always try to cover all functionality so that maximum indexes are used.
Once the above exercise is completed, find all the indexes from the v$object_usage table which has the USED flag as NO. These indexes that are not used by the application during the monitoring time and can be dropped.
Select index_name from v$object_usage where used=’NO’;
After completing the exercise, all the monitoring on the indexes should be removed. The script for the same is
alter index indexname nomonitoring usage;
