Oracle 11g Execution Plan Management overview

the following is from: Http://www.51cto.com/art/200806/76223.htm

35.2 Execution Plan management

35.2.1 Overview

The execution plan for the same SQL statement may vary depending on the optimizer's version, optimization statistics, optimization parameters, and system settings. The execution plan of the SQL statement automatically changes, usually resulting in performance gains, but in some cases may result in degraded system performance. Prior to 11g, DBAs used storage outlines (Stored Outline) and SQL profiles to fix execution plans for some SQL statements, preventing performance degradation due to automatic system changes to execution plans. However, they all require manual, active configuration.

In the 11GR1 release, Oracle introduced SQL Execution Plan management (SQL Plan Management), which allows the system to automatically control and use better execution plans to prevent performance degradation resulting from changes in execution plans. Activate this option by setting Optimizer_capture_ Sql_plan_baselines to True. When this execution plan management new feature is activated, if a statement produces a new execution plan, the new plan will be used only if it is better than the referenced execution plan.

In order to implement execution plan management, the optimizer automatically maintains the plan history of execution for all SQL statements that have been executed more than once. The optimizer recognizes that the SQL statement is a second execution by maintaining a statement log (Statement log) executed by one of the statements. The plan history contains all the information that the optimizer can regenerate the execution plan, including SQL scripts, storage outlines, binding variables to compile the environment, and so on. Oracle also supports the scheduling history of manually maintaining SQL statements.

To get a better execution plan, the system defines a plan baseline. The execution plan baseline is a subset of the execution plan history, and the execution plan within the execution plan baseline is the reference base for comparison with the new execution plan. A new execution plan for an SQL statement is compared to all plans in the plan baseline, and if the performance is optimal, a new execution plan is executed. The execution plan for an SQL statement belongs to the plan history, but it does not necessarily belong to the plan baseline.

The declaration log, the execution plan history, and the plan baseline are all stored in the SQL Management Library (SMB), which also includes the SQL profile. SMB is part of the data dictionary and is stored in the Sysaux table space. The relationship between these elements is shown in 35-3.

Figure 35-3 Related element diagram

There are two ways to load an execution plan for an SQL statement into an execution plan baseline, ad hoc capture and block load.

(1) Ad hoc capture: Setting the initialization parameter Optimizer_capture_plan_baselines to ture automatically captures the SQL execution plan, which defaults to false. When set to True, it automatically recognizes duplicate SQL statements and automatically creates its plan history, and also puts the first generated execution plan into the plan baseline, which constitutes the initial baseline.

(2) Block loading: Manually manage plan baselines using the DBMS_SPM package. With the functionality provided by the package, you can directly load the SQL execution plan from the cursor buffer (cursor cache) or the existing tuning set into the plan baseline. DBMS_SPM can change the status of the baseline plan (available and unavailable) or clear the execution plan in the plan baseline. It can also export a plan baseline for facilitates to other databases.

During the change phase of the planning baseline, the Oracle database estimates the performance of the new plan and puts the execution plan for better performance into the plan baseline. When the optimizer discovers a new execution plan for the SQL statement, the new plan is added to the plan history, but at this point the property is unacceptable, and when it is automatically or manually confirmed that it does not degrade performance, the schedule attribute is modified to be acceptable and loaded into the plan baseline.

There are two ways to trigger a change in the plan baseline:

(1) Execution of DBMS_SPM. The Evolve_sql_plan_baseline function.

For example, the following script returns a report that lists whether the existing historical plan is placed in the plan baseline.

Variable report CLOB;
EXEC:REPORT:=DBMS_SPM. Evolve_sql_plan_baseline (
Sql_handle=> ' Sys_sql_456sd76kjhiuykh ');
Print Report

(2) Running SQL Tuning Advisor (Advisor): When optimizing SQL scripts with SQL Tuning Advisor, you can trigger changes to the plan baseline manually or automatically. When a tuning consultant discovers an optimized plan and proves that performance is better than an execution plan in the plan baseline, it recommends acceptance of the SQL profile, and when the SQL profile is accepted, the tuning plan is added to the corresponding plan baseline.

In the Dba_sql_plan_base_lines view, information about the execution plan is included. Several important attributes include the following:

· Origin: Represents the source of the execution plan, such as automatic capture, manual loading, manual tuning, and automatic tuning.

· Enabled: Indicates whether the execution plan is enabled and can be used by the optimizer.

· ACCEPTED: Indicates whether the execution plan is automatically or manually acknowledged as a good execution plan. An execution plan is only considered for use by the optimizer when enabled and accepted. can be passed DBMS_SPM. The Alter_sql_plan_baseline function changes its state.

· FIXED: Indicates that the optimizer considers only these execution plans. For example, when there are 10 execution plans in a plan baseline, and only 3 are set to fixed, the optimizer uses only the best of the 3 plans.

For example, view execution plan information:

Where Sql_text like ' select% ';

In addition, you can change the execution plan property values. For example:

Variable CNT number;




Attribute_value = ' NO ');

You can also clear an execution plan. For example:

Variable CNT number;
EXEC:CNT: = Dbms_spm.drop_sql_plan_baseline (' sys_sql_6fe28d438dfc352f ');

With the Dba_sql_management_config view, you can get information such as the spatial configuration and cleanup policies of SMB, and can be dbms_spm. Configure process modification. For example:

Sql>exec dbms_spm.configure (' space_budget_percent ', 20);
Sql>exec dbms_spm.configure (' Plan_retention_weeks ', 100);

Set the execution plan to occupy 20% of the Sysaux space (default is 10%) and the retention period to 100 weeks, respectively.

Oracle 11g Execution Plan Management overview