15 Using SQL Plan Management

15.1 Managing SQL Plan Baselines

Managing SQL plan baselines involves three phases:

• Capturing SQL Plan Baselines

• Selecting SQL Plan Baselines

• Evolving SQL Plan Baselines

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;
/

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.2 Using SQL Plan Baselines with the SQL Tuning Advisor

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.

In Oracle Database 11g, an automatically configured task runs the SQL Tuning Advisor during a maintenance window. This automatic SQL tuning task targets high-load SQL statements as identified by the execution performance data collected in the Automatic Workload Repository (AWR) snapshots. The SQL profile recommendations made by the SQL tuning advisor are implemented by the automatic SQL tuning task. Tuned plans are thus automatically added to the SQL plan baselines of the identified high-load SQL statements.

15.3 Using Fixed SQL Plan Baselines

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.

15.4 Displaying SQL Plan Baselines

To view the plans stored in the SQL plan baseline for a given statement, use the DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package:

select * from table( 
    dbms_xplan.display_sql_plan_baseline( 
        sql_handle=>'SYS_SQL_209d10fabbedc741', 
        format=>'basic'));

The DISPLAY_SQL_PLAN_BASELINE function displays one or more execution plans for the specified SQL statement, specified by the handle (sql_handle). Alternatively, a single plan can be displayed by supplying a plan name (plan_name). For information about additional parameters used by the DISPLAY_SQL_PLAN_BASELINE function, see Oracle Database PL/SQL Packages and Types Reference.

This function uses plan information stored in the SQL management base to explain and display the plans. In this example, the DISPLAY_SQL_PLAN_BASELINE function displays the execution plans for the SQL statement specified by the handle SYS_SQL_209d10fabbedc741:

SQL handle: SYS_SQL_209d10fabbedc741
SQL text: select cust_last_name, amount_sold from customers c,
          sales s where c.cust_id=s.cust_id and cust_year_of_birth=:yob
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_bbedc741a57b5fc2
Enabled: YES      Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
----------------------------------------------------------------------------------
Plan hash value: 2776326082

----------------------------------------------------------------------------------
| Id  | Operation                                | Name                          |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                               |
|   1 | HASH JOIN                                |                               |
|   2 |   TABLE ACCESS BY INDEX ROWID            | CUSTOMERS                     |
|   3 |     BITMAP CONVERSION TO ROWIDS          |                               |
|   4 |     BITMAP INDEX SINGLE VALUE            | CUSTOMERS_YOB_BIX             |
|   5 |    PARTITION RANGE ALL                   |                               |
|   6 |    TABLE ACCESS FULL                     | SALES                         |
----------------------------------------------------------------------------------

----------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_bbedc741f554c408
Enabled: YES     Fixed: NO      Accepted: YES       Origin: MANUAL-LOAD
----------------------------------------------------------------------------------
Plan hash value: 4115973128

----------------------------------------------------------------------------------
| Id  | Operation                                | Name                          |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                               |
|   1 |   NESTED LOOPS                           |                               |
|   2 |     NESTED LOOPS                         |                               |
|   3 |       TABLE ACCESS BY INDEX ROWID        | CUSTOMERS                     |
|   4 |         BITMAP CONVERSION TO ROWIDS      |                               |
|   5 |           BITMAP INDEX SINGLE VALUE      | CUSTOMERS_YOB_BIX             |
|   6 |       PARTITION RANGE                    |                               |
|   7 |        BITMAP CONVERSION TO ROWIDS       |                               |
|   8 |          BITMAP INDEX SINGLE VALUE       | SALES_CUST_BIX                |
|   9 |     TABLE ACCESS BY LOCAL INDEX ROWID    | SALES                         |
----------------------------------------------------------------------------------

You can also display SQL plan baseline information using a SELECT statement directly on the DBA_SQL_PLAN_BASELINES view:

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
 
SQL_HANDLE                PLAN_NAME                      ENA  ACC    FIX
------------------------------------------------------------------------
SYS_SQL_209d10fabbedc741  SYS_SQL_PLAN_bbedc741a57b5fc2  YES  NO     NO
SYS_SQL_209d10fabbedc741  SYS_SQL_PLAN_bbedc741f554c408  YES  YES    NO

15.5 SQL Management Base

The SQL management base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement log, plan histories, and SQL plan baselines, as well as SQL profiles. To allow weekly purging of unused plans and logs, the SMB is configured with automatic space management enabled.

You can also add plans manually to the SMB for a set of SQL statements. This feature is especially useful when upgrading Oracle Database from a pre-11g version, since it helps to minimize plan regressions resulting from the use of a new optimizer version.

Because the SMB is stored entirely within the SYSAUX tablespace, SQL plan management and SQL tuning features will not be used if this tablespace is not available.

This section contains the following topics:

• Disk Space Usage

• Purging Policy

• SQL Management Base Configuration Parameters

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

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

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

15.6 Importing and Exporting SQL Plan Baselines

Oracle Database supports the export and import of SQL plan baselines using its import and export utilities or Oracle Data Pump. Use the DBMS_SPM package to define a staging table, which is then used to pack and unpack SQL plan baselines.

To import a set of SQL plan baselines from one system to another:

1. On the original system, create a staging table using the CREATE_STGTAB_BASELINE procedure:

   BEGIN
     DBMS_SPM.CREATE_STGTAB_BASELINE(
       table_name => 'stage1');
   END;
   /
   

   This example creates a staging table named stage1.

2. Pack the SQL plan baselines you want to export from the SQL management base into the staging table using the PACK_STGTAB_BASELINE function:

   DECLARE
   my_plans number;
   BEGIN
     my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
       table_name => 'stage1',
       enabled => 'yes',
       creator => 'dba1');
   END;
   /
   

   This example packs all enabled plan baselines created by user dba1 into the staging table stage1. You can select SQL plan baselines using the plan name (plan_name), SQL handle (sql_handle), or by any other plan criteria. The table_name parameter is mandatory.

3. Export the staging table stage1 into a flat file using the export command or Oracle Data Pump.

4. Transfer the flat file to the target system.

5. Import the staging table stage1 from the flat file using the import command or Oracle Data Pump.

6. Unpack the SQL plan baselines from the staging table into the SQL management base on the target system using the UNPACK_STGTAB_BASELINE function:

   DECLARE
   my_plans number;
   BEGIN
     my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
       table_name => 'stage1',
       fixed => 'yes');
   END;
   /
   

   This example unpacks all fixed plan baselines stored in the staging table stage1.

For more information about using the DBMS_SPM package, see Oracle Database PL/SQL Packages and Types Reference.