Oracle Database 11g: SQL Plan Management, Part 1
January 29, 2008
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 11gs 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 DBAs 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:
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 statements 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 statements 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 thats 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 statements 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. Its during the next execution of the same SQL statement that the elegance of SQL Plan Management becomes evident. During the statements second execution, the CBO compares the statements execution plan against the one thats 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 its more effective than the current execution plan in the SMB.
If the new execution plan would cause the statements performance to improve, then SPM marks the new plan as the best choice for the execution of the statement. As long as the DBA hasnt overridden the default value of TRUE for the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter, the CBO uses the new plan during the statements current execution. Otherwise, if the statements 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 its 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 parameters default setting, FALSE, means that SQL Plan Baselines arent 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:
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, its marked as ENABLED, but it wont 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. Heres a summary of the most valuable plan control information found there:
Another way to view the potential impact of existing SQL Plan Baselines on a SQL statements 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 statements handle in the SMB; if I supply the SQL statements 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, Ill 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 Ive tagged this statement with an appropriate comment (SPM_1.1) so that its easier to track its state as it progresses towards an accepted plan baseline.
Now Im ready for experimenting with automatic capture of SQL Plan Baselines. As shown in Listing 1.2, Ill set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE for just this session to activate automatic capture of SQL Plan Baselines, and Ill set OPTIMIZER_MODE to ALL_ROWS so that the CBO optimizes the SQL statements 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, Ive 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 Ill execute statement SPM_1.1 twice. Its first execution insures that the statements execution is logged into the SMB, but its the statements 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, Ill 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 Ive abbreviated the SQL statements hash value and its plan baseline to their last eight characters for easier handling.
Finally, Ill change the OPTIMIZER_MODE to FIRST_ROWS so that the CBO optimizes the SQL statements corresponding execution plan to return the first few rows of the querys 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 ddc1fcd0s 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. Ive utilized DBMS_XPLANs 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.
Ive introduced SQL Plan Management theory and some rudimentary examples of how to use it effectively, but theres still a plethora of performance tuning scenarios to cover. In the next article in this series, Ill delve into how to:
References and Additional Reading
While Im hopeful that Ive given you a thorough grounding in the technical aspects of the features Ive discussed in this article, Im also sure that there may be better documentation available since its 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 Ive 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