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