Go Oracle 11g new Features-SQL Plan Management Description

An overview

Two-SQL planning baseline Plan Baseline architecture
Three-load SQL plan baselines
Four Evolutionary SQL plan baselines
Five important baseline SQL plan properties
Six-SQL Plan selection
Seven possible SQL plan manageability Scenarios
Eight SQL performance analyzers and SQL plan baseline scenarios
Nine automatically load SQL plan baseline scenarios
Ten clear SQL Management library policies

A Overview

There may be a performance risk when a SQL statement's SQL execution plan changes.

There are many reasons for SQL plan changes, such as optimizer version, optimizer statistics, optimizer parameters, scenario definition, System design, and SQL profile creation.

various scheduling control techniques (such as stored outlines (Storedoutline (9i)) and SQL Profiles (Sqlprofile (10g)) were introduced in previous versions of Oracle DB to address performance regressions resulting from planned changes. However, these technologies are passive processes that require manual intervention.

SQL Plan management is a new feature introduced with Oracle Database 11g that enables the system to automate the evolution of SQL plans by maintaining so-called "SQL Plan Baseline (11g)". When this feature is enabled, this integration can be done as long as it proves that the newly generated SQL plan integrates with the SQL plan baseline without causing performance regressions. Therefore, when you execute an SQL statement, you can only use the plan that is included in the corresponding SQL plan baseline. SQL plan baselines can be automatically loaded or implanted using SQL optimization sets.

SQL The main advantage of the planning management feature is that system performance is stable and no planned regression occurs. In addition, this feature can save a lot of DBA time, which is typically spent on identifying and analyzing SQL performance regressions and finding available solutions.

Two SQL Plan Baseline (Plan BaseLine): Architecture

The SQL Plan Management (SPM) feature introduces the infrastructure and services necessary to support planned maintenance and performance validation of the new plan.

for SQL statements that are executed more than once, the optimizer maintains a plan history for a single SQL statement. the optimizer identifies repeatable SQL statements by maintaining the statement log. If a logged SQL statement is parsed again or the statement is executed again, the SQL statement is identified as a repeatable statement. After an SQL statement is identified as repeatable, the various plans generated by the optimizer are maintained as plan histories that contain relevant information, such as SQL text, outlines, bound variables, and compilation environments, and the optimizer uses this information to replicate the execution plan.

As an alternative or supplement to automatically identify repeatable SQL statements and create their plan history, the system also supports the manual placement of plans for a series of SQL statements.

The plan history contains the different plans that the optimizer generated for the SQL statement during a certain period of time. However, only some of the plans in the plan history may be accepted and used. For example, a new schedule generated by the optimizer is not normally used, unless the plan is validated and does not result in a performance regression. Schedule validation is automatically completed when you run automatic SQL optimization as an Automation task in the Maintenance window .

The only goal of the automatic SQL Optimization task is to get a high-load SQL statement. To do this, the task automatically performs some actions, such as making a successful validated plan an accepted plan. a series of acceptable plans makes up a SQL plan baseline (plan baseline). The first plan generated for an SQL statement is obviously an acceptable plan, so the plan forms the original plan baseline. any new plans that the optimizer discovers later are included in the plan history, but are not initially included in the plan baseline.

statement logs, plan history, and plan baselines are stored in the SQL Management Library (SMB), which also contains SQL Profiles . SMB is part of the database dictionary and is stored in the Sysaux table space. SMB uses automatic space management (for example, regular cleanup of unused schedules). SMB can be configured to change the plan retention policy and set the space size limit.

Note: When using Oracledatabase 11g, the optimizer will not be able to access the SQL Management object if the DB instance is started but the Sysaux table space is offline. This may affect the performance of some SQL workloads.

Three. Load the SQL plan baseline

There are two ways to load a SQL plan baseline:

(1) Instant Capture:

Use automatic schedule capture by setting the initialization parameter Optimizer_capture_sql_plan_baselines to True. By default, this parameter is set to False. Setting this parameter to true turns on the ability to automatically identify repeatable SQL statements and automatically create schedule history for such statements.

(2) Bulk Load:

Use the DBMS_SPM package, which supports manual management of SQL plan baselines. With this package, you can load SQL plans from the cursor cache or an existing SQL optimization set (STS) directly into the SQL plan baseline. For SQL statements to be loaded from the STS to the SQL plan baseline, the SQL plan needs to be stored in the STS. You can use DBMS_SPM to change the state of the baseline plan from accepted to unacceptable (and never accept changes to accepted), you can also export the baseline plan from the staging table, and then use the exported baseline plan to load the SQL plan baseline into another database.

Four Evolutionary SQL plan baselines

During the evolution phase of the SQL planning baseline, Oracle DB evaluates the performance of the new plan in a regular manner and integrates a better-performing plan into the SQL plan baseline.

When the optimizer finds a new plan for the SQL statement, the plan is added to the plan history as an unacceptable plan. Then, compare the performance of the SQL plan baseline to verify the performance of the plan. If it is proven that an unacceptable schedule does not result in a performance regression (manual or automatic), the plan is changed to an accepted schedule and integrated into the SQL plan baseline. The process of successfully validating a non-accepted plan includes the performance of this plan and the performance of a plan selected from the SQL Plan baseline to ensure better performance.

There are two ways to evolve a SQL plan baseline:

(1) Use DBMS_SPM. The Evolve_sql_plan_baseline function. The function returns a report that shows whether some existing history plans have been moved to the plan baseline. You can also specify specific plans to test in the history.

(2) Run SQL Optimization Guide: Evolve SQL plan baselines by using SQL optimization guidance to manually or automatically optimize SQL statements. SQL Tuning guidance discovers an optimized plan and confirms that its performance is better than the planned performance selected from the corresponding SQL plan baseline, and generates a proposal to accept the SQL profile. Once the SQL profile is accepted, the optimized plan is added to the appropriate SQL plan baseline.

Five. Important Baseline SQL Plan properties

If you add a plan to the plan history, the plan is associated with some important attributes:

(1) SIGNATURE, sql_handle, Sql_text, and plan_name are important identifiers for search operations.

(2) Use Origin to determine whether the plan is automatically captured (auto-capture), manually evolved (Manualload), automatically evolved through SQL Optimization guidance (manual-sqltune), or automatically evolved through automatic SQL optimization ( Auto-sqltune).

(3) ENABLED and Accepted: The Enabled property indicates that the plan is enabled and is available for use by the optimizer. If enabled is not set, the system will not consider this schedule. The ACCEPTED property indicates that the schedule has been validated as a valid plan (automated by the system or manually by the user) when the user changes the schedule to ACCEPTED. If you change a schedule to accepted, only if you use DBMS_SPM. When Alter_sql_plan_baseline () changes its state, the plan is non-accepted. You can temporarily disable the accepted schedule by removing the enabled setting. The program must be enabled and accepted, and the optimizer will consider using it.

(4) FIXED indicates that the optimizer considers only plans marked as FIXED , regardless of other plans. For example, if there are 10 baseline plans, three of which are marked as fixed, the optimizer will use only the best plans in the three plans, ignoring all other plans. If a SQL plan baseline contains at least one fixed schedule that has been enabled, the SQL plan baseline is fixed. If new schedules are added to the repaired SQL plan baselines, these new plans cannot be used until the new plans are manually declared as fixed.

You can use the dba_sql_plan_baselines View to view the properties of each plan. Then, you can use DBMS_SPM. The Alter_sql_plan_baseline function changes some of these properties. You can also use DBMS_SPM. The Drop_sql_plan_baseline function deletes the schedule or the entire schedule history.

Note: The Dba_sql_plan_baselines view contains additional properties that you can use to determine when each plan was last used, and whether a schedule should be automatically cleared.

Six SQL Plan Selection

If you are using automatic scheduled capture, the first time an SQL statement is identified as repeatable, its best cost plan will be added to the corresponding SQL plan baseline. The plan will then be used to execute the corresponding statement.

If a plan baseline exists for an SQL statement, and the initialization parameter optimizer_use_sql_plan_baselines is set to True (the default), the optimizer uses a comparison plan to select the policy. Each time the SQL statement is compiled, the optimizer builds an optimal cost plan using the traditional cost-based search method, and then attempts to find a matching plan in the SQL plan baseline. If a matching schedule is found, the optimizer continues to run as usual. If a matching schedule is not found, the optimizer adds the new plan to the Plan history, calculates the cost of each accepted plan in the SQL Plan baseline, and selects the plan with the lowest cost. Copy these accepted plans using an outline stored with each of the accepted schedules. Therefore, the advantage of having a SQL plan baseline for SQL statements is that the optimizer always selects an accepted plan in that SQL plan baseline.

With SQL plan management, the optimizer can generate the best cost plan, or it can generate a baseline plan. This information will be dumped in the Other_xml column of the plan_table that explains the plan.

In addition, you can use the new Dbms_xplain.display_sql_plan_baseline function to display one or more execution plans for a given sql_handle in a plan baseline. If Plan_name is also specified, the corresponding execution plan is displayed.

Note: In order to preserve backward compatibility, the statement will be compiled with this storage outline if the stored outline pair of an SQL statement for a user session is active. In addition, even if automatic scheduled capture is enabled for the session, the optimizer is not stored in SMB with a plan that is generated by the storage outline.

Although the storage outline does not have any explicit migration procedures, you can use the Load_plan_from_cursor_cache procedure or the Load_plan_from_sqlset procedure in the DBMS_SPM package to migrate it to a SQL plan baseline. When the migration is complete, you should disable or delete the original storage outline.

Seven. Possible SQL plan manageability Scenarios

(1) Database Upgrade:

Bulk load SQL Plans are particularly useful when upgrading a system from an earlier version to Oracle Database 11g. To do this, you can capture a plan for a SQL workload to the SQL tuning set (STS) before the upgrade, and then load the plans from the STS into the SQL plan baseline immediately after the upgrade. This strategy minimizes the planned regression resulting from the use of the new optimizer version.

(2) New application Deployment:

Deploying a new application module means introducing new SQL statements into the system. The software vendor can provide the application software along with the corresponding SQL plan baselines for the newly introduced SQL statement. Because of the planned baseline, the new SQL statement will initially run with a plan known to have good performance under the standard test configuration. However, if the customer system configuration differs greatly from the test configuration, the plan baseline can evolve over time to produce better performance.

In both cases, you can use automatic SQL plan capture after a manual load to ensure that only good planning is used for future applications.

Eight SQL Performance Analyzer and SQL plan baseline scenarios

One variant of the first method is by using the SQL Performance Analyzer. You can capture the plans before Oracle database11g in the STS and import them to Oracle Database 11g. Then, set the initialization parameter optimizer_features_enable to 10g so that the optimizer operates this database as a 10g Oracle DB. Next, run the SQL Performance Analyzer for the Sts. After the run is complete, set the initialization parameter optimizer_features_enable back to 11g and rerun the SQL Performance Analyzer for the Sts.

The SQL Performance Analyzer generates a report that lists the SQL statements from 10g to 11g whose plans have occurred.

For SQL statements that SQL Performance Analyzer displays for performance regression due to the new optimizer version, you can use an STS to capture its schedule and then load the plans into SMB.

This approach provides the best form for planning the implant process, because it helps to prevent performance regressions while preserving the performance improvements that the database upgrade brings.

Nine Automatically load SQL plan baselines: Scenarios

Another upgrade scenario involves using the automatic SQL Plan capture mechanism. In this scenario, the initialization parameter optimizer_features_enable (OFE) is set to the version value prior to Oracledatabase 11g in the first period of time (such as a quarter), and then the automatic SQL plan capture is used to perform your workload after the upgrade.

During this initial period, the optimizer can replicate the plan for most SQL statements before Oracledatabase 11g due to the ofe parameter setting. Because automatic SQL plan captures are also initiated during this period, the program generated by the optimizer before Oracle Database 11g is captured as a SQL plan baseline.

At the end of the initial period, you can remove the settings for Ofe to take advantage of the new optimizer version when the minimum planned regression or unplanned regression occurs due to a plan baseline. The regression plan will use the previous optimizer version, and the non-regressive statement will benefit from the new optimizer version.

Ten Clear the SQL Management library policy

The system uses the defined limits to check the space occupied by the SQL Management Library (SMB) weekly. The limit is defined based on the percentage size of the Sysaux table space . By default, the space budget limit for SMB is set to 10% of the sysaux size. However, you can use DBMS_SPM. The CONFIGURE process configures SMB to change the space budget to a value between 1% and 50%.

if the SMB space exceeds the defined percent limit, a warning is written to the alert log. alerts are generated on a weekly basis by clearing some SQL Management objects, such as SQL plan baselines or SQL profiles, to increase the SMB space limit, increase the sysaux size, or reduce the SMB size.

The spatial management of the SQL plan baseline will be completed ahead of time with weekly cleanup tasks. The task runs as an automation task in the Maintenance window. any plans that have not been used for more than 53 weeks will be cleared. However, you can configure SMB to set the unused scheduled retention period to a value between 5 weeks and 523 weeks (slightly longer than in 10). To do this, you can use DBMS_SPM. CONFIGURE process.

You can view the current configuration settings for SMB by checking the Dba_sql_management_config view. In addition, you can use DBMS_SPM. The Drop_sql_plan_baseline function clears SMB manually.

For more information:

Http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf

Using SQL Plan Management

Http://docs.oracle.com/cd/E11882_01/server.112/e16638/optplanmgmt.htm

Source: http://blog.csdn.net/tianlesoftware/article/details/8292410