X

Insights into Statistics, Query Optimization and the Oracle Optimizer

How do adaptive cursor sharing and SQL Plan Management interact?

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.

Join the discussion

Comments ( 7 )
  • guest Monday, February 18, 2013

    Allison,

    when I try to follow the link for the BIND_AWARE hint I get the message "Sorry, you do not have the privileges necessary to access the page you requested. This system is available to Oracle Employees only."

    Regards

    Martin


  • Allison Monday, February 18, 2013

    So sorry, Martin. Thanks for pointing this out. The links in the post have now been fixed.


  • guest Sunday, March 3, 2013

    Hi Maria,

    please can you give me an explanation of what Oracle don't offert a way to migrate sql profile to spm such migrating outline.

    why we need create STS? hope this not just to force customer to buy a license of diagnostic/tuning :-)

    Regards


  • guest Monday, March 11, 2013

    Hi,

    You don't need to use a SQL Tuning Set (STS) to migrate a SQL profile to a SQL plan baseline. You can simple capture the plan generated by the SQL profile directly from the cursor cache using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.

    That said, if the customer is using SQL profiles then they must already have the diagnostic / tuning packs. So, no additional license would be required.

    Thanks,

    Maria


  • Tianhua Wu Tuesday, May 14, 2013

    Hi Maria,

    Do you have a solution for IN clause? For example:

    Where col_name in (:B1, :B2)

    vs

    Where col_name in (:B1, :B2, ...)

    Thanks,

    T Wu


  • ANTONY Sunday, January 29, 2017

    The reason why are we using SPM is to expect optimizer to choose consistent plans from SPM all the time.But when the cursor is aged out from shared pool, I do not see optimizer choosing a right plan from SPM for the first time... for example a FTS was used when we expect INDEX SCAN.... after the second execution of similar bind values, it is started using right plans... So i strongly believe some relationship between ACS and SPM or it could be the bug in SPM or it is working as designed where you need at least two executions of similar bind values or range of values to get right plan when cursor is aged out...

    Can optimizer team clarify my doubts?

    Thanks


  • NigelBayliss Monday, January 30, 2017

    Hi Antony,

    If you have a SQL plan baseline for a SQL statement, it doesn't matter if the SQL statement is aged out but I expect it's because the statement is being hard parsed and then (I'm guessing) you might be getting stats/cardinality feedback or an adaptive mechanism kicking in. However the best line of attack is to check to see if you have multiple SQL plan baselines for the SQL statement in question. If you have more than one plan in the plan baseline, try disabling the SQL plan baselines you don't won't used (using ALTER_SQL_PLAN_BASELINE) - and set the one you want used to enabled, accepted and fixed. There are a number of options available to you, but this is what I'd look at first.

    Regards,

    Nigel.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services