DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: how to find which indexes are NOT being used

  1. #1

    Talking

    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.

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width