A Look at SQL Plan Management

All of us have experienced times when the runtime of a regularly executed report or query changes inexplicably and not always for the better.  The impact of these runtime changes can range from a simple annoyance to something that may severely impact business-critical operations. An organization requires consistent behavior from their systems with little to no regression in runtime. Database queries that have inconsistent response times in many cases exhibit what is known as plan instability. Oracle Database Enterprise Edition 11g introduces SQL Plan Management to address plan stability issues.

The Components of SQL Plan Management

The goal of SQL Plan Management is to provide a consistent execution plan regardless of changes made to the database environment. While consistency is great in the face of changes to an environment, it is important not to miss out when changes would improve the runtime execution. SQL Plan Management has three components that meet this goal.

• SQL Plan Baseline Capture

  • Create a baseline and a plan history for repeatable SQL.

• SQL Plan Baseline Selection

  • Only plans known to be performant are used.

• SQL Plan Baseline Evolution

  • Find plans in the plan in history that are better and add them to the baseline.

A SQL Plan Baseline is an accepted execution plan(s) for SQL statements under plan management. Only accepted plans in the baseline are used for SQL statements under plan management. If a new plan is created, that plan is added to the plan history. All of this information is stored in SQL Management Base in the SYSAUX tablespace.

SQL Plan Baseline Capture

Baseline Capture can be done automatically or via bulk loads. In the case of automatic capture, only SQL statements that have been repeated are captured. Automatic capture can be set at the session or system level by setting the parameter

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true (default is false). Setting this parameter at the session level is recommended as you can control the scope of the collection to a particular session.  Below is a typical sequence of events when using automatic capture.

• Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true at the session level.

• Execute the query to put into plan management two times.

• Exit the session.

After the second execution of the query, the SQL Plan Baseline is created and the first plan is added as both ENABLED and ACCEPTED. In order for a plan to be selected from the baseline, it has to be both ENABLED and ACCEPTED. All future executions of the query will use the plan from the baseline, even if a new plan was generated. Any new plans generated will be added to the history.

It is also possible to load plans in bulk from sources such as the cursor cache, stored outlines, and other databases, and if you have the license for the Diagnostics and Tuning Packs, you can load plans from SQL Tuning Sets and AWR. When loading plans manually or via bulk methods, the plans are enabled and accepted by default.

SQL Plan Baseline Selection

Once the baselines are created, plan selection is the next component of SQL Plan Management. When using SQL Plan Management, before a query is executed a check is first made to see if a SQL Plan Baseline exists.

If no baseline exists, the query executes as it would before SQL Plan Management–that is the plan generated is used to execute the query.  If, however, a baseline does exist, a plan from the baseline is used to execute the query and not the plan generated. Here is where plan stability is ensured. Only plans in the baseline that are ACCEPTED and ENABLED are used.

When new plans are generated, the plans are added to baseline as ENABLED, but not ACCEPTED. There is a very subtle point in this action that really needs to be understood. When a query is under plan management, every plan generated by the query, regardless if used or not, is documented and maintained. These collected plans can be added to the base through the process of SQL Plan Baseline Evolution.

SQL Plan Baseline Evolution

Over the normal course of system operations, changes will be made that provide better plans. We want to be able to take advantage of new plans made in response to these changes to produce faster results, but we want to avoid plans that produce slower results.

Using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE API plans in the history can be marked as ACCEPTED. Three things can be done with this API.

• Automatically ACCEPT the plan if it performs better than the existing SQL plan baseline.

• ACCEPT the plan without doing a performance verification.

• Do a performance comparison and generate a report, but do not evolve a new plan.

Conclusion

SQL Plan Management provides plan stability and only allows new plans to be used if they perform better than the baseline. With plan stability, a production system exhibits a consistent level of service to the business users. While these features alone are remarkable, what is more remarkable is that if you have a license for the Enterprise Edition of the Oracle database you have the ability to use SQL Plan Management now as it is included with Enterprise Edition.