Forum OpenACS Development: Tool to find missing Oracle indexes?

Posted by Janine Ohmer on 10/30/03 06:48 PM

We've been having a lot of performance problems with one of the sites we host, and today I asked if they had made sure they had all the appropriate indexes for the latest query from h*ll. It turned out they had not and did not, and now I'm wondering what other surprises may lurk out there.

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.

TIA!

2: Re: Tool to find missing Oracle indexes? (response to 1)

Posted by C. R. Oldham on 10/30/03 06:58 PM

I think Statspack is the way to go, it will show you your most offending queries. Then you can use TkProf or TOra to examine the execution plan for them.

But, no, I'm not aware of any tools that will automatically tell you -- "hey, you're missing an index here!"

3: Re: Tool to find missing Oracle indexes? (response to 1)

Posted by Dirk Gomez on 10/31/03 11:46 AM

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.

4: Re: Tool to find missing Oracle indexes? (response to 3)

Posted by Jeff Davis on 10/31/03 01:14 PM

you should also check that the foreign keys are indexed
( http://osi.oracle.com/~tkyte/unindex/index.html ) since
that can have a pretty big impact on dml.

5: Re: Tool to find missing Oracle indexes? (response to 1)

Posted by Raad Al-Rawi on 10/31/03 12:33 PM

Hi Janine

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.

<Raad>

6: Re: Tool to find missing Oracle indexes? (response to 1)

Posted by Brian Fenton on 11/03/03 11:39 AM

Hi Janine,

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.