Oracle Database 11g: SQL Plan Management, Part 1

Synopsis. Oracle Database 11gR1 introduces SQL Plan Management, a new set of tools that allows an Oracle DBA to capture and preserve the most efficient execution plans for any SQL statement, thus limiting the impact of refreshed optimizer statistics, changes to existing applications, and even upgraded database versions. This article – the first in this series – provides a primer to SQL Plan Management principles and offers a simple demonstration of its performance tuning capabilities.

The previous article series explored the capabilities of Oracle Database 11g’s new SQL Performance Analyzer (SPA) tool set to capture and analyze performance statistics for SQL statements and thus more accurately predict the impact of environmental changes upon those statements’ performance before the environment has been changed. However, while this is a valuable addition to any Oracle DBA’s “tool belt,” any experienced DBA will tell you that this is only half the battle.

A much more insidious problem crops up when a valid (and excellent!) execution plan for a SQL statement gets invalidated and the statement is reparsed, but the resulting new execution plan is actually much worse than the original plan. This can happen for several reasons:

• It’s not uncommon for a cursor to get aged out of the Library Cache eventually. Because this necessitates a hard parse of its SQL statement, there is always a chance that the cursor may end up using a less efficient execution plan.
• A cursor may also be invalidated when optimizer statistics are recalculated on that cursor’s dependent objects, or when even one of that cursor’s dependent object(s) is modified.
• There are other, less common causes of changes to a SQL statement’s plan stability: a change in optimizer versions, initialization parameter modifications, and even (after Oracle 10g) the creation of a new SQL Profile for the statement.

SQL Plan Management: A Brief History Lesson

One of the biggest challenges any DBA faces is the ability to identify not only which SQL statements are performing poorly, but also capturing the best execution plans for SQL statements and making those plans available to all statements that could benefit from them. The evolution of these solutions makes for an interesting history lesson.

Phase 1: Stored Outlines. Oracle 8i provided a mechanism to capture a SQL statement’s execution plan and save it into a stored outline. A stored outline is essentially a SQL statement that a DBA or developer has optimized – sometimes using tuning tools, and many times using just simple trial and error – and then stored within the OUTLN schema. When a SQL statement is executed and that statement’s execution plan matches that of the stored outline, Oracle uses the SQL statement within the stored outline instead. Stored outlines can also be staged for eventual execution within categories, or limited to only those sessions that have a matching category.

This provides what Oracle 8i terms plan stability, and while it can be useful, it does have some drawbacks, the most serious one being that stored outline plans over time become outdated because of changes to data distributions or optimizer statistics. Then the stored outline has the tendency to force a statement to actually use a plan that’s considerably less efficient than if a new plan was derived by simply reparsing the statement and obtaining a better execution plan.

Phase 2: SQL Profiles. Oracle Database 10g addressed the shortcomings of stored outlines with a new method called SQL Profiles. The DBA uses either the SQL Tuning Advisor or SQL Access Advisor to identify SQL statements that can benefit from better performance. These statements can be stored within a SQL Tuning Set, an AWR snapshot, or even within the current library cache. Once identified as tuning candidates, these Advisors analyze the captured statements for better performance opportunities, and then generate special extensions to the statements’ execution plans called SQL Profiles that rewrite the SQL statements to obtain the best possible performance during execution.

Like a stored outline, a SQL profile provides the ability to use a better execution plan if one is available. SQL profiles can also be staged for execution just like stored outlines, or limit execution of a SQL Profile to only particular sessions. But the most significant improvement over stored outlines is twofold: (a) the ability for limited self-tuning to insure a SQL Profile was providing the best possible execution plan; and (b) the ability to detect when a SQL Profile was no longer efficient (and thus have ADDM suggest that a new SQL Profile needs to be generated).

Phase 3: SQL Plan Management. Oracle 11g implements a new feature set called SQL Plan Management (SPM) that overcomes unexpected SQL execution plan regression via a simple yet elegant methodology. Once a user session has been enabled for automatic SQL Plan Baseline capture, the cost-based optimizer (CBO) logs any SQL statements executed within that session into the SQL Management Base (SMB). This stores the statement’s SQL text, its outline, its bind variables, and its compilation environment as a SQL Plan Baseline.

Since this is the first time the statement has been executed, Oracle 11g treats the stored execution plan as optimal. It’s during the next execution of the same SQL statement that the elegance of SQL Plan Management becomes evident. During the statement’s second execution, the CBO compares the statement’s execution plan against the one that’s currently stored in the SMB. If the CBO detects a new plan, the new plan is first added to plan history, and then the new plan is evaluated to see if it’s more effective than the current execution plan in the SMB.

If the new execution plan would cause the statement’s performance to improve, then SPM marks the new plan as the best choice for the execution of the statement. As long as the DBA hasn’t overridden the default value of TRUE for the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter, the CBO uses the new plan during the statement’s current execution. Otherwise, if the statement’s performance would actually regress because the new plan was used, then the CBO chooses the one plan from all accepted plans in the SMB that will result in the lowest execution cost. Either way, however, SPM will store the new execution plan within the SMB, because it’s possible that the new plan may actually become a better choice in the near future.

Capturing SQL Plan Baselines

The good news is that Oracle 11g makes it extremely easy to capture SQL Plan Baselines into the SMB. First, the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter controls whether automatic capture of SQL Plan Baselines is activated. This parameter’s default setting, FALSE, means that SQL Plan Baselines aren’t automatically captured. However, as soon as the DBA sets it to TRUE at either the session or system level, SPM begins to log the execution of SQL statements; when a SQL statement is executed more than once, that statement will be considered as a candidate for SQL Plan Baseline capture.

Second, a new Oracle 11g package, DBMS_SPM, provides the ability to capture and introduce high-volume SQL statements in advance by “seeding” plans manually from several sources:

• One or more SQL statements currently found in the database’s Library Cache can be used to create SQL Plan Baselines. Procedure LOAD_PLANS_FROM_CURSOR_CACHE can be used to capture any subset of statements in the Library Cache as potential SMB candidates.
• SQL statements stored in either a SQL Tuning Set or an AWR Snapshot can be captured and translated into SQL Plan Baselines via the LOAD_PLANS_FROM_SQLSET procedure.
• Finally, SQL Plan Baselines can be populated from SQL statements in an imported staging table. This means it’s possible to capture statements from a totally different database – say, a QA environment – and “pre-seed” them into the current production environment. (I’ll spend a lot more time on how this can be an excellent technique to prevent plan regression during database or application upgrades in future articles in this series.)

Viewing SQL Plan Baseline Information

The SQL Plan Baseline metadata captured in the SMB contains attributes that SPM and the CBO uses for plan control. When a brand-new plan first arrives in the SMB, it’s marked as ENABLED, but it won’t be marked as an ACCEPTED plan until either (a) the CBO has evaluated the plan and judged it the best plan, or (b) the plan has been evolved into ACCEPTED mode. Before the CBO considers using a plan for execution, it must be marked as both ENABLED and ACCEPTED.

The simplest way to view this metadata is to query the DBA_SQL_PLAN_BASELINES data dictionary view. Here’s a summary of the most valuable plan control information found there:

Table 1.1. SQL Plan Baseline Plan Control Metadata
Attribute	Description
SQL_HANDLE	A unique SQL identifier in string form; it can be used as a search key
PLAN_NAME	A unique SQL plan identifier in string form; it can be used as a search key
SQL_TEXT	The SQL statement’s unnormalized, actual text
ORIGIN	Tells if the SQL Plan was either: AUTO-CAPTURE: Automatically captured MANUAL-LOAD: Manually evolved MANUAL-SQLTUNE: Automatically evolved by SQL Tuning Advisor AUTO-SQLTUNE: Automatically evolved by Automatic SQL Tuning
ENABLED	Indicates that the SQL Plan is enabled (YES) for CBO utilization or not (NO). Disabled plans are ignored by the CBO
ACCEPTED	Indicates that the SQL Plan is validated as a good plan, either because Oracle 11g has: Automatically accepted it, or The DBA has forced its manual acceptance by changing its status to ACCEPTED via procedure DBMS_SPM.ALTER_SQL_PLAN_BASELINE()
FIXED	SQL Plans whose FIXED attribute is set to YES will be considered by the CBO. If multiple plans are marked as FIXED, the CBO will only select the best execution plan from those so marked
OPTIMIZER_COST	The total cost estimated by the CBO to execute the SQL statement using this execution plan

Another way to view the potential impact of existing SQL Plan Baselines on a SQL statement’s execution is via the new DISPLAY_SQL_PLAN_BASELINE procedure of package DBMS_XPLAN. For example, I can use this procedure to view all SQL Plan Baselines that match a SQL statement’s handle in the SMB; if I supply the SQL statement’s plan name as well, then its execution plan will also be shown.

Automatic SQL Plan Capture: A Simple Example

To demonstrate exactly how SQL Plan Management selects the most effective execution plan for a SQL statement, I’ll start with an extremely simple example: a SQL statement (see Listing 1.1) that retrieves a relatively small subset of data from several tables in the Sales History (SH) schema. Note that I’ve tagged this statement with an appropriate comment (SPM_1.1) so that it’s easier to track its state as it progresses towards an accepted plan baseline.

Now I’m ready for experimenting with automatic capture of SQL Plan Baselines. As shown in Listing 1.2, I’ll set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE for just this session to activate automatic capture of SQL Plan Baselines, and I’ll set OPTIMIZER_MODE to ALL_ROWS so that the CBO optimizes the SQL statement’s corresponding execution plan to return the entire result set as efficiently as possible. To insure that this session will utilize any SQL Plan Baselines that are captured, I’ve also set initialization parameter OPTIMIZER_USE_SQL_PLAN_BASELINES to TRUE (its default value). This parameter controls whether the CBO should check if the next repeatable execution of the SQL statement should be evaluated for a possible better execution plan.

Then I’ll execute statement SPM_1.1 twice. Its first execution insures that the statement’s execution is logged into the SMB, but it’s the statement’s second execution that causes its execution plan to be automatically accepted as a SQL Plan Baseline for the statement. To show the current state of the SMB in this point of my experiments, I’ll execute the query shown in Listing 1.3 against the DBA_SQL_PLAN_BASELINES view. As the resulting output shows, the current statement has indeed been captured into the SMB via automatic capture means. (Note that I’ve abbreviated the SQL statement’s hash value and its plan baseline to their last eight characters for easier “handling.”

Finally, I’ll change the OPTIMIZER_MODE to FIRST_ROWS so that the CBO optimizes the SQL statement’s corresponding execution plan to return the first few rows of the query’s result set as soon as possible – the obvious antithesis of the ALL_ROWS setting, which strives for maximum throughput. Listing 1.4 shows the commands I issued to achieve this, as well as the results of querying the DBA_SQL_PLAN_BASELINES view once again. This query output clearly shows that both execution plans are stored within the SMB, but plan 07e0351f is indeed more efficient – its optimizer cost is only 757, as compared to plan ddc1fcd0’s optimizer cost of 2388. Accordingly, SPM marks only the first plan as both ENABLED and ACCEPTED.

Finally, Listing 1.5 shows the details of both execution plans. I’ve utilized DBMS_XPLAN’s new DISPLAY_SQL_BASELINE procedure to display their exact execution methods; note that the SPM has captured the corresponding SQL Plan Baseline and all of its optimizer statistics.

Next Steps

I’ve introduced SQL Plan Management theory and some rudimentary examples of how to use it effectively, but there’s still a plethora of performance tuning scenarios to cover. In the next article in this series, I’ll delve into how to:

• Capture SQL Plan Baselines via manual methods
• Use SQL Plan Baselines to prepare for application upgrades
• “Evolve” a SQL Plan Baseline manually

References and Additional Reading

While I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that I’ve drawn upon the following Oracle Database 11gR1 documentation for the deeper technical details of this article:

B28274-01 Oracle Database 11gR1 Performance Tuning Guide

B28279-02 Oracle Database 11gR1 New Features Guide

B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types Reference

The following notes in Oracle MetaLink also provide valuable background information on stored outlines in Oracle 8i and beyond:

67536.1: Using Stored Outlines

102311.1: How to Move Stored Outlines for One Application from One Database to Another

132547.1: Stored Outlines Quick Reference

» See All Articles by Columnist Jim Czuprynski