Oracle Index Monitoring

The Oracle engine maintains all indexes whether it is used or not.There are so many applications out there that creates alot of indexes without knowing Maintenance of these indexes will take resources.Too many unused indexes may also slow down the system ecpecially on systems where there is write intensive.
Index monitoring is a way to identify unused indexes so that you can safely remove them.

TO START OR STOP INDEX MONITORING

Alter index INDEX_A MONITORING USAGE;
Alter inded INDEX_A NOMONITORING USAGE;

When you monitor these indexes, make sure you run this during the lifecycle of your applcation.Do not run this when your application is not running because you may not get the true picture.
The information on the monitoring of index usage will be displayed in

SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring
FROM v$object_usage
WHERE index_name = 'INDEX_A'
ORDER BY index_name;

You must connect as the object owner to see the usage.

One flaw in this method is it simply isn't granular enough. All it will tell me is that an index has been used but doesn't give a picture of how often it is used.

There is a way to do it but it is another story...:-)