Re: In what circumstances might optimizer not choose the lowest cost?
- From: Kim Berg Hansen <kibeha@xxxxxxxxx>
- To: Sayan Sergeevich Malakshinov <malakshinovss@xxxxxxxxx>
- Date: Tue, 20 Jan 2015 15:59:47 +0100
@Sayan:
Yes, picking the right branches of the decision tree is a heck of a complex
job for the optimizer... And most likely the real deep underlying causes
for "buggy behaviour" like Doc ID 4112254.8 that Mauro pointed me to, often
really is when the optimizer mistakenly eliminates certain branches that
shouldn't have been eliminated.
Why a hint then can force the optimizer down a path it wouldn't have
considered otherwise, that's more a mystery to me. I can better understand
when a hint is ignored because the optimizer already has chosen a path for
which the hint is not applicable.
I may (perhaps) get smarter about that when I try a 10053 (which I am not
an expert in ;-)
@Stephen:
Actually it does not use the old execution plan (that would have been
nice), but gets me a new when I create the check constraint. If I drop the
check constraint I go back to the old plan. If I create constraint again I
get the "bad" plan. This is reproducible (also after flush) on full scale
test environment (dataguard copy in snapshot standby mode.)
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha@xxxxxxxxx
@kibeha
On Tue, Jan 20, 2015 at 3:39 PM, Sayan Sergeevich Malakshinov <
malakshinovss@xxxxxxxxx> wrote:
> Kim,
>
> > Are there known circumstances where the optimizer does NOT choose
> > the lowest cost, even though same query with a hint CAN produce a
> > plan with a lower cost?
>
> In my humble opinion, that because desired plan branch was not analyzed by
> CBO because of transformation(optimizer choose another branch on previous
> steps), but when you hinted it, CBO chose right branch.
> It's quite often reason, for example a couple days ago i had issue on
> 11.2.0.3 with join_elimination: test case -
> https://gist.github.com/xtender/f0871ffa99b1413232e6
>
> --
> Best regards,
> Sayan Malakshinov
> http://orasql.org
Other related posts:
