We've received a lot of questions about how adaptive cursor sharing (ACS) and SQL plan management (SPM) interact. We discussed this briefly in one of the original SPM posts, but in this post, we'll explain the concepts of how the two features interact in more details, and show an example.
The simplest way to reason about the interaction is to remember that they are responsible for two different tasks. ACS controls whether or not a child cursor is shared on a particular execution. For each execution of the query, ACS considers the current bind values and decides if an existing child cursor can be shared or if the optimizer should be given the chance to find a better plan for the current bind values. SPM controls which plans the optimizer may choose. 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.
Let's look at a small example. There are many different ways to load plans into SPM, but for simplicity, we will manually load the plans from the cursor cache. I am using a modified version of the EMPLOYEES table from the sample HR schema - the table has been modified so that it has more rows, with more skew on the job column (there is only one president, and relatively few VPs), and there is also an index defined on it. This modified table is called EMPLOYEES_ACS (which can be created using this script). But just to give you an idea of the data distribution, here are the row counts and job distribution:
We will be working with a simple query that joins this table, filtered on job_id, to DEPARTMENTS and aggregates the results:
We are using the BIND_AWARE hint, to expedite the process of getting bind-aware cursors into the cursor cache.
If we run the query with three different bind values, AD_PRES, AD_VP, and SA_REP, the optimizer chooses three different plans.
To make things interesting, let's consider what happens if we load only two of these plans into SPM. After running the query with the bind values AD_PRES and SA_REP, there are two child cursors with different plans. Let's load these plans into SPM.
Now if we run the query with the three different bind values, SPM will constrain the optimizer to pick from the two accepted plans in the SQL plan baseline. Let's run the query with the same sequence of bind values again (AD_PRES, AD_VP, SA_REP), and see the plans that we pick for each:
For this bind value, we pick the same plan that we picked without the SQL plan baseline in the mix. This is because this was one of the plans that we loaded into the SQL plan baseline as an accepted, so the optimizer is allowed to choose it.
For this bind value, the optimizer comes up with a plan that is not in the SQL plan baseline. So instead we pick the best accepted plan, which uses a hash join. The cost-based plan the optimizer came up with is added to the SQL plan baseline, but it will not be considered until it has been evolved.
Finally lets run with the last value SA_REP.
As you would expect, we get the same plan here that we originally got for this bind value, since that was one of the plans that we loaded into SPM.
Since the second and third execution picked the same plan, there is now only one shareable cursor in the cursor cache for this plan hash value. And that cursor will now match bind values with a similar selectivity to AD_VP or SA_REP (or anything in-between).
If you are playing around with SPM and ACS, either with our demo or your own, there are a few potential surprises to keep in mind:
We hope that this small example clears up some of the confusion about how these two features interact. There are many different ways to load plans into a SQL plan baseline, and to get bind-aware cursors into the cursor cache, which can cause small changes in the behavior. If you have specific questions, please post them as a comment.