Hi,

We have a query which has a surprising plan. I assume Oracle considered
the right plan, but rejected it. Is there any way to tell it that plan,
and have it tell me the cost breakdown of it? That would really help me
figure out what's going wrong.

Thanks,
tom

--
Who would you help in a fight, Peter van der Linden or Bill Gates?

On Feb 25, 8:58*am, Tom Anderson <t... (AT) urchin (DOT) earth.li> wrote:
Sometimes the surprise is that Oracle is right.

The way to force a plan is through the use of a complete set of
hints. The performance guide in the docs has an introduction to that,
and Jonathan Lewis has a Cost Based Optimizer book that explains in
depth how it works, and there are many examples on his blog. Randolph
shows you how to see what is going on and explains how to post a
tuning request: http://oracle-randolf.blogspot.com/2...rformance.html
Often just working through the steps answers the question.

Neither, I'd run and get popcorn.

jg
--
@home.com is bogus.
"Risc and Itanium UNIX vendors again had a torrid three months, with
revenues down 19.3% as the market was squeezed by x86 and System Z."
Torrid? http://www.microscope.co.uk/news/ven...d-again-in-q4/

On Feb 25, 4:58*pm, Tom Anderson <t... (AT) urchin (DOT) earth.li> wrote:
Sounds like you want a 10053 CBO trace.
http://asktom.oracle.com/pls/apex/f?...63445044804318
http://tonguc.wordpress.com/2007/01/...053-trace-file

How have you managed to work out what the "right plan" should be ?
As a starting point you could put in a few hints that force Oracle in the
right direction - and that might be enough to get the plan you want to
examine.

If you're on 10g the leading() hint may be sufficient to get started -
/*+ leading(alias1 alias2 ... aliasn) */
where the list of aliases specifies the order you want Oracle to visit the
tables.

Unfortunately, depending on the complexity of your plan, you may have to be
more subtle to handle the complexities of subquery unnesting and view
merging. Note that this hint isn't sufficient for a production system -
but it may start you off in the right direction of reproducing the plan you
want to see.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com


"Tom Anderson" <twic (AT) urchin (DOT) earth.li> wrote