Oracle Database 11g: SQL Plan Management, Part 3

March 27, 2008

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 11g’s 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 11g’s 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, I’ll 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, I’ll first remove any SQL Plan Baselines from the SQL Management Base (SMB) that were created during prior analyses. I’ll 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 I’ll 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, I’ll aim SPA task SPA_SPM_300 at my database to evaluate simulated workload performance between a 10gR2 and an 11gR1 Oracle database environment. I’ll first clear my Oracle 11g’s library cache and database buffer cache to insure a clean starting point for performance evaluation. I’ll then set initialization parameter OPTIMIZER_FEATURES_ENABLE to a value of 10.0.0.0 to “fool” the optimizer into believing it’s an Oracle 10g database. Finally, I’ll analyze the workload in that mode by performing a test execution of the SPA_SPM_300 analysis task. Once this sequence is completed, I’ll then repeat the same analysis after setting OPTIMIZER_FEATURES_ENABLE to a value of 11.1.0.6 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, I’ve eschewed using a relative change in optimizer cost as my metric; instead, I’ve 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 11.1.0.6. I’ll 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 statement’s 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 statement’s history; however, SPM doesn’t permit the plan to be used until it’s been evaluated for possible improved performance. If SPM determines that the new plan does improve performance, SPM will then change the plan’s status to ACCEPTED. This concept, also known as plan progression, is at the heart of SPM’s automatic evolution of SQL statements within the SMB.

To demonstrate these concepts, I’ve 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, I’ll 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 that’s done, I’ll 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. I’ve 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 that’s captured as the corresponding SQL Plan Baseline. If I re-execute the same query but use different values for the bind variables, it’s 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 statement’s plan history in the SMB, but the new plan won’t be used until SPM verifies it as a better plan. (I’ll 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? I’ll 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 I’ve 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, I’ll 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 statement’s 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 statement’s 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. I’ve shown the results of this plan’s 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, I’ve 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, I’ve 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. It’s 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 it’s 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 can’t be utilized until it’s 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 SMB’s 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 I’ve 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 statement’s 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 database’s 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. I’ve 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, I’d be remiss if I didn’t 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 Baseline’s contents into a staging table
• Unpack a staging table into a SQL Plan Baseline

As the example in Figure 3.1 illustrates, it’s also possible to limit the list of SQL Plan Baselines displayed on this screen.

Conclusion

As this article series has demonstrated, Oracle 11g’s 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 it’s 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 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

» See All Articles by Columnist Jim Czuprynski