Re: multiple accepted plans for a give statement

If my investigations on this subject done a few years ago on an 11g release are still valid, which is most probably the case, then the SPM plans will be evaluated for usage *ONLY* when the plan produced by the CBO is different from those, accepted and enabled, in the SPM baselines. If the CBO plan is found in the SPM baseline then it will be used. If, however, the plan optimized by the CBO is not found in the SPM baseline (phv2 != plan_id) then Oracle will reproduce *ALL* the plans of the baseline which are accepted & enabled and cost them. It will end up by choosing the best-costed execution plan provided it is reproducible. Otherwise, it will fall back to the CBO plan.

https://hourim.wordpress.com/2015/10/01/oracle-optimizer-and-spm-plan-interaction/

Be aware of the parsing effect of this extra work of comparing baseline plans to choose the most effective one. The more plans (accepted and enabled) you have in the SPM baseline, the more you need that the plan produced by the CBO matches one of these plans. Otherwise, you will certainly pay a parsing penalty.

Best regards

Mohamed Houri

Le mar. 28 sept. 2021 à 14:41, Rajeev Prabhakar <rprabha01_at_gmail.com> a écrit :

> Based on this diagram, seems like it would be the
> lowest cost plan
>
>
>
> On Sep 28, 2021 at 7:58 AM, <Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>>
> wrote:
>
> If I have sql for which I have accepted more than one plan in the
> baseline, how does Oracle decide which of the accepted plans in the
> baseline to use?
>
>
>
> Jeffrey Beckstrom
>
> Greater Cleveland Regional Transit Authority
>
> 1240 W. 6th Street
>
> Cleveland, Ohio 44113
>
>
>
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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