Synopsis. Oracle Database 11gR1 offers a new tool set, SQL Plan Management (SPM), that lets any Oracle DBA capture and preserve the most efficient execution plans for any SQL statement. The final article in this series illustrates how to use SPM to limit unexpected regression of existing SQL statement performance before those statements first encounter Oracle 11gs cost-based optimizer. It also reveals several SQL Plan Management features that allow extremely granular management of the SQL Management Base (SMB).
The previous article in this series explored how Oracle Database 11gs new SQL Plan Management (SPM) features can be used to capture and load SQL Plan Baselines for Oracle 10g databases about to be upgraded to Oracle 11g so that SQL statement regression is eliminated during the database upgrade process. That article also illustrated methods to ensure that SQL statements for new application code would choose the most efficient execution plans even before the application is deployed in production.
This article the final one in this series will show how to use SPM to:
- Limit unnecessary regression of SQL statements when they attempt to take advantage of new Oracle 11g optimizer features
- Capture SQL Plan Baselines for specific SQL statements via manual methods
- Control the evolution of existing SQL Plan Baselines
- Manage the contents of the SQL Plan Management Baseline (SMB), including how to purge obsolete or undesired SQL Plan Baselines
SPM Scenario #3: Using SQL Performance Analyzer (SPA) Against Prior Optimizer Versions
The first scenario in the prior article illustrated how SPM could be used to capture SQL Plan Baselines for SQL statements whose performance would regress during an impending upgrade from Oracle 10g to Oracle 11g. That scenario involved actually executing the statements that made up the SQL Workload on an existing Oracle 10g database. An alternate method to solve this issue is to simulate an Oracle 10g environment in an existing Oracle 11g database environment by manipulating the value for the OPTIMIZER_FEATURES_ENABLE initialization parameter.
Preparing the Simulation. To illustrate this scenario, Ill utilize the same SQL statements that I captured earlier into SQL Tuning Set STS_SPM_200 as part of SPM Scenario #2 in the previous article. Before I perform any new analyses, however, Ill first remove any SQL Plan Baselines from the SQL Management Base (SMB) that were created during prior analyses. Ill use function DBMS_SPM.DROP_SQL_PLAN_BASELINE to remove just those statements that are tagged with a comment string of SPM_2 (see Listing 3.1). Then Ill prepare a new SQL Performance Analyzer (SPA) task named SPA_SPM_300 that will analyze the performance of the SQL workload in the STS_SPM_200 SQL Tuning Set (see Listing 3.2).
Analyzing SQL Workloads. Next, Ill aim SPA task SPA_SPM_300 at my database to evaluate simulated workload performance between a 10gR2 and an 11gR1 Oracle database environment. Ill first clear my Oracle 11gs library cache and database buffer cache to insure a clean starting point for performance evaluation. Ill then set initialization parameter OPTIMIZER_FEATURES_ENABLE to a value of 10.0.0.0 to fool the optimizer into believing its an Oracle 10g database. Finally, Ill analyze the workload in that mode by performing a test execution of the SPA_SPM_300 analysis task. Once this sequence is completed, Ill then repeat the same analysis after setting OPTIMIZER_FEATURES_ENABLE to a value of 22.214.171.124 so that SPA will evaluate the workload in an Oracle 11g database environment. The code I used to do this is shown in Listing 3.3.
Comparing Relative Workload Performance. Once the two workload test executions are complete, my next task is to determine if there are any SQL statements whose performance would have regressed because the optimizer setting has changed. I used the code in Listing 3.4 to perform a comparison between the two workload simulations and then generate a report of any SQL statements which had poorer performance. To show the flexibility of SQL Performance Analyzer here, Ive eschewed using a relative change in optimizer cost as my metric; instead, Ive chosen to compare statements based on estimated execution time.
Capturing SQL Plan Baselines for Regressing Statements. As the resulting analysis report shows, two statements would be negatively impacted by the simulated upgrade of optimizer features from 10.2.0.1 to 126.96.36.199. Ill capture the execution plans for these statements into SQL Plan Baselines. This will prevent the CBO from running these SQL statements using the 11g optimizer settings, which would cause deleterious performance for those statements. The code in Listing 3.5 illustrates how to accomplish this.
Controlling SQL Plan Evolution
Oracle 11g has coined the term SQL plan evolution to describe the progressive search for the best execution plan during a SQL statements execution. As I outlined in the first article in this series, whenever SPM intercepts a new execution plan for a SQL statement that already exists in the SMB, then SPM saves that plan within in the SMB as part of that SQL statements history; however, SPM doesnt permit the plan to be used until its been evaluated for possible improved performance. If SPM determines that the new plan does improve performance, SPM will then change the plans status to ACCEPTED. This concept, also known as plan progression, is at the heart of SPMs automatic evolution of SQL statements within the SMB.
To demonstrate these concepts, Ive created a simple SQL statement (SPM_3_1.sql) that queries several tables in the Sales History schema. To create a clean slate against which SPM will evaluate this query, however, Ill first clear any SQL Plan Baselines from the SMB that are tagged with a comment string of SPM_ and flush both the Library Cache and Database Buffer Cache, as shown in Listing 3.6. Once thats done, Ill use the code shown in Listing 3.7 to deactivate automatic SQL Plan Baseline capture by setting initialization parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to FALSE at a session level, execute the query, and then capture the resulting SQL Plan Baseline manually from the Library Cache with function DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.
Bind Value Peeking and Adaptive Cursor Sharing. Ive deliberately used bind variables in SQL statement SPM_3_1.sql to illustrate how SQL Plan Management interacts with bind variable peeking. The cost-based optimizer has been able to take advantage of this feature since Oracle Release 9.0.1, and it lets the CBO determine a more accurate execution plan based on the first set of values that are supplied for the bind variables.
Oracle 11g also introduces a new feature called Adaptive Cursor Sharing that interacts extremely effectively with bind variable peeking and shared cursors. The bottom line here is that SPM will resolve any conflicts between these two features by marking the very first execution plan thats captured as the corresponding SQL Plan Baseline. If I re-execute the same query but use different values for the bind variables, its possible that the resulting execution plan could be much less effective than the original plan. To prevent this, SPM still stores the execution plan as part of the SQL statements plan history in the SMB, but the new plan wont be used until SPM verifies it as a better plan. (Ill expand this discussion in a later article that will focus on how Adaptive Cursor Sharing works.)
Requesting SQL Plan Baseline Evolution. On the other hand, what will happen when SPM detects a better plan due to increased performance? Ill simulate this scenario by adding an index on the CUST_LAST_NAME column of the SH.CUSTOMERS table. This index will have a dramatically positive effect on the performance of SQL statement SPM_3_1.sql because a full-table scan of the SH.CUSTOMERS table is no longer required to provide the result set. Once Ive reactivated automatic SQL Plan Baseline capture by setting initialization parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE back to its default value (TRUE) at a session level, Ill execute the statement again and verify the resulting SQL Plan Baseline that is captured. The CBO will compile a new execution plan that will utilize the new index to dramatically improve the statements performance, and the new execution plan will also be logged in the SMB for this statement (see Listing 3.8).
Until SPM evolves this new plan, however, the plan will not be utilized by the statements SQL Plan Baseline. When I execute function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE, Oracle 11g will immediately evaluate all available SQL Plan Baselines and evolve any that result in improved performance. This function returns a CLOB that lists any SQL Plan Baselines that have been changed to an accepted status as a result of plan evolution. Ive shown the results of this plans evolution in Listing 3.9. And to illustrate that the improved and evolved SQL Plan Baseline will now be utilized whenever this SQL statement is executed, Ive also run an EXPLAIN PLAN for the statement and formatted the resulting output with the +NOTES directive.
Automatic Plan Evolution Via SQL Tuning Advisor. Oracle 11g also provides for automatic evolution of SQL Plan Baselines via the execution of the SQL Tuning Advisor for any selected SQL statements. And since the Automatic SQL Tuning (AST) batch process also invokes the SQL Tuning Advisor during its regularly scheduled nightly run, AST can also recommend the acceptance of a SQL Profile whenever that Profile offers better performance than the current SQL Plan Baseline. (For more information on Automatic SQL Tuning, see this article.)
Modifying SQL Plan Baseline Attributes
Oracle 11g permits extremely granular control of the status and availability of captured SQL Plan Baselines, regardless of their origin. To illustrate, Ive prepared three SQL statements (see and tagged them all with a similar comment for easy identification (see SPM_3_2.sql). Once executed, I captured their execution plans directly from the Library Cache, loading them into the SMB as SQL Plan Baselines using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.
Lastly, I applied function DBMS_SPM.ALTER_SQL_PLAN_BASELINE to change various attributes of each SQL Plan Baseline. These modifiable attributes include:
- ENABLED. Setting this attribute to its non-default value of NO tells Oracle 11g to temporarily disable a plan. A SQL Plan must be marked as both ENABLED and ACCEPTED, or the CBO will ignore it.
- FIXED. Its possible that a SQL Plan Baseline could actually reference more than one execution plan. When this attribute is set to its non-default value of YES for at least one plan of a SQL Plan Baseline, that one plan will be the only one chosen by the optimizer, even if a plan with a potentially lower cost exists. This give the DBA the ability to override the default behavior of the SMB, and its especially useful for transforming a stored outline into a stable SQL Plan Baseline. Note that when a new plan is added to a SQL plan baseline that is currently FIXED, the new plan cant be utilized until its declared as FIXED.
- AUTOPURGE. Setting this attribute to its non-default value of NO tells Oracle 11g to retain it indefinitely, thereby exempting from the SMBs automatic purging mechanism.
The code shown in Listing 3.10 shows how I set up these SQL statements for capture and how I later modified these SQL Plan Baseline attributes. Oracle 11g actually supplies four overloaded methods to execute this function. The method Ive just demonstrated is probably the most flexible, since it accepts one of several SPM attributes whose values can be filtered. The other three methods accept a combination of:
- A SQL ID, a SQL Plan Baseline hash value, and a simple filter against the statements SQL text
- A SQL ID, a SQL handle, and a SQL Plan Baseline hash value
- A SQL ID and a SQL Plan Baseline hash value
Controlling Automatic SMB Management Features
Oracle 11g also offers two automatic management features that help keep the relative size and content of the SMB under tight control.
SMB Space Management. Once per week, Oracle checks if the size of the SMB has exceeded its space budget limit (default value: 10% of the SYSAUX tablespace), and if that limit is exceeded, a warning is recorded in the databases alert log. Space warnings will continue until either:
- More space is allocated to SYSAUX; or
- The space limit is increased from its default value; or
- SQL plan baselines or SQL profiles are purged from the SMB.
SMB Automatic Purging. Once per week, an automated task runs against the SMB and purges all SQL Plans that have not been used within the unused plan retention period. This period defaults to 53 weeks but can be adjusted to a value between 5 and 523 weeks (i.e. about 10 years).
These limits can be set using procedure DBMS_SPM.CONFIGURE, and their current values can be queried in the DBA_SQL_MANAGEMENT_CONFIG data dictionary view. Ive demonstrated how to modify the default settings for SMB space management and automatic purging in Listing 3.11.
Managing SQL Plan Baselines with Enterprise Manager
These articles have concentrated on demonstrating script-based methods for managing SQL Plan Baselines. However, Id be remiss if I didnt mention that Oracle 11g Enterprise Manager Database Control also offers an excellent interface for reviewing and managing the contents of the SMB. Figure 3.1 below shows the results of selecting the SQL Plan Control link on the Server page:
Figure 3-1. SQL Plan Baseline Management Home Panel.
This interface makes it possible to easily perform any of the following SPM functions without any complex PL/SQL coding:
- Enable or disable a SQL Plan Baseline
- Evolve a better plan for a selected SQL Plan Baseline
- Drop an existing SQL Plan Baseline
- Pack a SQL Plan Baselines contents into a staging table
- Unpack a staging table into a SQL Plan Baseline
As the example in Figure 3.1 illustrates, its also possible to limit the list of SQL Plan Baselines displayed on this screen.
As this article series has demonstrated, Oracle 11gs new SQL Plan Management (SPM) features offer an Oracle DBA the power to guarantee that a SQL statement will have access to the absolutely best possible execution plan(s), and this offers a chance to stabilize the performance of the SQL statement even before its ever executed in a production environment. SQL Plan Baselines can be captured from multiple sources even from existing Oracle 10g databases and can be pre-loaded for new applications to ease the potential disruption to smooth deployment. Finally, SPM offers several methods to influence which SQL Plan Baselines the cost-based optimizer will choose for any individual statement, maintain the current list of SQL Plan Baselines, and either automatically or manually purge any SQL Plan Baselines that the DBA may deem are no longer desirable for future optimal SQL statement execution.
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