New Features of Oracle 11g-SQL Plan Management

I. Overview

Performance risks may occur when the SQL Execution Plan of an SQL statement is changed.

There are many reasons for changing the SQL plan, such as the Optimized Program version, optimized program statistics, optimized program parameters, solution definition, system design, and SQL profile creation.

In earlier versions of Oracle DB, various plan control technologies (such as storedoutline (9i) and SQL profile (SQLprofile (10g) were introduced ))), resolves the performance regression caused by planned changes. However, these technologies are passive processes that require manual intervention.

SQL plan management is a new feature introduced with Oracle Database 11g. It maintains the so-called "SQL plan baseline (11g )) to enable the system to automatically control the evolution of SQL plans. After this feature is enabled, you only need to verify that the newly generated SQL plan is integrated with the SQL plan baseline and does not cause performance regression. Therefore, when executing an SQL statement, you can only use the plan included in the corresponding SQL plan baseline. You can use the SQL optimization set to automatically load or implant the SQL plan baseline.

The main advantage of the SQL scheduler management function is that the system performance is stable and there will be no planned regression. In addition, this function can save DBA a lot of time, which usually takes to determine and analyze SQL Performance regression and find available solutions.

Ii. SQL Plan BaseLine: Architecture

The SQL scheduler Management (SPM) feature introduces the infrastructure and services necessary to support plan maintenance and Performance Verification for new plans.

For SQL statements executed multiple times, the optimizer maintains a plan history for a single SQL statement. The optimizer maintains statement logs to identify repeated SQL statements. If you perform a syntax analysis on a recorded SQL statement again or execute the statement again, the SQL statement is identified as a repeated statement. After an SQL statement is identified as repeated, various plans generated by the optimizer will be used as containing relevant information (such as SQL text, outline, variable binding, and compiling environment) plan history is maintained; the optimizer will use this information to replicate the execution plan.

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

Plan history records include different plans generated by the optimizer for SQL statements within a certain period of time. However, only some of the plans in the Plan history may be accepted and used. For example, a new plan generated by the optimizer is not normally used unless the plan is verified and does not result in performance regression. When you run automatic SQL optimization as an automated task in the maintenance window, the Plan verification is automatically completed.

The only goal of an automatic SQL optimization task is to obtain high-load SQL statements. To this end, the task automatically performs some operations, such as making a successful verified plan an accepted plan. A series of acceptable plans form an SQL plan baseline ). The first plan generated for an SQL statement is obviously an acceptable plan, which forms the original plan baseline. Any new plans subsequently discovered by the optimization program are included in the plan history, but are not initially included in the Plan baseline.

Statement logs, plan history records, and plan baselines are stored in the SQL management database (SMB). The database also contains SQL summary files. SMB is part of the database dictionary and is stored in the SYSAUX tablespace. SMB uses automatic space management (for example, clearing unused plans on a regular basis ). You can configure SMB to change the plan retention policy and set the size limit.

Note: When OracleDatabase 11g is used, if the database instance is started but the SYSAUX tablespace is OFFLINE, the optimizer cannot access the SQL management object. This may affect the performance of some SQL statements.

1 2 3 4 5 Next Page