Taking a while to generate a query plan?

Interesting situation. Running 10.2.0.2 EE. This situation can be duplicated on our production and 3 development instances.

We have a table with monthly partitions. I'm told that "all of a sudden (today)" new queries are taking their time running against their table. Once they are run the first time, successive attempts are fine. When I say "new queries" I mean basically the same query with a literal value changed in the predicate (yes I've discussed bind vars already).

We were able to duplicate this by flushing the shared pool, and not flushing the buffer cache. Based on this, I'm inclined to believe that data/index I/O was not a major factor in the time spent waiting. The query plan itself looked great, low cardinality and cost, using the desired indexes properly.

We wait sometimes as long as 30 seconds before the query comes back.

Where might one look to investigate what is happening during this time?

--

Don Seiler
oracle blog: http://ora.seiler.us
ultimate: http://www.mufc.us
--

http://www.freelists.org/webpage/oracle-l Received on Fri Jun 01 2007 - 15:01:33 CDT