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.

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.

BASIC INFO	SQL PROFILES	SPM BASELINES
What they are	Stored collections of Hints (plus some technical information for the optimizer)	Stored collections of Hints (plus some technical information for the optimizer)
Available from	10g	11g
They affect	Individual SQL	Individual SQL
What they do	Adjust Optimizer cardinality estimations	Direct SQL to follow specific execution plan
Motto (as far asSQL Plans are concerned)	Be the Best you can be !	Only the Worthy may Pass !
Managed by PL/SQL package	dbms_sqltune	dbms_spm
LOADING	SQL PROFILES	SPM BASELINES
How are they created ?	Run SQL Tuning task (dbms_sqltune.execute_tuning_task) to analyze existing SQL and IF cardinality is skewed, store it as SQLProfile	Take existing execution plan from SQL that already ran and store it as SPM baseline
Can their creation be forced ?	YES, but this is not fully supported. I.e.look how folks from www.oraxperts.com did it	YES, any SQL execution plan can be made into SPMbaseline
Can they be created automatically ?	YES, by AutoTask analyzing Top SQLs	YES, ifoptimizer_capture_sql_plan_baselines=TRUE
Can they be created manually for individual SQL?	YES, bydbms_sqltune.execute_tuning_task()	YES, but SQL needs to already have run:dbms_spm.load_plans_from_cursor_cache(sql_id => …)
Can they be captured for the ongoing workload ?	YES, through SQL Tuning Sets	YES, if optimizer_capture_sql_plan_baselines=TRUE
Can they be “group loaded” from SQLs in the shared pool ?	YES, through SQL Tuning Sets	YES, directly
Can they be “group loaded” from SQLs in AWRrepository ?	YES, through SQL Tuning Sets	YES, through SQL Tuning Sets
Are they “activated” upon creation ?	NO, SQL Profiles need to be explicitly accepted	MAYBE, Baseline is activated if it is the first baseline captured (for the SQL) OR if loaded from cursor cache, AWR etc
Can they be activated automatically ?	YES, if accept_sql_profiles is set forSQL Tuning AutoTask	MAYBE, SPM baseline is activated if it is the first baseline captured (for the SQL)
Can they be deactivated globally ?	NO	YES, Setoptimizer_use_sql_plan_baselines=FALSE
Can they be deactivated locally ?	YES, set sqltune_category	NO
Can they be transferred to another database ?	YES	YES
BEHAVIOR	SQL PROFILES	SPM BASELINES
Can they “fire” for the object in different schema ?	YES	YES
Can they “fire” when object has a different structure ?	YES	YES
Can they “fire” when table is replaced withMVIEW ?	YES	NO
Can they “fire” when some objects (i.e. indexes) used in the original plan are missing for the new object ?	YES	NO
LICENSING	SQL PROFILES	SPM BASELINES
Available in Standard Edition ?	NO	NO
Available in genericENTERPRISEEdition ?	NO, you need to also licenseDIAGNOSTICS and TUNING packs	YES

============================================================

QUESTIONS AND ANSWERS

General

What is the difference between a SPM Baseline, a SQL Profile and a SQL_PATCH?

SPM baselines :

Enforce specific plans and Guarantee plan stability
Contain multiple plans
Can record new plans for potential improvement
Only matches a whole SQL statement in its entirety
Only accepted plans are used

A SQL Profile is generated by SQL Tuning Advisor and:

Does not enforce a specific plan
Does not guarantee plan stability
Does not contains a plan at all, whereas SPM can contain multiple plans
Is a collection of directives (generated by STA based on empirical information) which can be applied across many SQL statements to influence the optimizer to hopefully produce a better plan.
Because no plans are recorded, if there is a change that results in a new, better, plan, then that plan would be lost if the change was backed out.
Can be configured to match part of multiple SQL statement (ie can apply to SQL strings which contain all the same characters)
If there is already a SQL plan baseline in place when a SQL Profile is created, the new plan is added to the baseline
For more details see:
Automatic SQL Tuning and SQL Profiles

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
E41573-03
Chapter 17 Automatic SQL Tuning
Section 17.5.1.2 SQL Profile Creation
http://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm#PFGRF95164

SQL_PATCH:

A SQL patch circumvents a failure by causing the query optimizer to choose an alternate execution plan for future executions.
Does not contain a plan - essentially it generates a hint to avoid a problem (in the short term)
Generated as a workaround via the SQL Repair Advisor
For more details see:
Document 1509192.1 How To Use DBMS_SQLDIAG To Diagnose Various Query Issues

https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a

Oracle® Database Administrator's Guide
11g Release 1 (11.1)
B28310-04
Repairing SQL Failures with the SQL Repair Advisor
http://docs.oracle.com/cd/B28359_01/server.111/b28310/diag008.htm

With a baseline, a new plan that is better than the one in the baseline will not be applied but with be stored in the history and not lost. You can then manually check and apply the new plan or schedule the API to check to see if the plan in the history is better. This provides the flexibility to evolve if necessary while providing stability in the meantime.

Additionally, with SPM, the plan is applied as a whole or not at all. For example if an index is dropped with a profile, the hint pertaining to the index will just be ignored and all the other hints applied. With a SPM baseline there is verification of whether the plan you want to force has been reproduced or not. If not a new plan will be calculated and no remnants of the previous plan are used (so you are less likely to get a situation where a nested loops join is used to drive a full table scan like you might get if a partial hint is applied).

You can find more on the differences in the following blog post: What is the difference between SQL Profiles and SQL Plan Baselines?

If a baseline is present for a particular SQL ID, then a profile or a SQL_PATCH will only have an effect if the plan generated by the profile or the SQL_PATCH is Enabled and Accepted within the baseline.

Can there be Multiple baselines?

Yes. SPM baselines allow multiple plans to be associated with a particular SQL statement. A baseline is a set of all the plans for a particular query. There can be multiple queries with baselines each with multiple plans.

Is there a feature which allows partitioning a baseline for multiple user profiles, for example one for application, one default and another for selected end users?

No, but you can use Adaptive Cursor Sharing (ACS) to differentiate between the different plans for different bind variable values. See:

Document 740052.1 Adaptive Cursor Sharing: Overview

I ran my SQL with optimizer_capture_sql_plan_baselines = true but I do not see a baseline in dba_sql_plan_baselines?

In order for a query to be a candidate for capture, it needs to be repeatable. The statement needs to executed more than once in order to be captured. Additionally, it will not capture dictionary SQL. See:

Document 788853.1 Sql Plan Baseline Not always created

If I have a SPM baseline, how do I find out what SQL_ID it relates to?

There are a few different ways of doing this including:

Using the SQL_HANDLE as the identifier for the baseline, join DBA_SQL_PLAN_BASELINES.PLAN_NAME with V$SQL.SQL_PLAN_BASELINE:
SELECT b.plan_name, b.enabled, b.accepted, s.sql_id
FROM DBA_SQL_PLAN_BASELINES b, V$SQL s
WHERE b.plan_name=s.sql_plan_baseline
AND sql_handle='&SQL_HANDLE'
ORDER BY 1;

Note: Column V$SQL.SQL_PLAN_BASELINE is only populated when the SQL plan baseline is, in fact, selected for use. If you are not sure, then you may compare column DBA_SQL_PLAN_BASELINES.SIGNATURE to V$SQL.EXACT_MATCHING_SIGNATURE.

How can I display the plans for a SQL statement recorded in a SPM baseline?

You can see the execution plan using the same API that you use to look at the plan in memory - dbms_xplan.display. For various different ways of doing this see:

Document 235530.1 How to Obtain a Formatted Explain Plan - Recommended Methods

Do I need to change my statistics gathering routine having implemented SPM Baselines?

SPM Baselines are not designed to avoid gathering statistics. If you stored baselines for every statement and never added any new statements, then technically you would not need to re-gather statistics on your application objects. However, this is unlikely to be the case.

How do I move SPM Baselines between databases?

Refer to the following article:

Document 880485.1 Transporting SQL PLAN Baselines from One Database to Another

How do SPM Baselines integrate with Adaptive Features?

For details of how adaptive features integrate with SQL Plan Management (SPM) refer to the following: SQL Plan Management with Oracle Database 12c (PDF)

For more information about Adaptive Features generally, refer to the following article:

Document 2031605.1 Adaptive Query Optimization

How do SPM Baselines interact with the Oracle Database In-Memory Option?

The Oracle Optimizer blog provides a series of articles with details of how the In-Memory option integrates with SQL Plan Management (SPM):

https://blogs.oracle.com/optimizer/entry/tips_on_sql_plan_management
https://blogs.oracle.com/optimizer/entry/tips_on_sql_plan_management1
https://blogs.oracle.com/optimizer/entry/tips_on_sql_plan_management2

Plan Choice

What is the order of precedence for Baselines, SQL profiles, Outlines, Hints etc?

The first recommendation is to "keep it simple". In other words,try not to mix up different methods if possible.

If you do have to use different methods, then:
Adding a hint changes the SQL and so you will have a new cursor that won't match up with an existing baseline. Once you add a baseline to a query containing a hint then that baseline can override whatever that hint is intended to do.

If you have a stored outline and a baseline then the Stored outline will take precedence. If you have Outlines and you want to use SPM Baselines, then migrate the outlines into the baseline. When they are migrated they are no longer considered an outline any more so they won't take precedence over the baseline. If you have migrated the stored outline into a baseline then it is just another plan in that baseline.

If a SQL Plan Baseline exists, then a plan generated by a SQL profile will be added to that baseline. Within a baseline, the plan generated by the SQL profile has the same status options as other baseline plans (ENABLED, ACCEPTED, FIXED, REPRODUCED etc.).

If SQL Profile plan is generated and added to a baseline that already contains a different accepted plan, the plan from the profile will not be one of the accepted plans in the baseline. The SQL Profile will be added as an unaccepted plan to the list of accepted and unaccepted candidates for the baseline in the plan history. The DBA then will have a choice between which different plans to enable and accept. If the profile plan was the first plan added to the baseline, then it would be automatically accepted as with any other initial plan.

How does SPM decide between the same SQL statement called by two different database users, referencing two different tables with the same name?

All SPM Baselines from different users are created for the same statement, and Oracle will choose the best one for each user based upon its cost. See:

Document 1469099.1 SQL Plan Baselines Clarified from a Security Perspective.

Additionally, in terms of baselines, queries using different case and whitespace are also deemed to match as long as the actual SQL is the same.

Plan Evolution

How can I Monitor Plan Evolution?

From 12cR1, it is possible to automatically evolve plans and modify the plan evolution using the SPM Evolve Advisor Task. This process verifies non-accepted plans by comparing their execution performance with the best accepted plan (plan with the lowest cost). If the unaccepted plan's performance is better, it will be accepted automatically. Plans can also be manually evolved. For more information see:

Oracle® Database SQL Tuning Guide
12c Release 1 (12.1)
23 Managing SQL Plan Baselines
23.2.2 Managing the SPM Evolve Advisor Task
http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_spm.htm#TGSQL94648

For more details of SQL Plan Management (SPM) in Oracle Database 12c, refer to the following: SQL Plan Management with Oracle Database 12c (PDF)

Prior to 12cR1, there is no monitoring that automatically evolves plans. Evolution is an active action by the DBA that moves the plan to be accepted and enabled. If you want to monitor what has and has not yet been evolved, then there are various views such as dba_sql_plan_baselines that allow the dba to monitor various pieces of information about SPM. For example you could use a select similar to the following to identify whether a particular plan has been enabled / accepted or not within a baseline:

col sql_text format a42

col parsing_schema_name format a14

col sql_handle format a20

SELECT sql_handle, sql_text, parsing_schema_name, plan_name, enabled, accepted

FROM dba_sql_plan_baselines

WHERE sql_text LIKE '&Query_Text%';

How are Adaptive Plans evolved?

By definition, SPM preserves execution plans for queries and so accepted plans in a SQL plan baseline are static and never adaptive. Accepted plans are always static (non-adaptive). This means that there will be no STATISTICS COLLECTOR row source operations in those plans.

With Adaptive Plans enabled, during parse, the optimizer may generate an adaptive plan that is not present in the plan history. This plan will be added to the history unaccepted. At execution time it will not be used and one of the accepted plans will be used for execution (which will be non-adaptive) instead. The newly added adaptive plan will be verified during the regular evolve process.

When non-accepted adaptive plans go through the evolve process and are marked accepted, the plan will be changed to static. Only the final plan that is used will be captured in the SQL plan baseline. As a result, the plan row sources will change from the original adaptive plan (with STATISTICS COLLECTOR row sources) to a static plan (with no STATISTICS COLLECTOR row sources). Note that since the optimizer picks the best subplan (based on the execution statistics recorded in the statistics collector), in some cases, other row sources will change as well (e.g. the original adaptive plan used Nested Loops as the default join method, but the final static plan uses a Hash Join) and thus the plan hash value will also change.

For more details refer to the "SQL plan baselines & Adaptive Plans" section in the following: SQL Plan Management with Oracle Database 12c (PDF)

How does a DBA decide which plans to accept and enable?

The choice between which plan to use is that same with or without SPM. SPM does not make the choice for you, it just provides a mechanism for enforcing the choice that has been made. The usual criteria a DBA would use to determine the relative performance of different plans would be elapsed time or buffer gets.

Plan Capture

What is the recommended method for plan capture?

Plans can be captured in numerous different ways and the 'best' depends on your application and the access to it. If all the plans you want are already executed and recorded in the cache, then create a baseline from there. If you can run a session which contains all the SQL you want to capture then set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true, capture the plans and then return it to the default of false when you are done. For more information on plan capture, see:

Oracle® Database SQL Tuning Guide
12c Release 1 (12.1)
E15858-15
Chapter 23 Managing SQL Plan Baselines
Section 23.1.2 Plan Capture

http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_spm.htm#TGSQL94621

The cost-based optimizer will continue to capture any new execution plan which it identifies as having a lower cost than the lowest-costed, applicable execution plan already present within the SQL plan baseline, even after you have turned off OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES. However, the execution plan is unaccepted until you choose to verify and evolve it. You may prevent this from taking place by marking at least one of the execution plans within the SQL plan baseline as FIXED. This is explained further in Document 1520337.1.

Is it good to set auto capture to true right after an upgrade of the Database?

No.
If you have done no pre-testing then you have no guarantee that the plans will be 'good' right out the box. If you have used Real Application Testing (RAT) or you have stored plans in the previous release then you would likely already have baseline information that you could use or migrate. You can find more about auto-capture in the Oracle Optimizer blog here:

https://blogs.oracle.com/optimizer/entry/what_you_need_to_know

What if the first captured plan is not the most optimal?

If the first captured plan is non-optimal then the situation is the same as without SPM.
You need to investigate why a bad plan is generated.
If the first plan is bad, then that implies that there is something deficient in the statistics or environment that produces a bad plan.
If different bind variable values perform differently with the same plan, then that implies that there is data skew, and column statistics (histograms) may be necessary to reflect that.

Assuming that you are using bind variables, without ACS, you will get a single plan based on the first bind value and all subsequent identical query will use that irrespective of the bind value. If different bind values give different good plans for each value then that implies that ACS would be helpful.

With ACS and SPM, the first plan will be automatically accepted and enabled, subsequent plans will need to be manually accepted. Once you have generated a good plan, you can then manage subsequent plans picking the good plan and make sure that is accepted and enabled so that the bad plan is not used

How can plans be migrated from Oracle 9i?

In Oracle 9i you can create stored outlines and export/import them from 9i to 11, then migrate the outlines in to SPM.
In Oracle 10g you can create a SQL Tuning Set (STS), transport it to 11g and then load it into a baseline.
So the additional steps in 9i over 10g is to create stored outlines and export/import them.

Is it possible to load plans from the AWR into SPM?

Yes.
You can load a SQL Tuning Set from AWR and load the baselines from there.

Document 789888.1 How to Load SQL Plans into SPM from AWR

Is it possible to upload an existing baseline or manually create a baseline?

Baselines can be created manually by
1. Loading plans from the shared SQL area or from a SQL tuning set Using routines in DBMS_SPM
2. Using an Alternative Plan found by SQL Tuning Advisor
   While tuning a SQL statement, SQL Tuning Advisor searches real-time and historical performance data for alternative execution plans
   for the statement. If plans other than the original plan exist, then SQL Tuning Advisor reports an alternative plan finding.
   SQL Tuning Advisor validates the alternative execution plans and notes any plans that are not reproducible.
   When reproducible alternative plans are found, a SQL plan baseline can be created using DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE

When a baseline is created manually, it is automatically accepted.

If a baseline is imported from a "test" database into a "production" database do environment variables that must be set to the same values (e.g. optimizer_mode)?

No.

The Goal of SPM baselines is to guarantee the plan regardless to the environment. The structure of the objects must be the same, but the parameters do not have to be.

Why doesn't the view, DBA_SQL_PLAN_BASELINES, display execution statistics? Columns, such as ELAPSED_TIME and CPU_TIME, are zero.

During auto-capture, SQL plan baseline data is recorded in the SQL Management Base prior to the actual execution of the SQL statement. This means that execution statistics are not available for capture. If the value of the ORIGIN column is 'AUTO-CAPTURE', then data for the following columns will not be populated:

EXECUTIONS
ELAPSED_TIME
CPU_TIME
BUFFER_GETS
DISK_READS
DIRECT_WRITES
ROWS_PROCESSED
FETCHES
END_OF_FETCH_COUNT

This is clarified in the following bug:

Bug 17875239 columns in sqlobj$auxdata not populated by auto-capture

Performance and Footprint

What is the performance impact of setting optimizer_capture_sql_plan_baselines on a production system?

If you are using the latest patchset the impact is negligible since all you are doing is recording generated plans .

How can you calculate additional disk space in the sysaux tablespace for spm?

This is not easy to calculate. It depends on how many queries are being captured and how many plans each generate. The best advice is not to enable the feature systemwide, but for specific SQLs that are critical to the business only. Then monitor and adjust the space as required.

How can you verify the performance of different accepted plans stored in a baseline verses non accepted plans?

The evolve API (DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE) has a verify option which allows Oracle to compare the performance inside or outside the accepted.

Does capturing and use of SPM baselines degrade the database performance?

No.

There is a very small overhead, but it is negligible.

Troubleshooting SPM Baseline Issues

What are the known issues with SPM ?

Please refer to the following article which outlines the recommended patches:

Document 2035898.1 Patches to Consider for 12.1.0.2 to Avoid Problems with SQL Plan Management (SPM)

Document 2034706.1 Patches to Consider for 11.2.0.4 to Avoid Problems with SQL Plan Management (SPM)

What should I collect to diagnose issues where an SPM baseline is not used?

Please refer to the following article which outlines the best information to collect:

Document 1644732.1 How to Collect Standard Information for an Issue where a SQL Plan Management (SPM) Baseline is not Used

=============================================================

=================================================