Oracle 11g fully automated SQL tuning tips Oracle11g Tips by Burleson Consulting

This is a work in progress excerpt from the book "Oracle 11g New Features" by Rampant TechPress.

Oracle has touted their new 11g “holistic” approach to SQL tuning as “fully automated SQL tuning”, but the marketing hype must be separated from the reality.  The main benefit is that the DBA can now test changes to global parameters against a real-world workload, using a SQL Tuning Set (STS).  Holistic tuning in Oracle 11g is offered through several functional areas, most importantly the SQL Performance Advisor (SPA) and the automated SQL Plan Management (SPM) facility.  SPA is the natural evolution of the SQLAccess advisor:

• 10g SQLAccess Advisor – The 10g SQL access advisor tests real-world SQL workloads, recommending missing indexes and materialized views.   
  
   
• 11g SQL Performance Analyzer – The SPA takes the SQLAccess advisor one step further and implements tuning recommendations for any SQL statements that run 3x faster (when tested with a new workload).  For more details, see Inside the 11g SQL Performance Advisor .

How fully automated SQL tuning works

In a nutshell, the 11g fully automated SQL tuning is a series of processes and tools, loosely coupled for maximum flexibility.

A – The Setup for fully automatic SQL tuning – Here we capture representative SQL workloads (SQL tuning sets) and set-up a testing environment:

1 - Define the SQL workload - The DBA defines a "set" of problematic SQL statements (or chooses a representative workload).  This is called the SQL Tuning set, or STS.  This uses the dbms_sqltune.create_sqlset package.

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.

B – Initial SQL tuning – Using the SQL Performance Analyzer, we optimize our environment using the SQL tuning set: 

1 - Schedule & run workload 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 from the two SQL tuning sets.  To do this we run the dbms_sqlpa package.  You can also use the OEM SPA “Guided Workflow” wizard.

2 - Implement the changes - For any statements that execute more then 3x faster, after the changes, Oracle 11g will automatically implement the changes via "SQL Profiles", a tool that bypasses the generation of an execution plans for incoming SQL, replacing it with the pre-tuned access plan.

C – Gather Baseline - Create the SQL Plan Baseline - To enable automatic SQL plan capture, set the optimizer_capture_sql_plan_baselines initialization parameter to TRUE.

D – Regression testing and implementation – We test global changes with the SQL Plan Manager (SPM).  As the system characteristics change, you can use the SQL Plan Manager to test against real workloads and ensure that all changed execution plans result in at least 3x faster performance.

This is a huge improvement over the hit-and-miss SQL tuning techniques of the past, but it’s not a truly “fully-automated” approach either.  We must remember that there will always be “outlier” SQL statements that must be tuned manually.

Fully Automated SQL Tuning is not a Panacea

There are many internal and external factors that influence the elapsed time for a given SQL statement, and the 11g SQL Performance Analyzer (SPA) and SQL Plan Management (SPM), the DBA can establish an "optimal baseline" before diving into the tuning of individual SQL statements:

• Optimize the server kernel - 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.
   
• Adjust your optimizer statistics - Always collect and store optimizer statistics to allow the optimizer to learn more about the distribution of your data to take more intelligent SQL access 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 – You can empirically determine the best settings for optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj.
   
• Optimize the instance - Your choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, etc.) can influence SQL performance.
   
• Tune with 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.

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.