Using SQL Plan ManagementThis chapter describes how to manage SQL execution plans using SQL plan management. SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information. The execution plan of a SQL statement can be affected by various changes, such as:
Some events may cause an irreversible change to an execution plan, such as dropping an index. These changes can cause regressions in SQL performance, and fixing them manually can be difficult and time consuming. The SQL tuning features of Oracle Database generate SQL profiles that help the optimizer in producing well-tuned plans, but this is a reactive mechanism and cannot guarantee stable performance when drastic changes happen to the system. SQL tuning can only resolve performance issues after they have occurred and are identified. For example, a SQL statement may become a high-load statement due to a plan change, but this cannot be resolved by SQL tuning until after the plan change occurs. SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system. Common usage scenarios where SQL plan management can improve or preserve SQL performance include:
This chapter contains the following topics: 15.1 Managing SQL Plan BaselinesManaging SQL plan baselines involves three phases: 15.1.1 Capturing SQL Plan BaselinesDuring the SQL plan baseline capture phase, Oracle Database records information about SQL statement execution to detect plan changes and decide whether it is safe to use new plans. To do so, the database maintains a history of plans for individual SQL statements. Because ad-hoc SQL statements do not repeat and thus do not suffer performance degradation, plan history is maintained only for repeatable SQL statements. To recognize repeatable SQL statements, a statement log is maintained that contains identifiers of various SQL statements the optimizer has evaluated over time. A SQL statement is recognized as repeatable when it is parsed or executed again after it has been logged. For each SQL statement, the system maintains a plan history that contains all plans generated by the optimizer. However, before a plan in the plan history can be made acceptable for use by the optimizer, the plan must be verified to not cause performance regression. The set of all accepted plans in the plan history is the SQL plan baseline. The SQL Plan Baseline Capture phase can be configured for automatic capture of plan history and SQL plan baselines for repeatable SQL statements, or a set of plans can be manually loaded as SQL plan baselines. This section contains the following topics: 15.1.1.1 Automatic Plan CaptureWhen automatic plan capture is enabled, the system automatically creates and maintains the plan history for SQL statements using information provided by the optimizer. The plan history will include relevant information used by the optimizer to reproduce an execution plan, such as the SQL text, outline, bind variables, and compilation environment. The initial plan generated for a SQL statement is marked as accepted for use by the optimizer, and represents both the plan history and the SQL plan baseline. All subsequent plans will be included in the plan history, and those plans that are verified not to cause performance regressions will be added to the SQL plan baseline during the SQL plan baseline evolution phase. To enable automatic plan capture, set the 15.1.1.2 Manual Plan LoadingAnother way to create SQL plan baselines is by manually loading existing plans for a set of SQL statements as SQL plan baselines. The manually loaded plans are not verified for performance, but are added as accepted plans to existing or new SQL plan baselines. You can use manual plan loading in conjunction with or as an alternative to automatic plan capture. Manual plan loading can be performed by: See Also: "SQL Management Base"15.1.1.2.1 Loading Plans from SQL Tuning Sets and AWR SnapshotsTo load plans from a SQL Tuning Set, use the DECLARE In this example, the database loads the plans stored in SQL Tuning Set named To load plans from Automatic Workload Repository (AWR), load the plans stored in AWR snapshots into a SQL Tuning Set before using the 15.1.1.2.2 Loading Plans from the Cursor CacheTo load plans from the cursor cache, use the DECLARE In this example, Oracle Database loads the plans located in the cursor cache for the SQL statement identified by its
See Also: Oracle Database PL/SQL Packages and Types Reference to learn how to use theLOAD_PLANS_FROM_CURSOR_CACHE function15.1.2 Selecting SQL Plan BaselinesDuring the SQL plan baseline selection phase, Oracle Database detects plan changes based on the stored plan history, and selects plans to avoid potential performance regressions for a set of SQL statements. Each time a SQL statement is compiled, the optimizer first uses a cost-based search method to build a best-cost plan, then tries to find a matching plan in the SQL plan baseline. If a match is found, the optimizer will proceed using this plan. Otherwise, it evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost. The best-cost plan found by the optimizer that does not match any plans in the plan history for the SQL statement represents a new plan, and is added as a non-accepted plan to the plan history. The new plan is not used until it is verified to not cause a performance regression. However, if a change in the system (such as a dropped index) causes all accepted plans to become non-reproducible, the optimizer will select the best-cost plan. Thus, the presence of a SQL plan baseline causes the optimizer to use conservative plan selection strategy for the SQL statement. To enable the use of SQL plan baselines, set the 15.1.3 Evolving SQL Plan BaselinesDuring the SQL plan baseline evolution phase, the database evaluates the performance of new plans and integrates plans with better performance into SQL plan baselines. When the optimizer finds a new plan for a SQL statement, the plan is added to the plan history as a non-accepted plan. The plan can then be verified for performance relative to the SQL plan baseline performance. When a non-accepted plan is verified to not cause a performance regression, it is changed to an accepted plan and integrated into the SQL plan baseline. A successful verification of a non-accepted plan consists of comparing its performance to that of a plan selected from the SQL plan baseline and ensuring that it delivers better performance. This section describes how to evolve SQL plan baselines and contains the following topics: 15.1.3.1 Evolving Plans With Manual Plan LoadingYou can evolve an existing SQL plan baseline by manually loading plans either from the cursor cache or from a SQL tuning set. When you manually load plans into a SQL plan baseline, these loaded plans are added as accepted plans. See Also: "Manual Plan Loading"15.1.3.2 Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINEThe PL/SQL function The following is an example of the SET SERVEROUTPUT ON Output: REPORT In this example, Oracle Database successfully evolved a plan for a
SQL statement identified by its SQL handle. Alternatively, you can use
the
See Also: Oracle Database PL/SQL Packages and Types Reference for information about using theDBMS_SPM.EVOLVE_SQL_PLAN_BASELIN E function15.2 Using SQL Plan Baselines with the SQL Tuning AdvisorWhen tuning SQL statements with the SQL Tuning Advisor, if the advisor finds a tuned plan and verifies its performance to be better than a plan chosen from the corresponding SQL plan baseline, it makes a recommendation to accept a SQL profile. When the SQL profile is accepted, the tuned plan is added to the corresponding SQL plan baseline. However, the SQL Tuning Advisor will not verify existing unaccepted plans in the plan history. In Oracle Database 11g, an automatically configured task runs the SQL Tuning Advisor during a maintenance window. This automatic SQL tuning task targets high-load SQL statements as identified by the execution performance data collected in the Automatic Workload Repository (AWR) snapshots. The SQL profile recommendations made by the SQL tuning advisor are implemented by the automatic SQL tuning task. Tuned plans are thus automatically added to the SQL plan baselines of the identified high-load SQL statements. 15.3 Using Fixed SQL Plan BaselinesA SQL plan baseline is fixed if it contains at least one enabled plan whose If a fixed SQL plan baseline also contains non-fixed plans, the optimizer will give preference to fixed plans over non-fixed ones. This means that the optimizer will pick the fixed plan with the least cost even though a non-fixed plan may have an even lower cost. If none of the fixed plans is reproducible, then the optimizer will pick the best non-fixed plan. The optimizer will not add new plans to a fixed SQL plan baseline.
Since new plans are not automatically added, a fixed SQL plan baseline
is not evolved when When a SQL statement with a fixed SQL plan baseline is tuned using
the SQL Tuning Advisor, a SQL profile recommendation has special
meaning. When the SQL profile is accepted, the tuned plan is added to
the fixed SQL plan baseline as a non-fixed plan. However, as described
above, the optimizer will not use the tuned plan as long as a
reproducible fixed plan is present. Therefore, the benefit of SQL tuning
may not be realized. To enable the use of the tuned plan, manually
alter the tuned plan to a fixed plan by setting its 15.4 Displaying SQL Plan BaselinesTo view the plans stored in the SQL plan baseline for a given statement, use the select * from table( The This function uses plan information stored in the SQL management base to explain and display the plans. In this example, the SQL handle: SYS_SQL_209d10fabbedc741 You can also display SQL plan baseline information using a select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines; 15.5 SQL Management BaseThe SQL management base (SMB) is a part of the data dictionary that resides in the You can also add plans manually to the SMB for a set of SQL statements. This feature is especially useful when upgrading Oracle Database from a pre-11g version, since it helps to minimize plan regressions resulting from the use of a new optimizer version. Because the SMB is stored entirely within the This section contains the following topics: 15.5.1 Disk Space UsageDisk space used by the SQL management base is regularly checked against a limit based on the size of the To change the percentage limit, use the BEGIN In this example, the space limit is changed to 30%. To learn about additional parameters used by the 15.5.2 Purging PolicyA weekly scheduled purging task
manages the disk space used by SQL plan management. The task runs as an
automated task in the maintenance window. Any plan that has not been
used for more than 53 weeks are purged, as identified by the To configure the retention period, use the BEGIN In this example, the retention period is changed to 105 weeks. To learn about additional parameters used by the 15.5.3 SQL Management Base Configuration ParametersThe current configuration settings for the SQL management base can be viewed using the select parameter_name, parameter_value from dba_sql_management_config; 15.6 Importing and Exporting SQL Plan BaselinesOracle Database supports the export and import of SQL plan baselines using its import and export utilities or Oracle Data Pump. Use the To import a set of SQL plan baselines from one system to another:
|
DBA's Home Page - A website for Oracle, Greenplum and PostgreSQL DBA's > DBA's Forum, Routine Tasks and Activities >