What is the difference between SQL Profiles and SQL Plan Baselines?

Since Oracle Database 11g was released I have gotten a lot of questions about the difference between SQL profiles and SQL plan baselines and why SQL profiles can be shared but SQL plan baselines can't. So I thought it would be a good idea to write a post explaining the differences between them and how they interact. But first let's briefly recap each feature.

The query optimizer normally uses information like object and system statistics, compilation environment, bind values and so on to determine the best plan for a SQL statement. In some cases, defects in either these inputs or the optimizer can lead to a sub-optimal plan.  A SQL profile contains auxiliary information that mitigates these problems.  When used together with its regular inputs, a SQL profile helps the optimizer minimize mistakes and thus more likely to select the best plan.

A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select the best plan from among this set. If a different plan is found using the normal cost-based selection process, the optimizer will add it to the plan history but this plan will not be used until it is verified to perform better than the existing accepted plan and is evolved. We described this behavior in more detail in a previous post.

So, SQL profiles provide additional information to the optimizer to help select the best plan; they don't constrain the optimizer to any specific plan, which is why they can be shared. SQL plan baselines, on the other hand, constrain the optimizer to only select from a set of accepted plans. The cost-based approach is still used to choose a plan, but only within this set of plans. SQL plan baselines are a more conservative plan selection strategy than SQL profiles. So when should you use SQL profiles versus SQL plan baselines?

You should use a SQL profiles if you just want to help the optimizer a little in its costing process without constraining it to any specific plan. This approach can be extremely useful when you want the system to adapt immediately to changes like new object statistics. You should use SQL plan baselines if you are more conservative and want to control which plans are used. If you are using SQL plan baselines and find that the optimizer sometimes does not select the best plan from the accepted list or does not find a best-cost plan to add to the plan history, then you can always use a SQL profile as well. What happens if a SQL statement has both a SQL Profile and a SQL plan Baseline?

If you recall, SQL Plan Management (SPM) has three component, plan capture, plan selection, and plan evolution. The presence of a SQL profile affects all three components of SPM and we will describe each of those interactions below.

SPM plan capture and SQL profiles

When the statement is executed it will be hard parsed and a cost based plan will be generated. That plan will be influenced by the SQL Profile. Once the cost based plan is determined it will be compared to the plans that exist in the SQL plan baseline. If the plan matches one of the accepted plans in the SQL plan baseline, we will go ahead and use it. However, if the cost based plan doesn't match any of the accepted plans in the SQL plan baseline it will be added to the plan baseline as an unaccepted plan.

SPM plan selection and SQL profiles

When a SQL statement with a SQL plan baseline is parsed, the accepted plan with the best cost will be chosen.  This process uses the regular optimizer.  The presence of a SQL profile will affect the estimated cost of each of these plans and thus potentially the plan that is finally selected.

SPM plan evolution and SQL profiles

The third sub-component of SPM is verification or evolution of non-accepted plans. The evolution process test-executes the non-accepted plan against the best of the accepted plans.  The best accepted plan is selected based on cost.  Again, if a SQL profile exists, it will influence the estimated cost and thus the accepted plan chosen for comparison against the non-accepted plan.

Hopefully this information gives you a clear picture of how SQL profile and SQL plan baselines differ and how they interact with one another!

+Maria Colgan

Comments:

Maria,

Whilst this difference between SQL Profiles and SQL Plan Baselines might have been what was originally intended - i.e. SQL Profiles being for statistical adjustments using opt_estimate hints amongst others - I think that in the field the distinction has become less so (in particular I'm thinking of the SQLT / COE method of using a sql profile but with access path/join mechanism hints captured from actual execution plans).

To clarify what I mean, see the results of a poll I published on Friday:
http://orastory.wordpress.com/2012/05/04/poll-sql-plan-management/

Whilst the poll participation has not been massive, the results have been in line with what I was expecting although I was surprised that the usage of SQL Profiles to enforce access paths and join mechanism was so high.

The key difference then becomes that Baselines enforce particular plan hashes and if they are not reproduced then the Baseline is discarded whereas SQL Profiles just apply the hints and don't care about the plan.

This poll is still open so if anyone can add their votes to give a better picture of how SQL Plan Management features are being used in the field.

Cheers,
Dominic

Posted by Dominic Brooks on May 09, 2012 at 02:16 AM PDT #

Good post! Very clear. thx.

Posted by guest on May 09, 2012 at 11:55 AM PDT #

Hi Dominic,

The profiles that influence access paths and join mechanisms created by SQLT in Oracle Database 10g were implemented as a 'workaround' because SQL plan baselines didn't exist. From Oracle Database 11g onwards SQLT uses SQL plan Baselines. So it would be interesting to see what the results would be if you limited the poll to systems that are just on Oracle Database 11g

Thanks,
Maria

Posted by Maria Colgan on May 09, 2012 at 02:10 PM PDT #

"The evolution process test-executes the non-accepted plan against the best of the accepted plans. The best accepted plan is selected based on cost."

What is the definition of "cost" here - is it a single metrics/statistic captured during the test-execute phase?

Jim

Posted by guest on May 10, 2012 at 02:34 AM PDT #

Maria,

thank you for that clarification. I am wondering about the usefulness of this kind of SQL plan control when it comes down to bind sensitivity. It seems to me neither SQL profiles nor SQL plan baselines can cope with that since they represent just the best world for a single set of bind values or a limited set of bind values. I hope my understanding is correct, isn't it?

I know there is adaptive cursor sharing available but on the other hand there seems to be no or few SQL plan control in that. And by the way adaptive cursor sharing information are shown in v$ views - does that mean they do not persist, thus they do not survive an instance restart?

Peter

Posted by Peter Alteheld on May 10, 2012 at 04:50 AM PDT #

Maria,

Speaking of SQL profiles, may I suggest the next topic for your blog? Can you provide more information on OPT_ESTIMATE hint and all possible parameters to pass this hint?

Thank you.

Posted by guest on May 10, 2012 at 09:06 AM PDT #

> So it would be interesting to see what the results would be if you limited the poll to systems that are just on Oracle Database 11g

This did occur to me but a) it was too late to change the results once the poll had started and b) I was influenced by the surprising number of DBAs I know on 11g still sticking to the SQLT/COE method.

Maybe I'll do another poll shortly.

Posted by Dominic Brooks on May 10, 2012 at 10:00 AM PDT #

@Peter

If a sql statement requires multiple execution plans for different bind sets then a sql profile isn't usually going to be the right option because you can only have one sql profile per statement.

However baselines provide the mechanism to have multiple accepted plans for a single statement.

But baselines do not appear to store any ACS-related information, so if you happen to lose that additional bind sensitivity information(aged/flushed/restart), then you need the multiple executions in order for ACS to kick in and then for the optimizer to switch to one of the accepted plans that are more suitable for the bindset.

Apologies to Maria for continuing to post links to my blog from here, but regarding ACS and Baselines, I did an investigation here:
http://orastory.wordpress.com/2012/03/22/adaptive-cursor-sharing-and-sql-plan-baselines/

Hope that's useful / accurate.

Cheers,
Dominic

Posted by Dominic Brooks on May 10, 2012 at 10:06 AM PDT #

Hi Maria,

I am faced with a case that does not make sense tome :-)The following query had a bad plan:

SELECT ASF_ID "asf_id", ASF_PARENT_ID "asf_parent_id", ASF_ATTR_UPTODATE "attrUptodate", ASF_CREATED_BY "createdBy", ASF_CREATE_DT "createDt", ASF_LABEL "label", ASF_NOTES "longDesc", ASF_MODIFIED_BY "modifiedBy", ASF_MODIFY_DT "modifyDt", ASF_NM "name", ASF_PATH "path", ASF_PUB_VERSION "pubVersion", ASF_DESC "shortDesc", ASF_STATUS_CD "status_cd" FROM V_ASST_FOLDER WHERE V_ASST_FOLDER.VERSION=:"SYS_B_0" AND (ASF_PARENT_ID IS NULL AND ASF_STATUS_CD NOT IN (:"SYS_B_1", :"SYS_B_2")) ORDER BY ASF_PATH

It is very easy to improve with a hint:
/*+ index(v_asst_folder.object xakobj_type) */

Tried to replace the bad plan with the hinted one using SPM...to no avail.
I then created a profile and it worked!( the good plan is now being selected at runtime); however, it added another plan to the baseline and IT IS IDENTICAL to the one I created by hand when tried to replace the bad one with the hinted one.
Now, both are accepted and enabled.

---------------------------------------------------------------------
Why? Why would not it use the same plan before I added the profile???
---------------------------------------------------------------------

Here are the details of both Baseline Details:

1. This one was added three days ago when I replaced the bad one using the hint:
--------------------------------------------------------------------------------SQL handle: SQL_bd4dcbf73b5b63d8SQL text: SELECT ASF_ID "asf_id", ASF_PARENT_ID "asf_parent_id", ASF_ATTR_UPTODATE "attrUptodate", ASF_CREATED_BY "createdBy", ASF_CREATE_DT "createDt", ASF_LABEL "label", ASF_NOTES "longDesc", ASF_MODIFIED_BY "modifiedBy", ASF_MODIFY_DT "modifyDt", ASF_NM "name", ASF_PATH "path", ASF_PUB_VERSION "pubVersion", ASF_DESC "shortDesc", ASF_STATUS_CD "status_cd" FROM V_ASST_FOLDER WHERE V_ASST_FOLDER.VERSION=:"SYS_B_0" AND (ASF_PARENT_ID IS NULL AND ASF_STATUS_CD NOT IN (:"SYS_B_1", :"SYS_B_2")) ORDER BY ASF_PATH-------------------------------------------------------------------------------- --------------------------------------------------------------------------------Plan name: SQL_PLAN_bumfbywxpqsyscab13b3a Plan id: 3400612666Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD-------------------------------------------------------------------------------- Plan hash value: 3480625151 --------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 144 | 946 (1)| 00:00:07 || 1 | SORT ORDER BY | | 1 | 144 | 946 (1)| 00:00:07 ||* 2 | TABLE ACCESS BY INDEX ROWID| OBJECT | 1 | 144 | 945 (1)| 00:00:07 ||* 3 | INDEX RANGE SCAN | XAKOBJ_TYPE | 44851 | | 38 (0)| 00:00:01 |-------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("OBJ_PARENT_ID" IS NULL AND "OBJ_STATUS_CD":SYS_B_1 AND "OBJ_STATUS_CD":SYS_B_2 AND "OBJECT"."VERSION"=TO_NUMBER(:SYS_B_0)) 3 - access("OBJ_TYPE"=4) Note----- - SQL profile "SYS_SQLPROF_0138fc984e7b0000" used for this statement

2. This one was added automatically when I created the profile:

SQL handle: SQL_bd4dcbf73b5b63d8SQL text: SELECT ASF_ID "asf_id", ASF_PARENT_ID "asf_parent_id", ASF_ATTR_UPTODATE "attrUptodate", ASF_CREATED_BY "createdBy", ASF_CREATE_DT "createDt", ASF_LABEL "label", ASF_NOTES "longDesc", ASF_MODIFIED_BY "modifiedBy", ASF_MODIFY_DT "modifyDt", ASF_NM "name", ASF_PATH "path", ASF_PUB_VERSION "pubVersion", ASF_DESC "shortDesc", ASF_STATUS_CD "status_cd" FROM V_ASST_FOLDER WHERE V_ASST_FOLDER.VERSION=:"SYS_B_0" AND (ASF_PARENT_ID IS NULL AND ASF_STATUS_CD NOT IN (:"SYS_B_1", :"SYS_B_2")) ORDER BY ASF_PATH-------------------------------------------------------------------------------- --------------------------------------------------------------------------------Plan name: SQL_PLAN_bumfbywxpqsys7460d2f3 Plan id: 1952502515Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-SQLTUNE-------------------------------------------------------------------------------- Plan hash value: 3480625151 --------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 144 | 946 (1)| 00:00:07 || 1 | SORT ORDER BY | | 1 | 144 | 946 (1)| 00:00:07 ||* 2 | TABLE ACCESS BY INDEX ROWID| OBJECT | 1 | 144 | 945 (1)| 00:00:07 ||* 3 | INDEX RANGE SCAN | XAKOBJ_TYPE | 44851 | | 38 (0)| 00:00:01 |-------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("OBJ_PARENT_ID" IS NULL AND "OBJ_STATUS_CD":SYS_B_1 AND "OBJ_STATUS_CD":SYS_B_2 AND "OBJECT"."VERSION"=TO_NUMBER(:SYS_B_0)) 3 - access("OBJ_TYPE"=4) Note----- - SQL profile "SYS_SQLPROF_0138fc984e7b0000" used for this statement

Maria, I would appreciate your help if you have a free moment.
Thanks

Posted by Gene Zisman on August 06, 2012 at 09:44 AM PDT #

Hi Gene,

Although the steps in both plans are identical, the plan hash value and the plan names are different.

When the SQL profile is in place we will generates a plan at parse time (based on the profile) that has the same plan hash value as an accepted plan so we don't need to generate the plan based on the information stored in SQL Plan Management. We just check that the plan hash value we came up with at parse matches one of the accepted and enabled plans in the SQL plan baseline and when it does match, we actual use the plan we got at parse.

If the plan hash value found at parse does not match any of the accepted plans in the SQL plan baseline, then we try and reproduce one of the accepted plans.

The question we should be asking here is why we couldn't reproduce use the plan you manually loaded?

The only way to answer that question is to do a 10053 trace and search for the term SPM. You should see a section in the trace that says a SQL plan baseline was found for this statement and a check to see if the plan hash value determined at parse matches an accepted plan. If they don't you will see us attempt to reproduce the accepted plan and why we could not reproduce it.

Thanks,
Maria

Posted by Maria Colgan on August 08, 2012 at 05:30 PM PDT #

Hi,

I have a question, I have checked for tune max sql query through Advisor, But giving me to either create the index or accept the profile.

Please let me know of which sql Advisor give to accept the base line.

Should I accept the profile/base plan for Dynamic sql (Bind Variables).

Please suggest

REgards
Sourabh Gupta

Posted by Sourabh Gupta on September 22, 2012 at 07:54 AM PDT #

I have a question regarding Sql profiles and Baselines, What will happen if a sql has an accepted and enabled Baseline and then you create a profile for it and implement it.
What will be the behavior of optimizer in this scenario. Will it still use baseline or it will use the profile to come up with a new plan and then use it.
Thanks

Posted by Devinder on August 14, 2013 at 09:14 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