15 Using SQL Plan Management

15.1.1 Purpose of SQL Plan Baselines

The goal of baselines is to preserve the performance of corresponding SQL statements, regardless of changes in the database. Examples of changes include:

• New optimizer version

• Changes to optimizer statistics and optimizer parameters

• Changes to schema and metadata definitions

• Changes to system settings

• SQL profile creating

SQL plan baselines cannot help in cases where an event has caused irreversible execution plan changes, such as dropping an index.

The SQL tuning features of Oracle Database generate SQL profiles that help the optimizer to produce well-tuned plans, but this is a reactive mechanism and cannot guarantee stable performance when drastic database changes occur. SQL tuning can only resolve performance issues after they have occurred and are identified. For example, a SQL statement may become high-load because of a plan change, but SQL tuning cannot solve this problem until after the plan change occurs.

Common usage scenarios where SQL plan management can improve or preserve SQL performance include:

• A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements, with most of the plan changes resulting in either no performance change or improvement. However, certain plan changes may cause performance regressions. The use of SQL plan baselines significantly minimizes potential performance regressions resulting from a database upgrade.

• Ongoing system and data changes can impact plans for some SQL statements, potentially causing performance regressions. The use of SQL plan baselines will help to minimize performance regressions and stabilize SQL performance.

• Deployment of new application modules means introducing new SQL statements into the system. The application software may use appropriate SQL execution plans developed in a standard test configuration for the new statements. If your system configuration is significantly different from the test configuration, then the SQL plan baselines can be evolved over time to produce better performance.

15.1.2 Architecture of SQL Plan Baselines

A SQL plan baseline contains one or more accepted plans, each of which contains the following information:

• Set of hints

• Plan hash value

• Plan-related information

The plan history is the set of plans, both accepted and not accepted, generated for a SQL statement over time. Because only accepted plans are included in the SQL plan baseline, the plans in the baseline form a susbset of the plan history. For example, after the optimizer generates the first acceptable plan for a SQL plan baseline, subsequent plans are part of the plan history but not part of the plan baseline.

The process of adding plans to a SQL plan baseline is known as plan evolution. To be eligible to be evolved, a plan must be enabled for use by the optimizer.

Figure 15-1 shows a single SELECT statement that has two accepted plans in its SQL plan baseline. The SQL plan history includes two other plans for the statement that have not been proven to perform well.

Figure 15-1 SQL Plan Baseline and SQL Plan History

Description of "Figure 15-1 SQL Plan Baseline and SQL Plan History"

SQL plan baselines and plan history are stored in the SQL management base (SMB), which also contains SQL profiles. The SMB is part of the data dictionary and is stored in the SYSAUX tablespace. The SMB uses automatic space management.

15.2.1 Capturing SQL Plan Baselines

During the SQL plan baseline capture phase, the database detects plan changes and records the new plan so that it can be evolved (verified) by the database administrator. To this end, the database maintains a plan history for individual SQL statements. Because ad hoc SQL statements do not repeat and thus do not suffer performance degradation, the database maintains plan history only for repeatable SQL statements.

To recognize repeatable SQL statements, a statement log is maintained that contains the SQL ID of various SQL statements that 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.

For each repeatable SQL statement, the system maintains a plan history that contains all plans generated by the optimizer. The set of all accepted plans in the plan history is the SQL plan baseline.

The SQL Plan Baseline Capture phase can be configured for automatic capture of plan history and SQL plan baselines for repeatable SQL statements, or a set of plans can be manually loaded as SQL plan baselines.

This section contains the following topics:

• Capturing Plans Automatically

• Creating Baselines from Existing Plans

DECLARE
  my_plans PLS_INTEGER;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'tset1');
END;
/

DECLARE
  my_plans PLS_INTEGER;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '99twu5t2dn5xd');
END;
/

15.2.2 Selecting SQL Plan Baselines

During the SQL plan baseline selection phase, Oracle Database detects plan changes based on the stored plan history, and selects plans to avoid potential performance regressions for a set of SQL statements.

Each time a SQL statement is compiled, the optimizer first uses a cost-based search method to build a best-cost plan, then tries to find a matching plan in the SQL plan baseline. If a match is found, the optimizer will proceed using this plan. Otherwise, it evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost. The best-cost plan found by the optimizer that does not match any plans in the plan history for the SQL statement represents a new plan, and is added as a non-accepted plan to the plan history. The new plan is not used until it is verified to not cause a performance regression. However, if a change in the system (such as a dropped index) causes all accepted plans to become non-reproducible, the optimizer will select the best-cost plan. Thus, the presence of a SQL plan baseline causes the optimizer to use conservative plan selection strategy for the SQL statement.

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

15.2.3 Evolving SQL Plan Baselines

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.

When the optimizer finds a new plan for a SQL statement, the plan is added to the plan history as a non-accepted plan. The plan can then be verified for performance relative to the SQL plan baseline performance. When a non-accepted plan is verified to not cause a performance regression, it is changed to an accepted plan and integrated into the SQL plan baseline. A successful verification of a non-accepted plan consists of comparing its performance to that of a plan selected from the SQL plan baseline and ensuring that it delivers better performance.

This section describes how to evolve SQL plan baselines and contains the following topics:

• Evolving Plans With Manual Plan Loading

• Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
    report clob;
BEGIN
    report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
                  sql_handle => 'SYS_SQL_593bc74fca8e6738');
    DBMS_OUTPUT.PUT_LINE(report);
END;
/

REPORT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
                       Evolve SQL Plan Baseline Report
--------------------------------------------------------------------------------
 
Inputs:
-------
 SQL_HANDLE = SYS_SQL_593bc74fca8e6738
 PLAN_NAME  =
 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
 VERIFY     = YES
 COMMIT     = YES
 
Plan: SYS_SQL_PLAN_ca8e6738a57b5fc2
-----------------------------------
 Plan was verified: Time used .07 seconds.
 Passed performance criterion: Compound improvement ratio >= 7.32.
 Plan was changed to an accepted plan.
 
                     Baseline Plan      Test Plan     Improv. Ratio
                     -------------      ---------     -------------
 Execution Status:        COMPLETE       COMPLETE
 Rows Processed:                40             40
 Elapsed Time(ms):              23              8              2.88
 CPU Time(ms):                  23              8              2.88
 Buffer Gets:                  450             61              7.38
 Disk Reads:                     0              0
 Direct Writes:                  0              0
 Fetches:                        0              0
 Executions:                     1              1
 
-------------------------------------------------------------------------------
                                Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.

15.6.1 Disk Space Usage

Disk space used by the SQL management base is regularly checked against a limit based on the size of the SYSAUX tablespace. By default, the limit for the SMB is no more than 10% of the size of the SYSAUX tablespace. The allowable range for this limit is between 1% and 50%. A weekly background process measures the total space occupied by the SMB, and when the defined limit is exceeded, the process will generate a warning that is written to the alert log. The alerts are generated weekly until either the SMB space limit is increased, the size of the SYSAUX tablespace is increased, or the disk space used by the SMB is decreased by purging SQL management objects (SQL plan baselines or SQL profiles).

To change the percentage limit, use the CONFIGURE procedure of the DBMS_SPM package:

BEGIN
  DBMS_SPM.CONFIGURE('space_budget_percent',30);
END;
/

In this example, the space limit is changed to 30%. To learn about additional parameters used by the CONFIGURE procedure, see Oracle Database PL/SQL Packages and Types Reference.

15.6.2 Purging Policy

A weekly scheduled purging task manages the disk space used by SQL plan management. The task runs as an automated task in the maintenance window. Plans not used for more than 53 weeks are purged, as identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. The 53-week time frame ensures plan information will be available during any yearly SQL processing activity. The unused plan retention period can range between 5 and 523 weeks (a little more than 10 years).

To configure the retention period, use the CONFIGURE procedure of the DBMS_SPM PL/SQL package:

BEGIN
  DBMS_SPM.CONFIGURE(
    'plan_retention_weeks',105);
END;
/

In this example, the retention period is changed to 105 weeks. To learn about additional parameters used by the CONFIGURE procedure, see Oracle Database PL/SQL Packages and Types Reference.

15.6.3 SQL Management Base Configuration Parameters

The current configuration settings for the SQL management base can be viewed using the DBA_SQL_MANAGEMENT_CONFIG view. The following query shows this information:

select parameter_name, parameter_value from dba_sql_management_config;
 
PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        30
PLAN_RETENTION_WEEKS                       105

15.8.1 Overview of Stored Outline Migration

A stored outline is a set of hints for a SQL statement. The hints direct the optimizer to choose a specific plan for the statement. A stored outline is a legacy technique for providing plan stability.

Stored outline migration is the user-initiated process of converting stored outlines to SQL plan baselines. A SQL plan baseline is a set of plans proven to provide good performance.

This section contains the following topics:

• Purpose of Stored Outline Migration

• How Stored Outline Migration Works

• User Interface for Stored Outline Migration

• Basic Steps in Stored Outline Migration

15.8.1.2 How Stored Outline Migration Works

This section explains how the database migrates stored outlines to SQL plan baselines. This information is important for performing the task of migrating stored outlines.

15.8.1.2.1 Stages of Stored Outline Migration

The following graphic shows the main stages in stored outline migration:

Description of the illustration pfgrf231.gif

The migration process has the following stages:

1. The user invokes a function that specifies which outlines should be migrated.

2. The database processes the outlines as follows:

   1. The database copies information in the outline needed by the plan baseline.

      The database copies it directly or calculates it based on information in the outline. For example, the text of the SQL statement exists in both schemas, so the database can copy the text from outline to baseline.

   2. The database reparses the hints to obtain information not in the outline.

      The plan hash value and plan cost cannot be derived from the existing information in the outline, which necessitates reparsing the hints.

   3. The database creates the baselines.

3. The database obtains missing information when it chooses the SQL plan baseline for the first time to execute the same SQL statement.

   The compilation environment and execution statistics are only available during execution when the plan baseline is parsed and compiled.

The migration is complete only after the preceding phases complete.

15.8.1.2.2 Outline Categories and Baseline Modules

An outline is a set of hints, whereas a SQL plan baseline is a set of plans. Because they are different technologies, some functionality of outlines does not map exactly to functionality of baselines. For example, a single SQL statement can have multiple outlines, each of which is in a different outline category, but the only category that currently exists for baselines is DEFAULT.

The equivalent of a category for an outline is a module for a SQL plan baseline. Table 15-1 explains how outline categories map to modules.

Table 15-1 Outline Categories

Concept	Description	Default Value
Outline Category	Specifies a user-defined grouping for a set of stored outlines. You can use categories to maintain different stored outlines for a SQL statement. For example, a single statement can have an outline in the OLTP category and the DW category. Each SQL statement can have one or more stored outlines. Each stored outline is in one and only one outline category. A statement can have multiple stored outlines in different categories, but only one stored outline exists per category per statement. During migration, the database maps each outline category to a SQL plan baseline module.	DEFAULT
Baseline Module	Specifies a high-level function being performed. A SQL plan baseline can belong to one and only one module.	After an outline is migrated to a SQL plan baseline, module name defaults to outline category name
Baseline Category	Only one SQL plan baseline category exists. This category is named DEFAULT. During stored outline migration, the module name of the SQL plan baseline is set to the category name of the stored outline. A statement can have multiple SQL plan baselines in the DEFAULT category.	DEFAULT

When migrating stored outlines to SQL plan baselines, Oracle Database maps every outline category to a SQL plan baseline module with the same name. As shown in the following diagram, the outline category OLTP is mapped to the baseline module OLTP. After migration, DEFAULT is a super-category that contains all SQL plan baselines.

Description of the illustration pfgrf230.gif

15.8.2 Preparing for Stored Outline Migration

This section explains how to prepare for stored outline migration.

To prepare for stored outline migration: 

1. Start SQL*Plus and log on as a user with SYSDBA privileges or the EXECUTE privilege on the DBMS_SPM package.

   For example, do the following to use operating system authentication to log on to a database as SYS:

   % sqlplus /nolog
   SQL> CONNECT / AS SYSDBA
   

2. Query the stored outlines in the database.

   The following example queries all stored outlines that have not already been migrated to SQL plan baselines:

   SELECT NAME, CATEGORY, SQL_TEXT
   FROM   DBA_OUTLINES
   WHERE  MIGRATED = 'NOT-MIGRATED';
   

3. Determine which stored outlines meet the following prerequisites for migration eligibility:

   • The statement must not be a runtime INSERT AS SELECT statement.

   • The statement must not reference a remote object.

   • This statement must not be a private stored outline.

4. Decide whether to migrate all outlines, specified stored outlines, or outlines belonging to a specified outline category.

   If you do not decide to migrate all outlines, then list the outlines or categories that you intend to migrate.

5. Decide whether the stored outlines migrated to SQL plan baselines should use fixed plans or nonfixed plans:

   • Fixed plans

     A fixed plan is frozen. If a fixed plan is reproducible using the hints stored in plan baseline, then the optimizer always chooses the lowest-cost fixed plan baseline over plan baselines that are not fixed. Essentially, a fixed plan baseline acts as a stored outline with valid hints.

     A fixed plan is reproducible when the database can parse the statement based on the hints stored in the plan baseline and create a plan with the same plan hash value as the one in the plan baseline. If one of more of the hints become invalid, then the database may not be able to create a plan with the same plan hash value. In this case, the plan is nonreproducible.

     If a fixed plan cannot be reproduced when parsed using its hints, then the optimizer chooses a different plan, which can be either of the following:

     • Another plan for the SQL plan baseline

     • The current cost-based plan created by the optimizer

     In some cases, a performance regression occurs because of the different plan, requiring SQL tuning.

   • Nonfixed plans

     If a plan baseline does not contain fixed plans, then SQL Plan Management considers the plans equally when picking a plan for a SQL statement.

6. Before beginning the actual migration, ensure that the Oracle database meets the following prerequisites:

   • The database must be Enterprise Edition.

   • The database must be open and must not be in a suspended state.

   • The database must not be in restricted access (DBA), read-only, or migrate mode.

   • OCI must be available.

15.8.3 Migrating Outlines to Utilize SQL Plan Management Features

The goals of this task are as follows:

• To allow SQL Plan Management to select from all plans in a plan baseline for a SQL statement instead of applying the same fixed plan after migration

• To allow the SQL plan baseline to evolve in the face of database changes by adding new plans to the baseline

The scenario in this section assumes the following:

• You migrate all outlines.

  To migrate specific outlines, see Oracle Database PL/SQL Packages and Types Reference for details about the DBMS_SPM.MIGRATE_STORED_OUTLINE function.

• You want the module names of the baselines to be identical to the category names of the migrated outlines.

• You do not want the SQL plans to be fixed.

  By default, generated plans are not fixed and SQL Plan Management considers all plans equally when picking a plan for a SQL statement. This situation permits the advanced feature of plan evolution to capture new plans for a SQL statement, verify their performance, and accept these new plans into the plan baseline.

To migrate stored outlines to SQL plan baselines: 

1. In SQL*Plus, call PL/SQL function MIGRATE_STORED_OUTLINE.

   The following sample PL/SQL block migrates all stored outlines to fixed baselines:

   DECLARE
     my_report CLOB;
   BEGIN
     my_outlines := DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name => 'all' );
   END;
   /
   

15.8.4 Migrating Outlines to Preserve Stored Outline Behavior

The goal of this task is to migrate stored outlines to SQL plan baselines and preserve the original behavior of the stored outlines by creating fixed plan baselines. A fixed plan has higher priority over other plans for the same SQL statement. If a plan is fixed, then the plan baseline cannot be evolved. The database does not add new plans to a plan baseline that contains a fixed plan.

This section assumes the following:

• You want to migrate only the stored outlines in the category named firstrow.

  See Oracle Database PL/SQL Packages and Types Reference for syntax and semantics of the DBMS_SPM.MIGRATE_STORED_OUTLINE function.

• You want the module names of the baselines to be identical to the category names of the migrated outlines.

To migrate stored outlines to plan baselines: 

1. In SQL*Plus, call PL/SQL function MIGRATE_STORED_OUTLINE.

   The following sample PL/SQL block migrates stored outlines in the category firstrow to fixed baselines:

   DECLARE
     my_report CLOB;
   BEGIN
     my_outlines := DBMS_SPM.MIGRATE_STORED_OUTLINE( 
       attribute_name => 'category', 
       attribute_value => 'firstrow',
       fixed => 'YES' );
   END;
   /
   

   After the migration, the SQL plan baselines will be in module firstrow and category DEFAULT.

15.8.5 Performing Follow-Up Tasks After Stored Outline Migration

The goals of this task are as follows:

• To configure the database to use plan baselines instead of stored outlines for stored outlines that have been migrated to SQL plan baselines

• To create SQL plan baselines instead of stored outlines for future SQL statements

• To drop the stored outlines that have already been migrated to SQL plan baselines

This section assumes the following:

• You have completed the basic steps in the stored outline migration.

• Some stored outlines may have been created before Oracle Database 10g.

  Hints in in releases before Oracle Database 10g use a local hint format. After migration, hints stored in a plan baseline use the global hints format introduced in Oracle Database 10g.

This section explains how to set initialization parameters relating to stored outlines and plan baselines. The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES and CREATE_STORED_OUTLINES initialization parameters determine how and when the database creates stored outlines and SQL plan baselines. Table 15-5 explains the interaction between these parameters.

The USE_STORED_OUTLINES session parameter (it is not an initialization parameter) and OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter determine how the database uses stored outlines and plan baselines. Table 15-6 explains how these parameters interact.

To place the database in the proper state after the migration: 

1. Check that SQL plan baselines have been created as the result of migration.

   Ensure that the plans are enabled and accepted. For example, enter the following query (partial sample output included):

   SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE
   FROM   DBA_SQL_PLAN_BASELINES;
   
   SQL_HANDLE                     PLAN_NAME  ORIGIN         ENA ACC FIX MODULE
   ------------------------------ ---------- -------------- --- --- --- ------
   SYS_SQL_f44779f7089c8fab       STMT01     STORED-OUTLINE YES YES NO  DEFAULT
   .
   .
   .
   

2. Optionally, change the attributes of the SQL plan baselines.

   For example, the following statement changes the status of the baseline for the specified SQL statement to fixed:

   DECLARE
     v_cnt PLS_INTEGER;
   BEGIN 
     v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(               
                              sql_handle=>'SYS_SQL_f44779f7089c8fab', 
                              attribute_name=>'FIXED', 
                              attribute_value=>'NO');
     DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
   END;
   /
   

3. Check the status of the original stored outlines.

   For example, enter the following query (partial sample output included):

   SELECT NAME, OWNER, CATEGORY, USED, MIGRATED 
   FROM   DBA_OUTLINES
   ORDER BY NAME;
   
   NAME       OWNER      CATEGORY   USED   MIGRATED
   ---------- ---------- ---------- ------ ------------
   STMT01     SYS        DEFAULT    USED   MIGRATED
   STMT02     SYS        DEFAULT    USED   MIGRATED
   .
   .
   .
   

4. Drop all stored outlines that have been migrated to SQL plan baselines.

   For example, the following statements drops all stored outlines with status MIGRATED in DBA_OUTLINES:

   DECLARE
     v_cnt PLS_INTEGER;
   BEGIN 
     v_cnt := DBMS_SPM.DROP_MIGRATED_STORED_OUTLINE();
     DBMS_OUTPUT.PUT_LINE('Migrated stored outlines dropped: ' || v_cnt);
   END;
   /
   

5. Set initialization parameters so that:

   • When executing a SQL statement, the database creates plan baselines but does not create stored outlines.

   • The database only uses stored outlines when the equivalent SQL plan baselines do not exist.

   For example, the following SQL statements instruct the database to create SQL plan baselines instead of stored outlines when a SQL statement is executed. The example also instructs the database to apply a stored outline in category allrows or DEFAULT only if it exists and has not been migrated to a SQL plan baseline. In other cases, the database applies SQL plan baselines instead.

   ALTER SYSTEM 
     SET CREATE_STORED_OUTLINE = false;
   
   ALTER SYSTEM 
     SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true;
   
   ALTER SYSTEM 
      SET OPTIMIZER_USE_SQL_PLAN_BASELINES = true;
   
   ALTER SESSION
      SET USE_STORED_OUTLINES = allrows;