how to find which indexes are NOT being used

[bberg@rtnyc.com]

unfortunately our architecture dynamically makes queries, so I cannot simply get a list of all of the queries being done... and I really prefer not doing sql_trace=TRUE

is there a way to determine if an index is being used? I could used bstats and see how many reads from the tablespace, but it won't tell me if i have 9 indexes there which ones are being used...

I'd like to drop unused ones.. is there an easier way?

thanks.

[grjohnson]

The most precise way I'd say would to go chasing the SQL queries that have been running on the database. Perform queries on V$SQLTEXT. Then run an explain plan over them and determine what indexes are being used that way. FREE TOAD has an easy to use explain plan feature. But toad does create to additional tables in the DB schema.

Hope this helps.

Cheers,