Slow queries...what am I missing?

[grrickar]

I have a database which was setup about a month ago and an application which accesses the data. When the db was first setup, a particular query was taking about 7 seconds, now the same query is taking 1-2 minutes - regardless of whether the app issues the query or if the user runs it in SQL*Plus. The amount of data has not changed drastically, so I suspected a tuning issue, but so far have not found anything. I have checked for invalid objects, objects with high extents, missing indexes, tables which haven't been analyzed, dictionary and library cache hit ratios, and still have not been able to determine the issue. I have ran an explain plan for the statement and checked it against other databases (which return the result quickly) and found that the execution path was the same (the table indexes were being used for the query). The server is RAID5 so I cannot move datafiles around to distribute I/O, but I also believe that this issue have came about earlier than after a month if it was I/O. The server is 8.0.5, and the entire db was exported with compress extents option and imported back in about a week ago. Any ideas? All clients experience these times, regardless of network or machine or application. Even running the queries on the server takes forever in SQL*Plus compared to what it used to.

[BV1963]

What is optimizer mode, rule or choose?

[grrickar]

It's CBO. Interesting thing, in the explain plan it does not mention that the object it is querying has been analyzed, but looking at the data dictionary we can see that this was done on 11/22. The other question mark is the significance of the cost=### in the explain plan. A database with the same data which is running correctly has a cost value of roughly half that of the database with the problem. I do not know if anything can be taken from that, or if that is just something Oracle uses and I shouldn't read anything into it.

[BV1963]

Verify that all objects involved in a query have statistics(Have been analyzed), including indexes.
Also , take a look in other databases and compare statistic to the one that have a problem with.
Did you run qn explain plan with rule optimization? Does it generate same execution plan? If it is diffrent try to execute your quiery with rull hint.