Inside the 11g SQL Performance Analyzer Oracle Tips by Burleson Consulting

 

This is a preview from the forthcoming book “Oracle 11g New Features”, by Rampant TechPress, featuring John Garmany, plus Oracle ACE’s Steve Karam, Lutz Hartmann, and VJ Jain.

A trip to the SPA – Inside the 11g SQL Performance Analyzer

The declarative nature of the SQL syntax has always made it difficult to perform SQL tuning.  The basic tenet of cost-based SQL optimization is that the person who writes a SQL query simply "declares" what columns they want to see (the SELECT clause), the tables where the columns reside (the FROM clause), and the filtering conditions (the WHERE clause).   It's up to the SQL optimizer to always determine the optimal execution plan.  This is a formidable challenge, especially in a dynamic environment, which is why Oracle introduced the 10g new feature of CBO dynamic sampling. 

Oracle tuning consultants have know for many years that the best way to tune an Oracle system is to take a top-down approach, finding the optimal configuration for external factors (i.e. OS kernel settings, disk I/O subsystem), and determining the best overall setting for the Oracle instance (i.e. init.ora parameters). 

Holistic tuning involves tuning a representative workload, adjusting global parameters in order to optimize as much SQL as possible.  Only then, is it prudent to start tuning individual SQL statements.  Many Oracle professional who adopt a bottom-up approach (tune the SQL first), find all of their hard-work un-done when a change is made to a global setting, such as one of the SQL optimizer parameters or recomputing optimizer statistics. 

Oracle’s holistic SQL tuning approach is new, and given the misleading marketing name "fully automated SQL tuning".  Holistic tuning is well-known to working DBA's who have been doing manual workload-based optimization since Oracle6.  Now in 11g, Oracle gives us an automated method.

The Oracle 11g SQL Performance Analyzer (SPA), is primarily designed to speed up the holistic SQL tuning process, automating much of the tedium.

Once you create a workload (called a SQL Tuning Set, or STS), Oracle will repeatedly execute the workload, using sophisticated predictive models (using a regression testing approach) to accurately identify the salient changes to execution plans, based on your environmental changes.

Using SPA, we can predict the impact of system changes on a workload, and we can forecast changes in response times for SQL after making any change, like parameter changes, schema changes, hardware changes, OS changes, or Oracle upgrades; any change that influence SQL plans is a good candidate for SPA.

 

Decision Support and Expert Systems Technology

Oracle had made a commitment to Decision Support Systems (DSS) Technology starting in Oracle 9i when they started to publish “advisory” utilities, the result of monitoring the Oracle instance and coming up with estimated benefits for making a change to the database configuration. In the world of applied artificial intelligence, an expert system (e.g. AMM, ASM) solves a well-structured problem for the DBA, while a decision support system solves a semi-structured problem with the DBA, who supplies the human intuition required to solve a complex problem.

Oracle has made a commitment to distinguishing themselves in the database marketplace, and this is one of the major reasons that they command a major market share.  One of the most exciting areas of Oracle technology is automation, especially the self-management features.  Oracle has now automated many critical components, including memory advisors (AMM), automated storage management (ASM), and Oracle is now working to enhance more intelligent utilities including ADDM, the Automated Database Diagnostic Monitor, and the brand new 11g SQL Performance Analyzer (SPA).

The Oracle 11g SPA functions as a DSS, helping the DBA by automating the well-structured components of a complex tuning task, such as hypothesis testing.  In SPA, the DBA defines a representative workload and then tests this workload empirically, running the actual queries against the database and collecting performance metrics.  SPA allows the DBA to obtain real-world performance results for several types of environmental changes:

• Optimizer software levels – You can compare SQL execution between different release of the cost-based optimizer (CBO)
   
• Initialization parameters – You can pre-test changes to global parameters, most often the Oracle optimizer parameters (optimizer_mode, optimizer_index_cost_adj, optimizer_index_caching).  Prior to Oracle 10g, adjusting these optimizer parameters was the only way to compensate for sample size issues with dbms_stats. 

As of 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats had made adjustments to these parameters far less important.  Ceteris Parabus, always adjust CBO statistics before adjusting optimizer parms.  For more details on optimizer parameters, see my latest book "Oracle Tuning: The Definitive Reference". 

• Guided workflow – This is a hypothesis testing option that allows the DBA to create customized experiments and validate their hypotheses using empirical methods.

Instead of using theory and mathematical calculations, Oracle SPA tests the SQL Tuning Set (STS) workload in a real-world environment, running the workload repeatedly while using heuristic methods to tally the optimal execution plan for the SQL.  The DBA can then review the changes to execution plans and tune the SQL (using the SQLTuning Advisor) to lock-in the execution plans using SQL profiles. 

Let’s take a closer look at SPA and see how holistic SQL tuning can remove the tedium of tuning SQL statements.

 

Inside the Oracle 11g SQL Performance Analyzer

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 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 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.  For more details, see my notes on 10gR2 automatically tuned multi-block reads.
   
• 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 he 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 changes in SQL execution plans.

A trip to the SPA

Until the advent of the Oracle 10g intelligent SQL tuning advisors (The SQLAccess advisor and SQLTuning 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 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 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 features 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 SQLTuning 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.

The goals of holistic SQL tuning

Holistic tuning in Oracle 11g is a broad-brush approach that can save thousands of hours of tedious manual SQL tuning.  By applying global changes, the DBA can tune hundreds of queries at once, and implement them via SQL profiles. 

DBA’s who fail to do holistic SQL tuning first (especially those who tune SQL with optimizer directives), may find that subsequent global changes (e.g. optimizer parameter change) may un-tune their SQL  By starting with system-level tuning, the DBA can establish an "optimal baseline", before diving into the tuning of individual SQL statements:

• Optimize the server kernel - You must always tune your disk and network I/O subsystem (RAID, DASD bandwidth, network) to optimize the I/O time, network packet size and dispatching frequency.  Kernel settings have an indirect effect on SQL performance.  For example, a kernel setting may speed-up I/O, a change which is noted by the CBO workload statistics (using dbms_stats.gather_workload_stats).  This, in turn, directly influences the optimizer’s access decisions.
  
   
• Adjusting your optimizer statistics - You must always collect and store optimizer statistics to allow the optimizer to learn more about the distribution of your data to take more intelligent execution plans.  Also, histograms can hypercharge SQL in cases of determining optimal table join order, and when making access decisions on skewed WHERE clause predicates. Also new in 11g, multi-column statistics can be gathered for use by the optimizer to determine optimal ways to run queries based upon multiple column criteria.
   
• Adjust optimizer parameters - Optimizer optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj.
   
• Optimize your instance - Your choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance.
   
• Tune your SQL Access workload with physical indexes and materialized views - Just as the 10g SQLAccess advisor recommends missing indexes and missing materialized views, you should always optimize your SQL workload with indexes, especially function-based indexes, a Godsend for SQL tuning. 

Now, Oracle 11g does not have all of the intelligence of a human SQL tuning expert, but the 11g SQL Performance Analyzer (SPA) is a great way to test for the effect of environmental changes to your Oracle environment. 

Let's take a closer look at how Oracle has automated the SQL tuning process with SPA.

 

The SPA treatment

The SQL performance analyzer allows the DBA to define the SQL Tuning set (the STS), as a source for the test (usually using historical SQL from the AWR tables).

The SPA  receives one or more SQL statements as input (via the SPA), and provides advice on which tuning conditions have the best execution plans, gives the proof for the advice, shows an estimated performance benefit, and allegedly has a facility to automatically implement changes that are more than 3x faster than the "before" condition".

 

Gathering the SQL Tuning set

The SQL workload (the STS) can be thought of as a container for conducting and analyzing many SQL statements. The STS is fed to the SPA for real-world execution with before-and-after comparisons of changes to holistic "environmental" conditions, specifically CBO levels or changed init.ora parameters.

Internally, the SPA is stored as a database object that contains one or more SQL statements combined with their execution statistics and context such as particular schema, application module name, list of bind variables, etc. The STS also includes a set of basic execution statistics such as CPU and elapsed times, disk reads and buffer gets, number of executions, etc.

When creating a STS, the SQL statements can be filtered by different patterns such as application module name or execution statistics, such as high disk reads. Once created, STS can be an input source for the SQL Tuning Advisor.
 
Typically, the following steps are used to define the STS using the dbms_sqltune package.  The steps within the new 11g OEM screen for "guided workflow" are simple and straightforward, and serve as an online interface to the dbms_sqltune.create_sqlset procedure:

 

1 – Options – Choose a name for your SQL tuning set (STS).  This encapsulated SQL workload is created using the dbms_sqltune.create_sqlset procedure. For example, the following script can be used to create a STS called SQLSET1:
 
exec dbms_sqltune.create_sqlset (‘MYSET1’);

 

2 – Load methods - Here is where you can choose the source for your SQL workload, and to take historical SQL statements from AWR. 

 

3 – Filter options - You can choose “filtering” conditions, based on your specific tuning needs.  For example, if your database is disk I/O bound, you might choose only SQL statements that have more than 100k disk reads.

 

4 – Schedule – This is an interface to the dbms_scheduler package, allowing you to define and schedule a job.

 

5 – Review – Here you can see the actual source calls to dbms_sqltune.create_sqlset and the dbms_scheduler.create_job procedure call syntax.

There is an interface to the SQL Performance Analyzer in the enterprise manager in the OEM Advisor Central area, and a number of new to dba_advisor views have been added in 11g which will display information from the SQL Performance Advisor. 

The technology behind SPA is encapsulated inside a new package called dbms_sqlpa. Here is an overview for the procedures of the dbms_sqlpa package:

 

• CANCEL_ANALYSIS_TASK – This procedure cancels the currently executing task analysis of one or more SQL statements.
  
   
• CREATE_ANALYSIS_TASK – This function Creates an advisor task to process and analyze one or more SQL statements.
  
   
• DROP_ANALYSIS_TASK - This procedure drops a SQL analysis task.
  
   
• EXECUTE_ANALYSIS_TASK – This function & procedure executes a previously created analysis task.
  
   
• INTERRUPT_ANALYSIS_TASK - This procedure interrupts the currently executing analysis task.
  
   
• REPORT_ANALYSIS_TASK – This function displays the results of an analysis task.
  
   
• RESET_ANALYSIS_TASK – This procedure resets the currently executing analysis task to its initial state.
  
   
• RESUME_ANALYSIS_TASK – This procedure resumes a previously interrupted analysis task that was created to process a SQL tuning set.
  
   
• SET_ANALYSIS_TASK_PARAMETER – This procedure sets the SQL analysis task parameter value.
  
   
• SET_ANALYSIS_DEFAULT_PARAMETER – This procedure Sets the SQL analysis task parameter default value

In sum, the new 11g SQL Performance Analyzer is a great way to test for holistic tuning changes.  Remember, the savvy Oracle DBA will always adjust their Oracle initialization parameters to optimizer as much of the workload as possible before diving into the tuning of specific SQL statements.

Oracle 11g guided workflow screen

The OEM screen for the SPA “guided workflow” contains a pre-defined set of steps for holistic SQL workload tuning:

1 – Create SQL Performance Analyzer Task, based on SQL Tuning Set

2 – Replay SQL Tuning Set in Initial Environment

3 – Create replay Trial using changed environment

4 – Create Replay trial comparison (using trials from step 2 and step3)

5 – View trial comparison report

 

Using the guided workflow functionality, we can take our SQL tuning set and execute it twice (before and after), saving the SQL execution results (disk reads, buffer gets) using some of the common SQL execution metrics found in the dba_hist_sqlstat table:

 

Here it’s important to note that the guided workflow does not measure these important SQL execution metrics such as sorts and fetches. 

 

Comparing the results SPA Results

The final step in SPA allows the DBA to quickly isolate sub-optimal SQL statement and tune them with the 11g SQLTuning Advisor.  When viewing the results, you can use OEM for a visually display of all delta values between the execution run, but most important, you can do a side-by-side comparison of the before-and-after execution plans.

Oracle has always been ahead of the curve in automating well-structured DBA tasks, and the SPA is just the latest incarnation in real-world SQL tuning tools.  Tools such as SPA free-up the DBA to pursue other important DBA tasks, relieving them of the tedium of individually tuning SQL statements.

---

This is a preview from the forthcoming book “Oracle 11g New Features”, by Rampant TechPress, featuring John Garmany, plus Oracle ACE’s Steve Karam, and Lutz Hartmann.