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@xxxxxxxxx> 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@xxxxxxxxx>>
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