Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle 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.
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational