optimizer unable to get execution plan / sql pool blown up

Hello,

I am hoping that someone can help me with a problem I am facing on an 9.2.0.7 Oracle installation on AIX 5.1, with the optimizer not being able to determine an execution plan and the shared pool being blown up.

The problem is, that sometimes when issuing a select against a view, Oracle does not successfully parse the query. From an 10053 trace file what appears to be happening is this: First Oracle evaluates the best execution plan as usual, however after it has the "Final - All Rows Plan" it starts all over again with the PARAMETERS USED BY THE OPTIMIZER section. At this point a new child entry appears in v$sql. This then results in the shared pool filling up with thousands versions of this statement, consuming lots of cpu and memory and bringing the database virtually to a standstill. To me it appears that the optimizer is in some kind of an endless loop.

The only way I have found to resolve this so far is to recreate the view. It then appears that the optimizer finally manages to compile an execution plan and the query gets executed.

Unfortunately I can't get this behaviour consistently.

I would be grateful if someone has any idea.

Cheers
Peter

---

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
--

http://www.freelists.org/webpage/oracle-l Received on Fri Jun 02 2006 - 06:28:47 CDT