But, let me explain to you what exactly I am looking for:
I have a table with 12 indexes (I know this number is too high) and I feel
some of them may not be required due to the changes in the application queries.
So, I want figure out the unwanted indexes and drop them. Perhaps, it would
improve the performance on the table.
This application is a web based application and it generates tons of SQLs and it would
be a painful task for me to trace the instance and do the analysis.
I know Quest Software has a tool (SQL*Impact ???) that would generate a report of "unused" indexes.
I can not use this tool since I do not have direct access to the database machine.
THat is why I am asking if there any SQL query that can be run to identify "unused" indexes.
It sounds like you are looking for a silver-bullet. Unfortunately, there is no "LAST_ACCESSED" type field in the data dictionary for indicies.
You could seperate your questionable indicies into different tablespaces and then query v$filestat to see if there is much activity. However, this method would only be an educated guess because you may have a small number of hits that are valid.
or actually, another route might be to find the queries used by the application and see what fields they query off of. then take a look at all the indexes, you said there were 12. if all your queries are only using a few fields, it might make more sense to just drop the ones on fields not being used at all, or just drop them all and re-create indexes from scratch with whatever decisions you would use in that situation.