Let me start by presenting the conclusions of these articles respectively

Dominic’s conclusion

Even with multiple plans in the a baseline, if your ACS information is flushed or ages out of the cache, you’re going to have to repeat the relevant executions required to get that ACS information back. Baselines can’t act as a shortcut to bringing back in that ACS feedback.

Coskan’s conclusion

I personally think they work perfectly fine together but I also wish if Oracle gives option to hold this runtime monitoring info in SYSAUX for env where people can accommodate more data in SYSAUX. This will save a lot of time for the initial loads.

Optimizer group conclusion

If a child cursor is bind-aware, the decision to share or not is made irrespective of whether the query is controlled by SPM. But once the query and its current bind values are sent to the optimizer for optimization, SPM constrains the optimizer’s choice of plans, without regard to whether this query is being optimized due to ACS.

I like very much the optimizer group conclusion:

“SPM constrains the optimizer’s choice of plans, without regard to whether this query is being optimized due to ACS”

Yes, that’s very correct. Because ACS and SPM are playing different goals:

ACS is a feature that helps the CBO adapt itself to the input bind variable in order to generate an execution plan that best fits that bind variable. ACS, given certain conditions, kicks of independently of the presence or not of a SQL Baseline

SPM is a feature which guarantees plan stability and allow plan evolution. It ensures only accepted plan will be executed whatever the technology used by the CBO to generate the best execution plan: ACS or Cardinality Feedback (even thought that Dominic and Kerry Osborne have already investigated the Interaction of SPM and Cardinality Feedback where they both demonstrated that this interaction is not as simple as it looks).

The logic of plan selection when SPM is used follows the following diagram:

In which we can see that when the best generated CBO plan is not already inside the SQL plan baseline (i.e. plan is ENABLED and ACCEPETD) then it will not be used. Instead, it will be inserted into the SQL plan history (i.e. ENABLED and not ACCEPTED) waiting to be evolved either manually using DBMS_SPM package or automatically when the Tuning Advisor consent to do so.

What does this means all in all?

In my opinion, in order to have a good collaboration between ACS and SPM, we need to load ACS plans (we have better to do that manually than automatically because they will be immediately ENABLED and ACCEPTED) and hope that all plans generated by the CBO via ACS will match the plans we have already loaded into the SPM baseline. When the CBO comes up with a plan that is not into the SPM baseline it will not be used. Instead all ENABLED and ACCEPTED plans will compete against each other and the best plan from the Baseline will be selected for use.

The optimizer group example is largely sufficient to explain what I have stated above. The goal of this article is to start from the Coskan’s article end and present a curious observation.

A picture is worth a thousand words (in order to make this post as short as possible, select against dba_sql_plan_baseline will be referred to as pbaseline)

Two plan baselines and two shareablesql child (1 and 3) that are bind sensitive and bind aware so that when FULL bind variable (‘N1’) is used we get a FULL TABLE SCAN and when INDEX bind variable (‘Y1’) is used we get an INDEX RANGE SCAN.

The CBO comes up with a new execution plan (SQL_PLAN_13w748wknkcwd495f4ddb) which has been constrained(discarded) by the SPM baseline. This new plan has been inserted into the SPM plan history (ACCEPTED =’NO’) for future evolution. The newly generated execution plan uses a INDEX FAST FULL SCAN of the new I2 index and it resembles to:

The child cursor number 5 has gone!!! I still have only one shareable child cursor (number 3 the one for FULL TABLE SCAN) which became no bind sensitive and no bind aware. Let’s execute the case of an INDEX RANGE SCAN

Great article and thanks for sharing this info, i had a question with regards to this. Lets say if i have ACS and bind peeking turned off(SAP system recommendation), and i have a similar situation where i have a SQL that is very bind sensative…i go put 2 baseline in place(one for full scan and other for index scan, both of the enabled and accepted)…now i run the same SQL with bind variable (which likes full scan) and that gets picked up with the baseline(full scan baseline)….next i run same SQL with different bind value(which should favor index scan)…but when i run the SQL it always tends to go towards full scan, even though i have 2 baseline for it(enabled and accepted)….

is that by design or am i missing something here? is oracle not picking up my index scan baseline(with diff bind value which favro index scan) because i have ACS tunred off?

Forget a little bit your two SPM baselines. Think about the situation you have. The initial plan the CBO has produced has been optimized with the bind variable favoring table full scan. As far as this plan has been found in the SPM baseline it has been used. The second run using a bind variable favoring the index range scan has shared the previous plan (table full scan). Thanks to the use of bind variable there were no new plan optimization. When a new optimization (hard parse) will occur and this optimization will be done against a bind variable favoring the index range scan then the CBO will come up with a index scan plan which will be obviously found in your SPM and hence will be used. And so on. The next run of the same query using bind variable favoring full table scan will share the index range scan plan until a hard parse of the query.

ACS has been developed so that, under certain conditions, the above plan sharing situation, stop to share the same execution plans for different bind variables values.

Your case has nothing to do with SPM nor with ACS. It is a classical problem of plan sharing when using bind variables

Thanks for your feedback Hourim. One other question. you mentioned my case is nothing to do with SPM nor with ACS, are you saying that because i have ACS turned off? As if i do have ACS on(obvisouly with histogram in place for that column) then i do get what i should(when i put in bind that favors index scan i get that and when i change the bind that favors full scan i get full scan)….if what you said is because i have ACS turned off, is there any other way of making it the way the it suppose to with ACS turned off ( i mean index scan on varaible that favor index scan and same with FTS)….i was thinking can we put a SQL Patch on that SQL and make it bind_aware, would that fix this situation ? or do i have this competly wrong. Sorry for my ignorance, i come from a SQL Server background and do oracle on the side(only 2 years of experience). so trying to get around learning new stuff.

Before reading your blog do you recommend readers to read first the post written by Dom Brooks & Cosgan Gundogar to get the feel of the scenario about SPM & ACS. Post this blogs are understood then we can read you blog on SPM & ACS.

Hourim, This is not a complete test in order to prove that Optimizer wouldn’t choose a wrong plan when plans are baselined in SPM. Let us forget about ACS for a while. Have two plans (Index Scan and FTS) baselined and ensure the right plans are being used with two different input parameters. Then flush out the cursors from the shared area and then repeat the test. This is where I had faced performance issues. Optimizer was choosing FTS when Index scan was expected.

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.