SQL Plan Management (SPM) for SQL optimization (SQL TUNING) features in oracle11g

1. Brief introduction

Oracle DATABASE11GR1 introduces SQL Planmanagement (SPM), a set of new tools that allow DBAs to capture and maintain discretionary SQL statements to run the best plan, limiting refresh optimizer statistics. There has been an application change. Even the impact of a database version number upgrade. 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. For a user session to open its own active SQL Plan baseline capture, the CBO will record SQL Management Base (SMB) for whatever SQL is running within that session, SQL statement text, Synopsis (Outline), bound variable, and its compilation environment are stored as a SQL Plan Baseline.

Because this is the first time the statement is run, ORACLE11G will consider the operational plan as optimal. It is when the same SQL statement is run the second time. The elegance of the SPM is more pronounced. During the second run of the statement, the CBO will plan the run of the statement and the plan stored in SMB, and the new plan is evaluated to see if it is more efficient than the plan in SMB.

Assuming that the new plan improves the performance of the statement, then. The SPM will mark the new plan as the best plan for that statement.

Only if the DBA does not change the default setting (true) of the Optimizer_use_sql_plan_baselines parameter, the CBO will use the new plan in the current statement run. Otherwise, assuming that the new plan reduces the performance of the statement, the CBO chooses one of the lowest-cost plans from all acceptable plans in SMB. Also, the SPM will store the new plan in SMB, which may be a good choice in the near future.

2.1. Capturing SQL Plan Basebline

In oracle11g, it's easy to capture SQLPlan baseline and store it in SMB. First of all. Optimizer_capture_sql_plan_baselines parameter Control SQL plan baselines whether the active capture is turned on, the default setting for this parameter is false, which means that the SQL plan Baselines is not actively captured by itself. However, the DBA sets it to TRUE,SPM at the session or system level to start recording the operation of the SQL statement. When an SQL statement is run more than once. The SQL statement is thought to be a candidate for the SQL Plan baselines capture.

Secondly. ORACLE11G has a new package dbms_spm, which is able to pre-capture and introduce a large number of SQL statements by hand-planted 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 whatever 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.

? At last. 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 includes 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 is not yet marked. Until:

1) The CBO has evaluated the plan and inferred it as 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. The following is a summary of the most valuable information to control the operational 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 see the existing SQL Plan baselines that has a potential impact on running an 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; Suppose you provide a plan name for the SQL statement, and you can also display the statement's run schedule.

4. Implementation and process of self-active capture

Following. We analyze our own initiative to capture the SQL Plan baselines process. First, we set the Optimizer_capture_sql_plan_baselines parameter to True (which defaults to false) to open the SQL PLAN baselines itself and actively capture it. We also need to set the Optimizer_use_sql_plan_baselines parameter to True (the default value). This parameter controls whether the CBO checks if the SQL statement will be evaluated as a good plan.

Then. We run the same SQL two times. The first time the runtime is run. The SQL statement is logged, and the second time it is run, it plans to take its own initiative to be captured into SMB and marked as the SQLPlan Baseline of the statement accepted.

When the SQL statement is run again in the future and a different new plan is generated, the plan is also proactively captured into SMB. But it 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 into a accepted state, capable of being used by SQL statements to be run again in the future, and the way in which SPB evolves, there are, for example, the following two types:

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. Self-motivated approach

? Regular dispatch of the DBMS_SPM package Evolve_sql_plan_baseline ()

? Configure the SQL TUNING ADVISOR to proactively execute itself on its own active task form

6. Detailed Operation command

? Open your own active 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 include 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;

SQL Plan Management (SPM) for SQL optimization (SQL TUNING) features in oracle11g