Getting costs for hypothetical query plans

On Feb 25, 8:58*am, Tom Anderson wrote:
> 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

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.

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

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 wrote:
> 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.

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