Connor,
I tried the SQL compiler trace event. Looking at the trace file I can see that the optimizer attempts to reproduce the plan in the baseline. It has two attempts and then determines it cannot reproduce the plan and so rejects the baseline.
Not sure what I am missing, but isn't the purpose of the baseline to inform the optimizer of the plan it should use? If the optimizer could parse and obtain the plan in the baseline, we would not need the baseline.
Can you clear up the process of "reproducing" the plan?
Thanks
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f0d08d998f0, ciP=0xc1dff6e0, dtCtx=0x7f0d0e365d50, smoInfo=(nil), sig=10432042122727988865, planId=3472127703
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f0d08d998f0, ciP=0xc1dff6e0, dtCtx=0x7f0d0e365d50, smoInfo=(nil), sig=10432042122727988865, planId=3472127703
SPM: statement found in SMB
SPM: finding a match for the generated plan, planId = 3472127703
SPM: setup to add new plan to existing plan baseline, sig = 10432042122727988865, planId = 3472127703
SPM: sql stmt=select
SPM: planId's of plan baseline are: 3083109073
SPM: using qksan to reproduce, cost and select accepted plan, sig = 10432042122727988865 cntRepro = 0
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3083109073
SPM: planId in plan baseline = 3083109073, planId of reproduced plan = 4109224122
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
SPM: baseline plan:
SPM: generated non-matching plan:
------- END SPM Plan Dump -------
SPM: plan reproducibility round 2 (hinted OFE only)
SPM: using qksan to reproduce accepted plan, planId = 3083109073
SPM: planId in plan baseline = 3083109073, planId of reproduced plan = 1696282644
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
SPM: baseline plan:
SPM: generated non-matching plan:
------- END SPM Plan Dump -------
SPM: couldn't reproduce any enabled+accepted plan so using the cost-based plan, planId = 3472127703
SPM: kkopmUnsupportedOrFakeBinds - pos=1 oac=0x11bbf3ea8 dty=1 oacflg=0x3 oacfl2=0x1000010 bnd=0xc0ab1b20 bndfir=(nil) bndflgs=0x20 name=1
SPM: using qksan to check for successful recursive parse
SPM: recursive parse succeeded, sig = 10432042122727988865, new planId = 3472127703
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f0d08c83130, ciP=0x11bbf35b8, dtCtx=0x7f0d0e365d50, smoInfo=(nil), sig=10432042122727988865, planId=3472127703
SPM: add new plan: sig = 10432042122727988865, planId = 3472127703
SPM: failed to auto-capture, sig = 10432042122727988865, planId = 3472127703
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f0d08c83130, ciP=0x11bbf35b8, dtCtx=0x7f0d0e365d50, smoInfo=(nil), sig=10432042122727988865, planId=3472127703
November 18, 2021 - 3:06 am UTC
SPM stores a plan that is our preference for use. It does not stop the standard parse.
So a query arrives, we parse it.
We now see if that plan we just came up with matches any existing (accepted) plans we've stored with SPM.
If it does, we go ahead and use it
If it does not, we store it as an un-accepted plan, and choose one of the accepted plans.
But what if the *accepted* plan we want to use can *not* be used.
eg You stored a plan that used an index, and the index is no longer present. We *want* to use an accepted plan, but we cant. We "failed to reproduce" the plan.