oracle11g new features--sql Plan Management (SPM)

1. Introduction

Oracle DATABASE11GR1 introduces SQL Planmanagement (SPM), a set of new tools that allow DBAs to capture and maintain an optimal execution plan for any SQL statement, limiting the Refresh optimizer statistics to existing application changes, Even the impact of database version upgrades. This article helps to understand the principle of SPM and gives a brief description of its performance optimization capability.

2. SPM principle and mechanism

Oracle 11g implements a new set of features called SQL Plan Management (SPM) that addresses the unexpected deterioration of SQL plans through a simple and elegant approach. Whenever an automatic SQL Plan baseline capture is turned on for a user session, the CBO records any SQL executed within that session within SQL Management Base (SMB), putting SQL statement text, Synopsis (Outline), bound variables, and its compilation environment are stored as a SQL Plan Baseline.

Since this is the first execution of the statement, ORACLE11G will consider the execution plan at the time as optimal. It is in the second execution of the same SQL statement that the elegance of the SPM manifests itself more clearly. During the second execution of the statement, the CBO compares the execution plan of the statement with the plan stored in the SMB, and the new plan is evaluated to see if it is more efficient than the plan in SMB.

If the new plan improves the performance of the statement, the SPM will mark the new plan as the best plan for that statement. As long as the DBA does not modify the default setting for the Optimizer_use_sql_plan_baselines parameter (TRUE), the CBO will use the new plan in the current statement execution. Otherwise, if the new plan lowers the performance of the statement, the CBO chooses one of the lowest-cost plans from all the acceptable plans in SMB, and the SPM stores that new plan in SMB, because in the near future, the new plan might be a good choice.

2.1. Capturing 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 the SQL Plan baselines automatic capture is turned on, and the default setting for this parameter is false, which means that the SQL plan Baselines is not automatically captured by default. However, when the DBA sets it to TRUE,SPM at the session or system level, it begins to record the execution of the SQL statement, which is considered a candidate for the SQL Plan baselines capture when an SQL statement is executed more than once.

Second, ORACLE11G has a new package DBMS_SPM that can be pre-captured and introduced in a number of SQL statements by hand-planting from several sources:

? One or more SQL statements in the database library cache can be used to create SQL Planbaselines. Process Load_plans_from_cursor_cache can be used to capture a subset of any statement in the library cache as a candidate for potential SMB.

? SQL statements stored in SQL Tuning set or an AWR snapshot can be captured by the process Load_plans_from_sqlset and converted into SQL Plan Baselines.

? Finally, SQL Plan baselines can come from an imported storage table. This means that statements can be captured from different databases.

3. View SQL Plan Baseline information

The SQL plan baseline metadata that is captured and stored in SMB contains the properties that the SPM and CBO use to control the schedule. When the new plan enters SMB, it is marked as enabled, but accepted cannot be marked until:

1) The CBO has evaluated the plan and judged it to be the best plan;

2) The plan has been evolved into a accepted model. Before the CBO considers a plan, the plan must be marked as enabled and accepted.

The simplest way to view these metadata is to query the Dba_sql_plan_baselines dictionary view. Here is a summary of the most valuable information to control the execution plan:

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 the SQL Plan is enabled (YES) for CBO utilization or not (NO). Disabled plans is ignored by the CBO
ACCEPTED	Indicates that the SQL plan was validated as a good plan, either because Oracle 11g has: Automatically accepted it, or The DBA has forced it manual acceptance by changing it status to ACCEPTED via procedure DBMS_SPM. Alter_sql_plan_baseline ()
FIXED	SQL plans whose FIXED attribute is set to YES would be considered by the CBO. If multiple plans is marked as FIXED, the CBO would only select the best execution plan from those so marked
Optimizer_cost	The total cost of estimated by the CBO to execute the SQL statement using this execution plan

Another way to view the existing SQL Plan baselines that has a potential impact on one SQL statement is through the new procedure of Dbms_xplan Display_sql_plan_baseline. For example, you can use this procedure to view all SQLPlan Baselines that are matched by SMB and SQL statement handles, and if you provide a plan name for the SQL statement, you can also display the execution plan for that statement.

4. Implementation and process of automatic capture

Below, we analyze the process of automatically capturing SQL Plan baselines. First, we set the Optimizer_capture_sql_plan_baselines parameter to True (the default is False) to turn on SQL PLAN baselines Auto capture; we also need to put Optimizer_use_sql_ The Plan_baselines parameter is set to True (the default value). This parameter controls whether the CBO checks if the SQL statement repeats the resulting plan will be evaluated as a good plan.

Next, we execute the same SQL two times. At the first execution, the SQL statement is logged, and the second execution is scheduled to be automatically captured into SMB and marked as the SQLPlan Baseline of the statement accepted.

When the SQL statement is executed again in the future and a different new plan is generated, the plan is automatically captured into SMB, but is not marked as accepted, so the SPM will only mark the first plan as enabled and accepted.

5. Evolution of SQL Plan baseline

The SQL Plan Baselines, which is not marked as accepted in SPB, needs to be further evolved to indicate the accepted state before it can be used by SQL statements that are executed again in the future, with the following two ways to evolve SPB:

5.1. Manual Methods

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

? Calling 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

? Regular dispatch of the DBMS_SPM package Evolve_sql_plan_baseline ()

? Configure the SQL TUNING ADVISOR to run automatically in the Automatic Tasks window

6. Specific Operation commands

? Enable automatic capture and use of SPM

ALTER SESSION SET optimizer_capture_sql_plan_baselines=true;

ALTER SESSION SET optimizer_use_sql_plan_baselines=true;

? Viewing the SPM meta data

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

Creator

, SUBSTR (sql_handle,-8, 8) Hndle

, SUBSTR (Plan_name,-8, 8) Plnme

, SUBSTR (Sql_text, 1, 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;

? Through Dbms_xplan. Display_sql_plan_baseline displays reserved SQL PLAN Baselines that contain specific text

SET Linesize 150

SET PAGESIZE 2000

SELECT pt.*

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 NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

oracle11g new features--sql Plan Management (SPM)