How to monitor the unused indexes in Oracle
• Keeping a lot of indexes on a table, can affect the performance of the transaction like insert, update, delete on the table.
• 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.
• You should drop unused index on tables, monitor all indexes and check them if they are unnecessary and unused, you should drop them.
• But how will you find out what are the unused indexes? Well, index monitoring feature will come to our rescue.
How to monitor INDEXES?
To Enable monitoring
ALTER INDEX <index name> MONITORING
USAGE;
To check status of monitoring
select
INDEX_NAME,monitoring,USED,start_monitoring from v$object_usage ;
To disable monitoring
Alter index <index name> no monitoring usage;