15 Using SQL Plan Management

15.1.1 Purpose of SQL Plan Baselines

The goal of SQL plan 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 creation

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. However, this mechanism is reactive 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 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. Most of these plan changes result in either no performance change or improvement. However, some plan changes may cause performance regressions. SQL plan baselines significantly minimize potential regressions resulting from an upgrade.

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

• Deployment of new application modules means introducing new SQL statements into the database. The application software may use appropriate SQL execution plans developed in a standard test configuration for the new statements. If the system configuration is significantly different from the test configuration, then the database can evolve SQL plan baselines 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, that the optimizer generates for a SQL statement over time. Because only accepted plans are 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 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"

The SQL management base (SMB), which is part of the data dictionary, stores the SQL plan baselines and plan history in the SYSAUX tablespace. The SMB also contains SQL profiles. 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, the database maintains a statement log that contains the SQL ID of various SQL statements that the optimizer has evaluated. The database recognizes a SQL statement as repeatable when it is parsed or executed again after it has been logged.

For each repeatable SQL statement, the database 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.

You can configure the SQL Plan Baseline Capture phase for automatic capture of plan history and SQL plan baselines for repeatable SQL statements. Alternatively, you can manually load plans 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 the database compiles a SQL statement, the optimizer does the following:

1. Uses a cost-based search method to build a best-cost plan

2. Tries to find a matching plan in the SQL plan baseline

3. Does either of the following depending on whether a match is found:

   • If found, then the optimizer proceeds using the matched plan

   • If not found, then the optimizer 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. The database adds this plan as a nonaccepted plan to the plan history. The database does not use the new plan 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, then the optimizer selects 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 database adds the plan to the plan history as a nonaccepted plan. The database can verify the plan for performance relative to the SQL plan baseline performance. A successful verification of a nonaccepted plan consists of comparing its performance to that of a plan selected from the SQL plan baseline and ensuring that it delivers better performance. When the database verifies that a nonaccepted plan will not cause a performance regression, the database changes it to an accepted plan and integrates it into the baseline.

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 SMB 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 SYSAUX. The allowable range for this limit is between 1% and 50%.

A weekly background process measures the total space occupied by the SMB. When the defined limit is exceeded, the process writes a warning to the alert log. The database generates alerts weekly until one of the following conditions is met:

• The SMB space limit is increased

• The size of the SYSAUX tablespace is increased

• 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. The following example changes the space limit to 30%:

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

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.

The database purges plans not used for more than 53 weeks, as identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. The 53-week period ensures plan information is available during any yearly SQL processing. 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. The following example changes the retention period to 105 weeks:

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

15.6.3 SQL Management Base Configuration Parameters

You can access the current configuration settings for the SQL management base 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 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 run-time 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_report := 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 migration, the SQL plan baselines is 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 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 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;