New Features of oracle11g-SQL Plan Management (SPM) and oracle11gspm

New Features of oracle11g-SQL Plan Management (SPM) and oracle11gspm

1. Introduction

Oracle Database11gR1 introduces SQL PlanManagement (SPM), a new tool that allows DBA to capture and maintain the optimal execution plan for any SQL statement. This restricts the refreshing of optimizer statistics and changes to existing applications, even the impact of database version upgrades. This article provides a basic understanding of SPM principles and briefly describes its performance optimization capabilities.

2. SPM principles and mechanisms

Oracle 11g implements a set of new features called SQL Plan Management (SPM) to solve unexpected deterioration of SQL plans in a simple and elegant way. As long as automatic SQL Plan Baseline capture is enabled for a user session, CBO records any SQL statements executed in the session in SQL Management Base (SMB) and converts the SQL statement text, outline is used to bind variables and the compiling environment to an SQL Plan Baseline.

As this is the first execution of a statement, Oracle11g regards the current execution plan as the best. It is the second execution of the same SQL statement that the elegance of SPM is more obvious. During the second execution of a statement, CBO compares the statement execution plan with the plan stored in SMB. The new plan is evaluated to see if it is more efficient than the plan in SMB.

If the new plan improves the statement performance, SPM marks the new plan as the best plan for the statement. As long as DBA does not modify the default setting (true) of the OPTIMIZER_USE_ SQL _PLAN_BASELINES parameter, CBO will adopt a new plan in the current statement execution. Otherwise, if the new plan reduces the statement performance, the CBO selects the lowest-cost plan from all acceptable plans in SMB, and, SPM will store the new plan in SMB, because in the near future, the new plan may become a good choice.

2.1. Capture SQL Plan Basebline

In Oracle11g, it is very easy to capture SQLPlan Baseline and store it in SMB. First, the OPTIMIZER_CAPTURE_ SQL _PLAN_BASELINES parameter controls whether automatic capture of SQL Plan Baselines is enabled. The default value of this parameter is FALSE, which means that SQL Plan Baselines is not automatically captured by default. However, when DBA sets it to TRUE in a session or system level, SPM begins to record the execution of SQL statements. When an SQL statement is executed more than once, this SQL statement is considered a candidate for SQL Plan Baselines.

Secondly, Oracle11g has a new package DBMS_SPM, which can capture and introduce a large number of SQL statements in advance by manually "Planting" the plan from the following sources:

One or more SQL statements in the database Library Cache can be used to create SQL PlanBaselines. LOAD_PLANS_FROM_CURSOR_CACHE can be used to capture a subset of any statements in the Library Cache as a candidate for SMB.

The SQL statements stored in SQL Tuning Set or an AWR snapshot can be captured and converted into SQL Plan Baselines by LOAD_PLANS_FROM_SQLSET.

Finally, SQL Plan Baselines can be imported from a storage table. This means that statements can be captured from different databases.

3. View SQL Plan Baseline information

The SQL Plan Baseline metadata captured and stored in SMB contains attributes of SPM and CBO used to control the Plan. When a new plan enters SMB, it is marked as ENABLED, but it cannot be marked as ACCEPTED:

1) CBO has evaluated the plan and determined it as the best plan;

2) The plan has been evolved into the ACCEPTED mode. Before CBO considers a plan, it must be marked as ENABLED and ACCEPTED.

The simplest way to view these metadata is to query the DBA_ SQL _PLAN_BASELINES dictionary view. The following is a summary of the most valuable information for controlling execution plans:

Table 1.1. SQL Plan Baseline Plan Control Metadata
Attribute	Description
SQL _HANDLE	A unique SQL identifier in string form; it can be used as a search key
PLAN_NAME	A unique SQL plan identifier in string form; it can be used as a search key
SQL _TEXT	The SQL statement's unnormalized, actual text
ORIGIN	Tells if the SQL Plan was either: AUTO-CAPTURE: Automatically captured MANUAL-LOAD: Manually evolved MANUAL-SQLTUNE: Automatically evolved by SQL Tuning Advisor AUTO-SQLTUNE: Automatically evolved by Automatic SQL Tuning
ENABLED	Indicates that the SQL Plan is enabled (YES) for CBO utilization or not (NO). Disabled plans are ignored by the CBO
ACCEPTED	Indicates that the SQL Plan is validated as a good plan, either because Oracle 11g has: Automatically accepted it, or The DBA has forced its manual acceptance by changing its status to ACCEPTED via procedure DBMS_SPM.ALTER_ SQL _PLAN_BASELINE ()
FIXED	SQL Plans whose FIXED attribute is set to YES will be considered by the CBO. If multiple plans are marked as FIXED, the CBO will only select the best execution plan from those so marked
OPTIMIZER_COST	The total cost estimated by the CBO to execute the SQL statement using this execution plan

To check whether an SQL Plan Baselines exists, you can use the new DBMS_XPLAN process DISPLAY_ SQL _PLAN_BASELINE to execute an SQL statement. For example, you can use this process to view all SQLPlan Baselines that match the SQL statement handle in SMB. If the Plan Name of an SQL statement is provided, the execution plan of the statement can also be displayed.

4. Implementation and process of automatic capture

Next, we analyze the process of automatically capturing SQL Plan Baselines. First, set the OPTIMIZER_CAPTURE_ SQL _PLAN_BASELINES parameter to TRUE (FALSE by default) to enable automatic capture of SQL Plan Baselines. We also need to set the OPTIMIZER_USE_ SQL _PLAN_BASELINES parameter to TRUE (default ). This parameter controls whether the CBO checks whether the plan generated by repeated SQL statement execution will be evaluated as a good plan.

Next, we run the same SQL statement twice. During the first execution, the SQL statement is recorded. During the second execution, the plan is automatically captured into SMB and marked as the SQLPlan Baseline of the statement ACCEPTED.

When the SQL statement is executed again and a new plan is generated, the plan is automatically captured into SMB but not marked as ACCEPTED, SPM only marks the first plan as ENABLED and ACCEPTED.

5. Evolution of SQL Plan Baseline

The SQL Plan Baselines in SPB that are not labeled as ACCEPTED must be further evolved into the ACCEPTED state before it can be used by the SQL statement that will be executed again in the future, there are two main types:

5.1. manual method

Call the evolve_ SQL _plan_baseline () function of the dbms_spm package.

SQL> var report clob;

SQL> exec: report: = dbms_spm.evolve_ SQL _plan_baseline ();

SQL> print: report

SQL> select SQL _text, plan_name, enabled, accepted fromdba_ SQL _plan_baselines;

Call the SQL Tuning Advisor Toolkit

SQL> var tname varchar2 (30 );

SQL> exec: tname: = dbms_sqltune.create_tuning_task (SQL _id => 'bfbr3zrg9d5cc ');

SQL> execdbms_sqltune.execute_tuning_task (task_name =>: tname );

SQL> selectdbms_sqltune.report_tuning_task (: tname, 'text', 'Basic ') FROM dual;

SQL> exec dbms_sqltune.accept_ SQL _profile (task_name =>: tname );

SQL> select SQL _text, plan_name, enabled, accepted from dba_ SQL _plan_baselines;

5.2. Automatic Method

Ø regularly schedule the evolve_ SQL _plan_baseline () of the dbms_spm package ()

Configure SQL TUNING ADVISOR to run automatically in the automatic task window

6. Specific operation commands

Enable automatic capture and use SPM

Alter session set optimizer_capture_ SQL _plan_baselines = TRUE;

Alter session set optimizer_use_ SQL _plan_baselines = TRUE;

View SPM metadata

COL creator         FORMAT A08      HEADING 'Creator'

COL hndle           FORMAT A08      HEADING 'SQL|Handle'

COL plnme           FORMAT A08      HEADING 'Plan|Name'

COL sql_hdr         FORMAT A25      HEADING 'SQL Text' WRAP

COL origin          FORMAT A12      HEADING 'Origin'

COL optimizer_cost  FORMAT 9999999  HEADING 'CBO|Cost'

COL enabled         FORMAT A04      HEADING 'Ena-|bled'

COL accepted        FORMAT A04      HEADING 'Acpt'

COL fixed           FORMAT A04      HEADING 'Fixd'

COL autopurge       FORMAT A04      HEADING 'Auto|Purg'

COL create_dt       FORMAT A11      HEADING 'Created|On' WRAP

COL lst_exc_dt      FORMAT A11      HEADING 'Last|Executed' WRAP

SELECT 

     creator

    ,SUBSTR(sql_handle, -8, 8) hndle

    ,SUBSTR(plan_name, -8, 8)  plnme

    ,SUBSTR(sql_text, 1, 75) sql_hdr

    ,origin

    ,optimizer_cost

    ,enabled

    ,accepted

    ,fixed

    ,autopurge

    ,TO_CHAR(created, 'yyyy-mm-dd hh24:mi:ss') create_dt

    ,TO_CHAR(last_executed, 'yyyy-mm-dd hh24:mi:ss') lst_exc_dt

  FROM dba_sql_plan_baselines

  WHERE (sql_text LIKE '%SPM%')

 ORDER BY 1,2,3;

Ø use DBMS_XPLAN.DISPLAY_ SQL _PLAN_BASELINE to display the reserved SQL Plan Baselines containing specific text

SET LINESIZE 150

SET PAGESIZE 2000

SELECT PT.*

   FROM (SELECT 

             DISTINCT sql_handle 

           FROM dba_sql_plan_baselines

          WHERE sql_text like '%SPM%') SPB,

        TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SPB.sql_handle, NULL,'TYPICAL +NOTE')) PT;

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.