Taking a while to generate a query plan?

  • From: "Don Seiler" <don@xxxxxxxxx>
  • To: oracle_l <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 1 Jun 2007 15:01:33 -0500

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
--
//www.freelists.org/webpage/oracle-l


Other related posts: