Just one word of warning regarding dropping of unused indexes.

Over 10 years ago I worked for a customer where they identified an unused index on their heavy loaded system. One of the customers admin staff decided to drop that index, he was sure this really was not used anymore.

And what was the consequence: Severe performance issues!

Not what you think, the index actually was not used any more.

But all SQL statements that were involved in this table need to be re-parsed, instead of getting the execution plan from the shared pool. There were very long statements causing nasty wait events like library cache lock and so on.

It took a long time, until the system continued to run smoothly again.

So please keep in mind that this may happen as well in your environment.

Hello Bruce,

you run a very old and unsupported Oracle version… Too bad, monitoring of index usage has been improved with Oracle 12.2 ;-) You can find more info by searching the doc (or the web) about DBA_INDEX_USAGE or V$INDEX_USAGE_INFO; one more reason to think at going to Oracle 19…

For older versions you have to put the index in "monitoring" mode to raise a flag once the index is used, so this reveals you at time t1 if the index has been used since t0, when you started to monitor (but you don't know if it is 1 time or 1 billion times). Better than nothing but not very useful. This is explained in Doc ID 136642.1 "Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command"

May I stress your attention on your display name, "User_PXVM8". Note that you can very easily change it to a more human-friendly value (it takes 2 minutes), just have a look at https://community.oracle.com/mosc/kb/articles/11-how-to-edit-your-profile-and-notification-preferences#how-to-edit-your-community-username . (Other interesting document for MOSC users: https://community.oracle.com/mosc/kb/articles/30-how-to-be-a-community-star-six-behaviors-to-promote-mosc-success )

Best regards,

Bruno Vroman.