Forum OpenACS Development: Re: Tool to find missing Oracle indexes?
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.