X

Insights into Statistics, Query Optimization and the Oracle Optimizer

How does SQL Plan Management match SQL statements to SQL plan baselines?

Maria Colgan
Master Product Manager

Happy New Year to all of our readers!

As more and more of your systems migrate to Oracle Database 11g, SQL Plan Management (SPM) is becoming increasingly popular as a mechanism to maintain plan stability and subsequently system performance. However, one of the key frustrations folks encounter when using SPM is that the SQL plan baseline they painstakingly created is not used. So, how does Oracle decide whether or not to use an existing SQL plan baseline?

SQL statements are matched to SQL plan baselines using the signature of the SQL statement. A signature is a unique SQL identifier generated from the normalized SQL text (uncased and with whitespaces removed). This is the same technique used by SQL profiles and SQL patches. This means, if you issue identical SQL statements from two different schemas they would resolve to the same SQL plan baseline. Now, you might be thinking that this approach could lead to all sorts of performance problems because you may not want the same plan used by each schema. But remember a SQL plan baseline can have multiple plans for the same SQL statement and SPM records the parsing schema along with each plan.

Let’s take a look at this in action to fully understand why it is not a problem using the following SQL text,

Select count(s.quantity_sold) total_sales
From Sales s
Where s.prod_id=30;

We begin in the SH sample schema and execute the statement to see the cost-based plan.

Let’s create a SQL plan baseline for this SQL statement. We can do this by capturing the plan directly from the cursor cache into a SQL plan baseline, using the DBMS_SPM package. But first we need the SQL_ID for our statement, which we can get from V$SQL.

We can confirm our SQL plan baseline exists and our preferred plan is both enabled and accepted by querying DBA_SQL_PLAN_BASELINES.

By re-executing the query we can see that the SQL plan baseline is now being used even when we change the case and white spaces in our statement.

Let’s now connect as the sample schema SCOTT. We have created an identical copy of the sales table in the SCOTT schema but it does not have the IND_SALES_PROD_QTY_SOLD index. If we execute the identical SQL statement from the SCOTT schema should it use the existing SQL plan baseline or not?

Well, we didn’t get the same plan or a note to say the SQL plan baseline was used. The plan used in this case was a fast full scan of the bitmap index SALES_PROD_BIX. So does this mean the SQL statement wasn’t matched to our existing SQL plan baseline?

No, the SQL statement was in fact matched to the existing SQL plan baseline but the accepted plan in the SQL plan baseline couldn’t be reproduced in the SCOTT schema.

Here is exactly what happened. When the statement was issued in the SCOTT schema Oracle determined the best cost-based plan for the SQL statement to be a fast full index scan on the SALES_PROD_BIX index. Before executing this plan we checked to see if the SQL statement matched an existing SQL plan baseline using its SQL signature. We found a corresponding SQL plan baseline, so we checked to see if the cost-based plan we came up with matches an accepted plan in the SQL plan baseline. It does not, so our cost-based plan got added to the SQL plan baseline as a non-accepted plan and we tried to use the existing accepted plan.
However, the existing plan requires a b-tree index on the prod_id and quantity_sold, which doesn’t exist in the SCOTT schema. Since we can’t reproduce the accepted plan, we use the cost-based plan that the Optimizer came up with at parse.
If we check the SQL plan baseline we will see that this new plan using the SALES_PROD_BIX index has been added to the SQL plan baseline but it is not accepted.


If the new plan is accepted using DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE procedure, we will see that it is used by the identical SQL statement in the SCOTT schema.

Now, that the new plan is accepted let try running the SQL statement again from the SCOTT schema.

So, as you can see the same SQL plan baseline is being used for an identical SQL statement issued from two different schemas. This is not a problem since a SQL plan baseline can have multiple accepted execution plans. You just need to manage the plans in the SQL plan baseline to ensure your preferred plan for each schema is an accepted plan.

You can get a copy of the script I used to generate this post here.

+Maria Colgan

Join the discussion

Comments ( 21 )
  • guest Wednesday, January 9, 2013

    Great , so new plan has been added because You've set optimizer_capture_sql_plan_baselines=true ?

    Or I'm missing something ?

    Regards

    GregG


  • Maria Colgan Wednesday, January 9, 2013

    Hi Greg,

    No, in my scenario optimizer_capture_sql_plan_baselines is set to false (the default). If a new plan is found for a SQL statement that already has a SQL plan baseline then that plan is added to the SQL plan baseline as an unaccepted plan. This is the expected behavior from SPM.

    So, in this case the new plan based on the SCOTT schema is automatically added to the SQL plan baseline because the SQL plan baseline exists.

    The only way to prevent this behavior is to mark the initial plan in the SQL plan baseline as FIXED.

    Thanks,

    Maria


  • guest Wednesday, January 9, 2013

    Thanks Maria, I've found that minute after clicking submit :).

    Regards

    GregG


  • Yury Thursday, January 10, 2013

    Hello Maria,

    Thanks a lot for all your great work to explain how Optimizer works. Please keep it the way you do.

    On this occasion I think the way Optimizer behaviors is wrong. My understanding is: if database objects (tables) that an SQL uses are different from the objects used for a baseline created for an SQL with the same text the Optimizer shouldn't even try to match baselines.

    Let's immagine a very simple case where the schemas' structure (tables, indexes, constraints) is exactly the same under two users SH and SCOTT. However because of the data cardinality differences in the schemas it is more efficient to execute a FULL TABLE scan in one of the schemas but in INDEX LOOP in the other. If Optimizer search for a baseline using approach you have described then for one schema it will use inefficient execution plan dictated by the baseline. I think Oracle should use SQL signature + tables list as a unique ID for matching SQL with a baseline.

    Is my understanding correct or I am missing something?

    Regards,

    Yury


  • mdinh Thursday, January 10, 2013

    Why add the plan and not verify? Is it so that the SQL can have two execution path from different design? What's the benefit of adding the plan to the baseline since the optimizer is chosing the proper executing with the appropriate index in place?

    Thanks Michael.


  • Maria Colgan Thursday, January 10, 2013

    Hi Michael,

    The new plan is added to the SQL plan baseline but is not automatically accepted because SQL Plan Management(SPM) is fundamentally a plan stability feature. If we automatically accepted the new plan without verifying it's performance in comparison to the initial plan we could potentially regress the performance of the SQL statement.

    Existing good plans are often added to SQL plan baselines to ensure they won't change in the future when one or more changes may be planned for the environment.

    Thanks,

    Maria


  • Maria Colgan Thursday, January 10, 2013

    Hi Yury,

    I am delighted to hear you enjoy our blog posts and thanks you for feedback on how SPM match baselines. I have passed your feedback to Oracle development for further consideration.

    You are correct in the scenario you described. If the schema structures (tables, indexes, constraints) are exactly the same under the two different users SH and SCOTT, but each schema needs a different plan then both of those plans need to presented in the SQL plan baseline and accepted or else one of the schemas will end up with a sub-optimal plan due to the SQL plan baseline.

    HOWEVER, the goal of SPM is plan stability, which means the same plan every time no matter what, even if that means you may miss out on better plan. That's what you sign up for when you use it. If you need / want different plans depending on a scenario you have two choices.

    1. Trust that the Optimizer in conjunction with to our adaptive features to get the right plan in each scenario.

    2. Proactively add and accept all of the plans you want to be used for a specific SQL statement into a SQL plan baseline.

    Thanks,

    Maria


  • DILLIP KUMAR DAS Friday, January 11, 2013

    This is good article. now i am just reading.

    This is issue of performance.

    Thank you.

    With Regards

    Dillip


  • Iudith Mentzel Monday, January 21, 2013

    Hello Maria,

    Thanks a lot for your great posts, with so clear explanations :):)

    I think that Yury is right in the above post, two SQL statements that have identical syntax, but reference completely different objects

    (like objects named identically in different SCHEMAS) should NEVER be allowed to even try to "borrow" an execution plan from each other.

    I think that the hash value that serves for the baseline plan matching

    should already incorporate all the information that Oracle detects while soft-parsing a statement, aka the same information that

    is used for looking up a statement in the Library Cache to decide

    whether a hard-parse is required or not, in the absence of the SPM feature.

    The baseline can contain several plans, but only for the 100% SAME statement, accessing exactly the same objects.

    Thanks again a lot, I wish I were able to attend "live" your presentations at the many conferences :) :)

    Best Regards,

    Iudith Mentzel

    ZIM Integrated Shipping Services Ltd.

    Haifa, Israel


  • guest Tuesday, January 22, 2013

    I don't want to get off topic but could you explain if it is possible to use SQL Plan Baselines in an Active Data Guard database?

    Would it be possible to store the Plan Baseline in the Primary database and have it "discovered" and utilized in the Physical Standby?

    Thanks in advance for any information you can provide reqarding this subject.


  • guest Tuesday, January 22, 2013

    Can SQL PLan Baselines be implemented for Active Data Guard Physical Standby databases?


  • guest Thursday, March 14, 2013

    Hello Maria

    Thanks for this wonderful post. I follow most of your posts and i am not exaggerating here. You provide lot of very useful information.

    I tried something very similar here with SPM on and i found that if the value 30 is changed to some other value, the baseline plan is not being used.

    In fact even if variable used while capturing the baseline plans are changed, optimizer does not use the baseline.

    All that it is doing is a exact text string match of the sql_text and only if it matches completely, it use the baseline.

    Is there any way to change this behavior.

    Please advise.


  • guest Thursday, March 14, 2013

    Hi,

    You are correct if the literal value in the SQL statement is changed from 30 to any other value the SQL plan baseline will not be used. This is the expected behavior, as SQL plan baselines are matched on exact SQL text. There is no way to change this behavior.

    You can of course change the SQL to use binds so that SQL text doesn't change on each execution but that will require you changing either the initialization parameter CURSOR_SHARING to FORCE or the actual application to insert binds. Both of these changes are significant and would require a lot of testing before being implemented in product.

    Thanks,

    Maria


  • guest Friday, April 5, 2013

    Hi, Maria

    Could you explain the next stiuation:

    there are two plans in the SQL plan baseline for one sql statement.

    (not different owner, not different sql, the sql statement is the same exactly, the same sql_id, from the same user and the same client application)

    one of the plan is marked as enabled, other is marked as not enabled.

    but some time thoe optimizer picks up the "not enabled" plan - why ?

    And no another plan for this sql is added to SQL plan baseline for such execution. The behaviour looks as randmom

    ???


  • guest Friday, April 19, 2013

    Hi,

    We are seeing one issue wherein a sql has a remote table lookup and baseline is not getting created. Is there a restriction that if there is a remote table lookup, dont create baseline. We have an SR open with support anyway.

    Can you please clarify and suggest if this is indeed the case

    Thanks

    Devinder


  • guest Friday, April 19, 2013

    Hi Devinder,

    There is a restriction on SQL plan baseline and remote table accesses. SQL plan baselines currently do not support remote table accesses.

    Thanks,

    Maria


  • Devinder Friday, April 19, 2013

    Thanks Maria for the quick reply. any reason this is a restriction? as remote table look up is always going to be full table scan anyway.

    The reason we started looking at this issue is because of a bad plan in one environment and good plan in another. So I thought of importing the baseline ( after manually creating in the env where it is working fine.) It got imported fine, but during execution time, optimizer completely ignored it and executed the sql with the bad plan. Is that how it will work, that it will not even care to check if a baseline exists.

    Is there a way to force it to use baseline in this situation?


  • Mark Agulnick Monday, May 20, 2013

    Hi Maria. Very informative blog post.

    I had 2 questions which I believe are related to this post. We are currently working on a migration from Oracle Database 10g to 11g. We would like to make use of SQL Plan Management and SQL Plan Baselines in order to maintain plan stability when migrating from 10g to 11g.

    Question 1) We are following the Oracle documentation to generate a SQL Tuning Set from our 10g production system and migrating that to our 11g test system, using Staging tables and datapump, and then loading the SQL plans from the Tuning Set into the 11g SQL Plan Baselines. In our environment, the name of the database user in the 10g Production System is different from the name of the database user in the 11g test system. Again, we have followed the documentation so that during the transfer, the owner of the SQL Tuning set is changed to the database user in the target 11g test system. However, the PARSING_SCHEMA_NAME remains that of the production schema name. Given that the two DBs are (nearly) structurally identical (same objects) and the only thing different is the database user name, will Oracle be able to make proper use of the SQL Plan Baselines even though the PARSING_SCHEMA_NAME is different than the user on the test system? In other words, when the test user on the test system runs the same exact queries, will Oracle be able to find them in the baselines even if the PARSING_SCHEMA_NAME is different?

    Question 2) I am seeing that a few of the plans in the 11g SQL Plan Baslines that we loaded have REPRODUCED=NO. Based on my read of the documentation, Oracle will not be able to use these plans. Is there some way to tell definitively why a particular Plan in the Baselines is not reproducible?

    Thanks in advance.

    - Mark Agulnick


  • Neel Wednesday, December 16, 2015

    Hello Maria,

    Thanks a lot for the detailed explanation.

    Could you please help me to understand the following scenario?

    Plan Hash Value of a query in Cursor Cache differs with the Plan Hash Value (and also the plan) of the specific SPM which is being used by the Query.

    SQL ID - 0yc7a706upwna

    1) Showing output from dbms_xplan.display_cursor

    Plan hash value: 3927359371

    SQL plan baseline SQL_PLAN_f6kbqs97r7vdg89b07845 used for this statement

    2) Showing output from DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE

    for SQL_PLAN_f6kbqs97r7vdg89b07845

    Plan hash value: 1387980137 (with a different plan)

    Note:- I will provide the full details if needed.

    Regards.

    Neel..


  • GOURANGA MOHAPATRA Wednesday, October 3, 2018
    Hello Maria,

    Below is my current setting:

    optimizer_capture_sql_plan_baselines : FALSE

    Against one sql_id I have two plan# value. One plan# value is less cost and good another is high cost and bad plan. But currently optimizer is taking bad plan and there is a performance issue. It is in 12c ( 12.1)

    How I can apply directly best paln# value to optimizer?

    Regards,
  • Nigel Bayliss Friday, October 5, 2018
    Hi Gouranga, firstly check DBA_SQL_PLAN_BASELINES to see if both plans are enabled and accepted (i.e. they are active SQL plan baselines). If the plan you want is not accepted, then perhaps you are not evolving plans automatically? You can use EVOLVE_SQL_PLAN_BASELINE supplying the sql_handle and plan_name for the plan you want. Print the contents of the CLOB returned by this function to see why or why not the plan is accepted. If the SQL plan baselines are all enabled+accepted, then it's possible that one cannot be reproduced (e.g. there's a schema change) or perhaps because the optimizer deems it as being more costly based on bind values. You can try setting ENABLED=NO for the plan(s) you don't want. If you do this, be careful to test, because the SQL plan baseline plan you have might provide a better plan than an unconstrained plan (in the case where the plan you want fails to reproduce for whatever reason - e.g. there's been a schema change). If you ENABLE=NO the plan you don't want, then the remaining enabled+accepted SQL plan baseline will be used (provided that it is reproducible). An alternative is to FIX the plan you want, and then it will chosen in preference. I generally prefer not to fix SQL plan baselines because it effectively disables the evolve mechanism for the relevant SQL plan baseline, but it is quick and easy if you want to use it (you will want to be sure that the plan you have fixed is a plan that can be used by optimizer - as per the example described in this post).
    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