Inside the Oracle 11g SQL Performance Analyzer Oracle 11g New Features Tips by Burleson Consulting July 13, 2008

Oracle 11g SQL New Features Tips

The Oracle 11g SQL Performance Analyzer is a step in the direction of fully automated SQL tuning, allowing the database administrator to create a STS “workload”, a unified set of SQL which comes from either the cursor cache (Shared Pool) or from the AWR (the Automated Workload Repository).  The Remote DBA can use exception thresholds to select the SQL for each STS, based on execution criteria such as disk reads, consistent gets, executions, etc.  Once the Remote DBA has chosen their STS, SPA allows them to run the workload while changing Oracle environmental factors, namely the CBO release level, init.ora parameters and customized hypothesis testing using the guided workflow option.

The central question becomes which Oracle initialization parameters would be the most appropriate within the SQL performance analyzer?  Because the SPA is used to measure changes in SQL execution plans, it only makes sense that we would want to choose those Oracle parameters which will influence the behavior of the Oracle optimizer. 

These would include the basic Oracle optimizer parameters (including optimizer_index_cost_adj, optimizer_mode, optimizer_index_caching), as well as other important initialization parameters.  We also have non-optimizer parameters which effect SQL execution plan decisions.

• db_file_multiblock_read_count - when this parameter is set to a high value the Oracle cost based optimizer recognizes that scattered multiblock reads may be less expensive than sequential reads. (i.e. full table scans and full index scans).  10gr2 Note:  Starting in Oracle 10g release2, Oracle recommends not setting the db_file_multiblock_read_count parameter, allowing Oracle to empirically determine the optimal setting. 
   
• parallel_automatic_tuning - when parallel_automatic_tuning is set to “on” the Oracle optimizer will parallelize legitimate full table scans. Because we have told Oracle that parallel full table scans can be done very quickly using parallel query Oracle’s cost based optimizer will assign a higher cost index access, making the optimizer friendlier to full table scans.
   
• hash_area_size (if not overridden by pga_aggregate_target) - the setting for hash_area_size governs the propensity of Oracle’s optimizer to favor hash joins over nested loop and for merge joins. This makes it an ideal testing parameter for changes to Oracle memory regions so that you can see how they would be affected within a production environment.
   
• pga_aggregate_target - the settings for Pga_aggregate_target have a profound impact on the behavior of Oracle SQL statements, making this an interesting test case for the SQL performance analyzer, especially with regard of the propensity of the Oracle optimizer to do in memory sorts, and hash joins.
   
• sort_area_size (if not overridden by pga_aggregate_target) the sort_area_size parameter influences the cost based optimizer when deciding whether or not to perform index access, or to perform a sort of the ultimate results set from the SQL query. The higher the value for sort_area_size the more likely it will be that the Oracle 11g optimizer will invoke a backend sort, because it knows that the sort can be performed in memory.  Of course, this depends upon the Oracle optimizers estimated cardinality for the results set of the SQL query.

Of course, we can change any parameters we like.  Let’s now see how the SPA captures change in SQL execution plans.

A trip to the SPA

Until the advent of the Oracle 10g intelligent SQL tuning advisors (The SQL Access advisor and SQL Tuning Advisor), SQL tuning was a time-consuming and tedious task.  That all started to change in Oracle 10g, and it's even more exciting in Oracle 11g, where Oracle has promised "fully automated" SQL tuning, via the new SQL Performance Analyzer and improvements in the SQL advisories.

The Oracle 10g automatic tuning advisor allowed us to implement tuning suggestions in the form of SQL profiles that will improve performance. Now with Oracle11g, the Remote DBA can tell Oracle to automatically apply SQL profiles for statements whenever the suggested profile give 3-times better performance that the existing statement. These performance comparisons are done by a new 11g administrative task that is executed during a user-specified maintenance window.  In a nutshell, the 11g fully automated SQL tuning works like this:
 

1 - Define the SQL workload - The Remote DBA defines a "set" of problematic SQL statements using exception thresholds (e.g. all SQL with > 100,000 disk reads), select from the cursor cache or the AWR.  This is called the SQL Tuning set, or STS.

2 - Set-up a changed environment - Here you can chose to change your initialization parms, test your performance against a previous release of the CBO (a very useful feature when testing upgrades) or conduct "custom experiments" on the effect of environmental changes on your SQL tuning set.

3 - Schedule & run your tests - The workload is scheduled for execution during "low usage" periods, so that an empirical sample of real-world execution times can be collected and compared, using different execution plans.

4 - Implement the changes – You can flag SQL statements for changes and tune them with the 10g SQL Tuning advisor. 

Tip:  Also related is the Oracle 11g automated SQL tuning Advisor, whereby you can automatically implement changes that cause your SQL to run more than 3x faster. 

The Oracle 11g automated SQL tuning advisor will implement all execution plan changes via "SQL Profiles", a tool that is conceptually similar to stored outlines, a method to bypasses the generation of execution plans for incoming SQL, replacing it with a pre-tuned access plan. 

The automatic SQL tuning advisor also recommends restructuring badly-form SQL, and adding missing indexes and materialized views, but these require a manual decision.

Before we examine the nuances of the 11g fully automated SQL tuning features, let's briefly review the goals of SQL tuning.

This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr. You can buy it direct from the publisher for 30% off.