Compare History Plan in oracle

posted Aug 4, 2011, 10:59 AM by Sachchida Ojha

Using SQL Plan Management

This chapter describes how to manage SQL execution plans using SQL plan management. SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information.

The execution plan of a SQL statement can be affected by various changes, such as:

  • New optimizer version

  • Changes to optimizer statistics and optimizer parameters

  • Changes to schema and metadata definitions

  • Changes to system settings

  • SQL profile creating

Some events may cause an irreversible change to an execution plan, such as dropping an index. These changes can cause regressions in SQL performance, and fixing them manually can be difficult and time consuming.

The SQL tuning features of Oracle Database generate SQL profiles that help the optimizer in producing well-tuned plans, but this is a reactive mechanism and cannot guarantee stable performance when drastic changes happen to the system. SQL tuning can only resolve performance issues after they have occurred and are identified. For example, a SQL statement may become a high-load statement due to a plan change, but this cannot be resolved by SQL tuning until after the plan change occurs.

SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

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 under a standard test configuration for the new SQL statements. If your system configuration is significantly different from the test configuration, the SQL plan baselines can be evolved over time to produce better performance.

This chapter contains the following topics:

15.1 Managing SQL Plan Baselines

Managing SQL plan baselines involves three phases:

15.1.1 Capturing SQL Plan Baselines

During the SQL plan baseline capture phase, Oracle Database records information about SQL statement execution to detect plan changes and decide whether it is safe to use new plans. To do so, the database maintains a history of plans for individual SQL statements. Because ad-hoc SQL statements do not repeat and thus do not suffer performance degradation, plan history is maintained only for repeatable SQL statements.

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.

For each SQL statement, the system maintains a plan history that contains all plans generated by the optimizer. However, before a plan in the plan history can be made acceptable for use by the optimizer, the plan must be verified to not cause performance regression. 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:

15.1.1.1 Automatic Plan Capture

When automatic plan capture is enabled, the system automatically creates and maintains the plan history for SQL statements using information provided by the optimizer. The plan history will include relevant information used by the optimizer to reproduce an execution plan, such as the SQL text, outline, bind variables, and compilation environment.

The initial plan generated for a SQL statement is marked as accepted for use by the optimizer, and represents both the plan history and the SQL plan baseline. All subsequent plans will be included in the plan history, and those plans that are verified not to cause performance regressions will be added to the SQL plan baseline during the SQL plan baseline evolution phase.

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

15.1.1.2 Manual Plan Loading

Another way to create SQL plan baselines is by manually loading existing plans for a set of SQL statements as SQL plan baselines. The manually loaded plans are not verified for performance, but are added as accepted plans to existing or new SQL plan baselines. You can use manual plan loading in conjunction with or as an alternative to automatic plan capture. Manual plan loading can be performed by:

15.1.1.2.1 Loading Plans from SQL Tuning Sets and AWR Snapshots

To load plans from a SQL Tuning Set, use the LOAD_PLANS_FROM_SQLSET function of the DBMS_SPM package:

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

In this example, the database loads the plans stored in SQL Tuning Set named tset1. To learn about additional parameters used by the LOAD_PLANS_FROM_SQLSET function, see Oracle Database PL/SQL Packages and Types Reference.

To load plans from Automatic Workload Repository (AWR), load the plans stored in AWR snapshots into a SQL Tuning Set before using the LOAD_PLANS_FROM_SQLSET function as described in this section.

15.1.1.2.2 Loading Plans from the Cursor Cache

To load plans from the cursor cache, use the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package:

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

In this example, Oracle Database loads the plans located in the cursor cache for the SQL statement identified by its sql_id. Plans in the cursor cache can be identified by:

  • SQL identifier (SQL_ID)

  • SQL text (SQL_TEXT)

  • One of the following attributes:

    • PARSING_SCHEMA_NAME

    • MODULE

    • ACTION

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn how to use the LOAD_PLANS_FROM_CURSOR_CACHE function

15.1.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.1.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:

15.1.3.1 Evolving Plans With Manual Plan Loading

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.

15.1.3.2 Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

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.

The following is an example of the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function:

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

Output:

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.

In this example, Oracle Database successfully evolved a plan for a SQL statement identified by its SQL handle. Alternatively, you can use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to specify:

  • The name of a particular plan that you want to evolve

  • A list of plans to evolve

  • No value

    This enables Oracle Database to evolve all non-accepted plans currently in the SQL management base.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function

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, SQL plan baselines, and 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:

15.5.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.5.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. Any plan that has not been 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 weeks 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.5.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.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;
    /
Comments