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:

SQL_HANDLE	PLAN_NAME	ENABLED	ACCEPTED	FIXED
SYS_SQL_143ab3c15ab507bc	SYS_SQL_PLAN_5ab507bc2492fe52	YES	YES	NO