SQL query to identify missing hinted indexes We have a fairly large number of queries using index hints in our 11.2.0.4 database.  I recently uncovered some performance issues and found that some of the indexes referenced by the query hints were missing.  I'm concerned that there may be more cases of this, and I'm wondering if it's possible to construct a query to do the following:


Search the cursor cache for all queries with index hints
Extract the index name from each hint and search the data dictionary for the index.
Return back the name of each index that is missing.


The indexes all follow the same naming scheme: 6 letters, followed by 6 digits, followed by the string "$INDXn", where n is a digit 0-9.

Any suggestions how to approach this?
Personally, I would start with DBA_HIST_ACTIVE_SESS_HISTORY, and look at all statments that contain that type of hint. 

From there, you can pull the index name coming from that hint, and then do a lookup on dba_indexes to see if the index exists, is valid, etc. 

You should be able to do this via PL/SQL if you want to make it really fancy and do it all in one step, otherwise a few pieces of SQL and a spreadsheet application can be your friend.