How do adaptive cursor sharing and SQL Plan Management interact?

How do adaptive cursor sharing and SQL Plan Management interact?     

By Allison on Feb 11, 2013

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.

AD_PRES:

AD_VP:

SA_REP:

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:

AD_PRES:

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.

AD_VP:

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.

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:

• The plans that are chosen by ACS impact the number of child cursors and number of hard parses that you will see for a particular sequence of bind values.  Thus, the presence of a SQL plan baseline that constrain the possible plans can cause a different number of child cursors and hard parses.  For instance, with our example, we end up with one child cursor that can be used for AD_VP, SA_REP, or bind values whose selectivity falls somewhere in-between.  Without SPM in the picture, a bind value that falls in-between may generate an additional hard parse and a new child cursor (and possibly a new plan).

• New cost-based plans chosen by the optimizer that are not in the SQL plan baseline are automatically added to it, as unaccepted plans.  However, the optimizer only has the opportunity to choose a plan when cursor sharing fails to match a cursor and a hard parse is triggered.  As mentioned in the point above, the presence of a SQL plan baseline can reduce the number of hard parses.

• When SPM updates a SQL plan baseline, the cursor built using that baseline is invalidated.  A couple of common reasons for updating the SQL plan baseline include: when a new (cost-based) plan is added to the SQL plan baseline, or when a plan in the SQL plan baseline is marked as reproduced (when it is successfully reproduced for the first time).  So if you are tinkering with this, and you can't find the cursor that you just ran with, this may be the case.  In the examples I showed above, after I loaded the plans into the SQL plan baseline, I ran the queries with each bind multiple times to get over the hump and ensure that I could display the plan after I ran the query.  This is not likely to have a significant impact on a running system, but when you are running small test cases like this, it can be a bit confusing.

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.