Forum OpenACS Development: Tool to find missing Oracle indexes?
So, are there any Oracle tools that will analyze database activity, probably via Statspack snapshots, and suggest indexes that could help? I know I can do this manually but I'm hoping for a little assistance. I took a quick look at Toad and Tora but it didn't look like either of them would do this.
But, no, I'm not aware of any tools that will automatically tell you -- "hey, you're missing an index here!"
How would you determine which indexes are missing? That's tough problem, right?
E. g. full-table scans are not necessarily evil and indexes accesses are not necessarily beneficial. Over-indexing in fact hurts, both on data insertion and update, and maybe even on retrieval.
Take statspack snapshots and tackle the worst queries day after day.
( http://osi.oracle.com/~tkyte/unindex/index.html ) since
that can have a pretty big impact on dml.
Have you looked at Quest Central (http://www.quest.com/quest_central/qco/performance_diagnostics/)?
It has a whole bunch of stuff that includes performance diagnostics. Once it's installed, you tell it to monitor performance, and it looks at hi-load / badly performing queries, and makes suggestions about how to improve them, including additional indexes.
You can get a free trial version, so see what you think.
maybe some of your indexes have gotten a bit off-kilter. Run this query to find them:
select index_name from dba_indexes where status <> 'VALID';
Also if the amount of data you have has changed recently you may need to run analyze again on all your tables and indexes.
Oracle have a good tool called DBA Studio that I think comes with the Windows client install CDs. I found it very useful for identifying troublesome sessions and their corresponding SQL. It tells you which queries are long-running and all that good stuff. It's all stuff you can find out yourself if you know the underlying queries, but DBA Studio makes it really easy (especially when you're under pressure, as you usually are when these things happen!).
In general, I am told that installing Statspack is the only way to go for reliable performance monitoring.