11G OCM Use SQL Plan Management feature

References

Overview

The objective from the 11G OCM Upgrade Exam is:

Use SQL Plan Management feature

Reading Notes

SQL Plan Management and Control of SQL Plan Baselines

SQL Plan Management and Control of SQL Plan Baselines says:

The use of stored outlines is deprecated in Oracle Database 11g Release 1 (11.1). Instead, you should use the SQL plan management feature that enables the optimizer to maintain a history of execution plans for a SQL statement. Using the execution plan history, the optimizer is able to detect a new plan representing a plan change for a SQL statement. When the optimizer detects a new plan, it stores the new plan and marks it for performance evaluation and uses the old (currently known good) plan. The optimizer uses the new plan only after its performance is verified to be better than that of the old plan. A SQL plan baseline consists of a set of known good plans for a SQL statement.

Emphasis Mine

And the backward compatibility is as follows:

If a stored outline is available for a SQL statement, then the SQL Plan Management feature is not used. However, if another user session uses the same SQL statement but without an active stored outline, then the SQL plan management feature is used.

SQL Plan Management

SQL Plan Management says that:

There are two ways to seed or populate a SQL Management Base (SMB) with execution plans:

Automatic capture of execution plans (available starting with Oracle Database 11g)
Bulk load execution plans or preexisting SQL plan baselines

Bulk loading of execution plans or SQL plan baselines is especially useful when upgrading a database from a previous version to Oracle Database 11g. SQL plans that are bulk loaded are automatically accepted and added to existing or new plan histories as SQL plan baselines. Use one of the following techniques to bulk load the SQL Management Base as part of an upgrade:

Populate the execution plans for a given SQL Tuning Set (STS), as described in "Bulk Loading a SQL Management Base with a SQL Tuning Set (STS)".
Unpack existing SQL plan baselines from a staging table, as described in "Unpacking Existing SQL Plan Baselines from a Staging Table".

Capturing SQL Plan Baselines

Capturing SQL Plan Baselines says that:

To recognize repeatable SQL statements, a statement log is maintained that contains identifiers of various SQL statements the optimizer has evaluated over time. A SQL statement is recognized as repeatable when it is parsed or executed again after it has been logged.

Emphasis Mine

Automatic Plan Capture

Automatic Plan Capture says that:

To enable automatic plan capture, set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE. By default, this parameter is set to FALSE.

Selecting SQL Plan Baselines

Selecting SQL Plan Baselines says that:

To enable the use of SQL plan baselines, set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE (default).

Evolving SQL Plan Baselines

Evolving SQL Plan Baselines says that:

During the SQL plan baseline evolution phase, the database evaluates the performance of new plans and integrates plans with better performance into SQL plan baselines.

Evolving Plans With Manual Plan Loading

Evolving Plans With Manual Plan Loading says that:

You can evolve an existing SQL plan baseline by manually loading plans either from the cursor cache or from a SQL tuning set. When you manually load plans into a SQL plan baseline, these loaded plans are added as accepted plans.

Emphasis Mine

Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE says that:

The PL/SQL function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE tries to evolve new plans added by the optimizer to the plan history of existing plan baselines. If the function can verify that the new plan performs better than a plan chosen from the corresponding SQL plan baseline, the new plan is added as an accepted plan.

Emphasis Mine

Using SQL Plan Baselines with the SQL Tuning Advisor

Using SQL Plan Baselines with the SQL Tuning Advisor says that:

When tuning SQL statements with the SQL Tuning Advisor, if the advisor finds a tuned plan and verifies its performance to be better than a plan chosen from the corresponding SQL plan baseline, it makes a recommendation to accept a SQL profile. When the SQL profile is accepted, the tuned plan is added to the corresponding SQL plan baseline. However, the SQL Tuning Advisor will not verify existing unaccepted plans in the plan history.

Emphasis Mine

Using Fixed SQL Plan Baselines

Using Fixed SQL Plan Baselines says that:

A SQL plan baseline is fixed if it contains at least one enabled plan whose FIXED attribute is set to YES. You can use fixed SQL plan baselines to fix the set of possible plans (usually one plan) for a SQL statement, or migrate an existing stored outline by loading the "outlined" plan as a fixed plan.

If a fixed SQL plan baseline also contains non-fixed plans, the optimizer will give preference to fixed plans over non-fixed ones. This means that the optimizer will pick the fixed plan with the least cost even though a non-fixed plan may have an even lower cost. If none of the fixed plans is reproducible, then the optimizer will pick the best non-fixed plan.

The optimizer will not add new plans to a fixed SQL plan baseline. Since new plans are not automatically added, a fixed SQL plan baseline is not evolved when DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE is executed. However, a fixed SQL plan baseline can be evolved by manually loading new plans into it from the cursor cache or a SQL tuning set.

When a SQL statement with a fixed SQL plan baseline is tuned using the SQL Tuning Advisor, a SQL profile recommendation has special meaning. When the SQL profile is accepted, the tuned plan is added to the fixed SQL plan baseline as a non-fixed plan. However, as described above, the optimizer will not use the tuned plan as long as a reproducible fixed plan is present. Therefore, the benefit of SQL tuning may not be realized. To enable the use of the tuned plan, manually alter the tuned plan to a fixed plan by setting its FIXED attribute to YES.

Emphasis Mine

Test Case

The test I am using is the SQL from Replay a captured workload which has an SQL ID of 'dgdfqhj9ua2kv'.

The test query is as follows:

SELECT products.prod_category, products.prod_subcategory, times.calendar_year, SUM( sales.quantity_sold ) AS total_quantity_sold, SUM( sales.amount_sold ) AS total_amount_sold FROM sales INNER JOIN products USING( prod_id ) INNER JOIN customers USING( cust_id ) INNER JOIN countries USING( country_id ) INNER JOIN channels USING( channel_id ) INNER JOIN times USING( time_id ) WHERE products.prod_valid = 'A' AND channels.channel_class = 'Direct' AND customers.cust_valid = 'A' AND countries.country_name = 'Australia' GROUP BY products.prod_category, products.prod_subcategory, times.calendar_year ORDER BY products.prod_category, products.prod_subcategory, times.calendar_year ;

This query is about a yearly summary of product categories and sub-categories sold directly to active customers in Australia.

This query was put into an SQL Tuning Set called SYS.OCM11G during the procedure outlined in Use SQL Tuning Advisor.

Procedure

Check Status of SQL Plan Baselines

The current value for the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is FALSE:

SQL> show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES NAME TYPE VALUE ------------------------------------ ----------- -------- optimizer_capture_sql_plan_baselines boolean FALSE

Enable SQL Plan Baselines

I am going to do this on a session level only:

ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

Load Plans From SQL Tuning Set

Following the procedure in Bulk Loading a SQL Management Base with a SQL Tuning Set (STS), I loaded the plan for the test SQL from the SQL Tuning Set, SYS.OCM11G:

VARIABLE num_plans_loaded NUMBER

EXEC :num_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( 'OCM11G', 'SYS' );

PRINT num_plans_loaded

The result is:

NUM_PLANS_LOADED

1

Displaying SQL Plan Baselines

Using the procedure in Displaying SQL Plan Baselines, the SQL Plan Baselines are found from:

SELECT sql_handle, plan_name, enabled, accepted, fixed FROM DBA_SQL_PLAN_BASELINES;

The result is: