Re: Getting costs for hypothetical query plans
From: joel garry <joel-garry_at_home.com>
Date: Fri, 25 Feb 2011 09:20:31 -0800 (PST)
Message-ID: <d5656d6a-9f2a-4dce-9a9d-aeb094bf82b8_at_k15g2000prk.googlegroups.com>
On Feb 25, 8:58 am, Tom Anderson <t..._at_urchin.earth.li> 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
Date: Fri, 25 Feb 2011 09:20:31 -0800 (PST)
Message-ID: <d5656d6a-9f2a-4dce-9a9d-aeb094bf82b8_at_k15g2000prk.googlegroups.com>
On Feb 25, 8:58 am, Tom Anderson <t..._at_urchin.earth.li> 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/2009/02/basic-sql-statement-performance.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
-- _at_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/vendor-news/oracle-server-sales-hammered-again-in-q4/Received on Fri Feb 25 2011 - 11:20:31 CST