Re: Getting costs for hypothetical query plans
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 26 Feb 2011 10:19:58 -0000
Message-ID: <HfydnfouX7k_TvXQnZ2dnUVZ7t6dnZ2d_at_bt.com>
Date: Sat, 26 Feb 2011 10:19:58 -0000
Message-ID: <HfydnfouX7k_TvXQnZ2dnUVZ7t6dnZ2d_at_bt.com>
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.earth.li> wrote in message news:alpine.DEB.1.10.1102251657340.16341_at_urchin.earth.li...Received on Sat Feb 26 2011 - 04:19:58 CST
> 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?