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

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

Comments:

Great , so new plan has been added because You've set optimizer_capture_sql_plan_baselines=true ?
Or I'm missing something ?
Regards
GregG

Posted by guest on January 09, 2013 at 08:03 AM PST #

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

Posted by Maria Colgan on January 09, 2013 at 08:47 AM PST #

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

Posted by guest on January 09, 2013 at 08:54 AM PST #

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

Posted by Yury on January 09, 2013 at 04:00 PM PST #

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.

Posted by mdinh on January 09, 2013 at 04:29 PM PST #

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

Posted by Maria Colgan on January 09, 2013 at 06:40 PM PST #

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

Posted by Maria Colgan on January 10, 2013 at 03:06 PM PST #

This is good article. now i am just reading.

This is issue of performance.

Thank you.

With Regards
Dillip

Posted by DILLIP KUMAR DAS on January 11, 2013 at 04:08 AM PST #

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

Posted by Iudith Mentzel on January 21, 2013 at 09:16 AM PST #

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.

Posted by guest on January 22, 2013 at 09:30 AM PST #

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

Posted by guest on January 22, 2013 at 09:48 AM PST #

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.

Posted by guest on March 14, 2013 at 01:21 AM PDT #

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

Posted by guest on March 14, 2013 at 11:15 AM PDT #

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
???

Posted by guest on April 05, 2013 at 09:48 AM PDT #

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

Posted by guest on April 19, 2013 at 08:21 AM PDT #

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

Posted by guest on April 19, 2013 at 09:52 AM PDT #

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?

Posted by Devinder on April 19, 2013 at 10:44 AM PDT #

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

Posted by Mark Agulnick on May 20, 2013 at 08:37 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today