Re: In what circumstances might optimizer not choose the lowest cost?

a good appoach is to have a trace from the good plan and a trace from the bad plan and do a diff on it.
Since you can reproduce the good plan by using bind variable peeking (rather than the hint) that should be do able. Might save you some time.

Thanks

Lothar

Am 20.01.2015 um 15:59 schrieb Kim Berg Hansen:
> _at_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 ;-)
>
> _at_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_at_gmail.com <mailto:kibeha_at_gmail.com>
> _at_kibeha
>
>
> On Tue, Jan 20, 2015 at 3:39 PM, Sayan Sergeevich Malakshinov
> <malakshinovss_at_psbank.ru <mailto:malakshinovss_at_psbank.ru>> 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 <http://orasql.org/>
>
>

-- 






---
Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft.
http://www.avast.com

--
http://www.freelists.org/webpage/oracle-l