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 








Important notes for global SQL optimization

Oracle Database Tips by Donald BurlesonOctober 14, 2015


Oracle professionals must always undertake to perform holistic tuning before delving into the tuning of specific SQL statements, an approach that has been the bread and butter of successful corporate DBA's since the earliest days of Oracle6.


In Oracle 11g, we see this holistic approach codified in the SQL Performance Analyzer (SPA), a new tool to simplify the setting of optimizer statistics and initialization parameters.  In SPA, the DBA chooses a representative workload and runs it, comparing the overall SQL execution plans with different sets of CBO statistics and settings for the silver bullet initialization parameters.


Prior to Oracle 10g, adjusting the optimizer_index_cost_adj parameter was the only cost-effective way for large shops to compensate for sampling issues in 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. Today, the savvy DBA always optimizes their CBO statistics before adjusting their silver bullet parms.


Dealing with time constraints


In a busy Oracle shop, the DBA often does not have the luxury of undertaking the time-consuming task of reorganizing fragments tables with dbms_redefinition and manually gathering optimal CBO statistics, especially the tedious task identifying columns that require histograms.  Hence, some DBA managers want a quick-fix and undertake to optimize their workload by lowering their value for optimizer_index_cost_adj.


For more details on holistic tuning with parameters, see my book "Oracle Tuning: The Definitive Reference".


Next, let's see how holistic workload tuning has changed within each release of Oracle.


A release-centric approach to holistic optimization


Each release of Oracle brings enhancements and changes to the way that the Oracle DBA optimizes their system-wide workloads.  In general, these approaches to global SQL optimization are highly dependent on the release of Oracle:

  • Oracle6 - Oracle7 - In Oracle7 there were significant shortcomings to the cost-based optimizer, and many Oracle professionals were forced to tweak the CBO throttles to achieve the largest amount of optimized SQL.  These techniques include setting optimizer_mode=rule, and adjusting optimizer_index_cost_adj.  We also saw the introduction of the CBO histogram, an important feature for SQL optimization.

  • Oracle8 - Oracle8i - In the Oracle8 series of releases, the cost-based optimizer was ready for prime-time and we saw the introduction of materialized views and enhanced CBO statistics collection.  We saw the introduction of function-based indexes to alleviate unnecessary full-table scans, and we also saw the dbms_stats package to allow better collection of schema metadata, to help the CBO make more intelligent optimization decisions.  The rule-based optimizer (RBO) remained a popular tuning tool for simple databases, and the adjustment of SQL workload optimization relied on changing optimizer_mode, optimizer_index_cost_adj and optimizer_index_caching.  We also saw global temporary tables for optimizing multi-step SQL queries.  Best of all, the BSTAT-ESTAT utility was enhanced with the STATSPACK tool to allow for the historical storage of tuning information, indispensable for proactive workload tuning.

  • Oracle9i - In Oracle9i we saw the new v$sql_plan view to help tune SQL proactively and the dbms_stats package was enhanced to be more intelligent.  Still, the DBA was frequently forced to optimize their SQL by adjusting the optimizer parameters.  We also saw the ability to collect a "deep" sample with dbms_stats and save them, a tool that is especially useful for large shops where production CBO statistics can be exported and imported into test and development instances, allowing developers to optimize their SQL before it is introduced into production.  We also got the dbms_redefinition package to allow online reorganization of fragmented tables.

  • Oracle 10g - Oracle 10g greatly aided the optimization of large SQL workloads with the introduction of dynamic sampling and root-cause optimization with dbms_stats.  Oracle acknowledged that the root cause of sub-optimal SQL execution plan related to the quality of CBO statistics, and they introduced enhancements to dbms_stats to allow for automatic histogram creation and the gather_system_stats procedure to collecting all-important external information, most notably the average disk access timings for index access (sequential reads) and full-scan access (scattered reads).  This changed the landscape for SQL tuning, as it was now possible to address the root cause of suboptimal execution plans.  However, many DBA's who were under time constraints were unable to undertake the time-consuming analysis required to verify optimal statistics and they resorted to the "quick fix" of adjusting the optimizer parameters.

  • Oracle 10g Release 2 - 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.


  • Oracle 11g - In Oracle 11g we see the promise of a greatly improved dbms_stats package, running 2x faster and automatically collecting a statistically significant sample size.  We also see the promise of better detection of columns for histograms.  Oracle technical support claims that adjusting the CBO statistics addresses the root cause of sub-optimal execution and that changes to the optimizer_index_cost_adj are "rarely" required.  However, the DBA still needs to set the optimal optimizer_mode and optimizer_index_caching.  (You can run scripts to intelligently set an appropriate value for optimizer_index_caching).  For more details, see Oracle 11g dbms_stats enhancements.

In sum, the techniques for holistic SQL optimization change radically by release level, and in Oracle10g and Oracle11g it is now less time-consuming to fix the root-cause of CBO issues by adjusting the CBO statistics.


The general holistic SQL optimization steps for 9i, 10g and 11g include addressing the root cause (bad statistics) whenever feasible:

  1. Verify a correct sample size using dbms_stats and determine an intelligent threshold for re-analyzing statistics.

  2. Examine the low-hanging fruit of high-impact sub-optimal SQL and add histograms to improve access for queries against skewed and out-of-bounds queries.  Also, add histograms to improve the CBO's ability to determine the optimal table join order.

  3. Run dbms_stats.gather_system_stats to get external I/O tuning for full-scan vs. index I/O.

  4. Intelligently set optimizer_mode and optimizer_index_caching, testing with a representative workload in a real-world environment.

  5. Optimize the silver bullet parameters (db_cache_size, db_file_multiblock_read_count, etc.)

  6. Set optimizer_index_cost_adj (if required) to fix any remaining suboptimal execution plans.

  7. Finally, tune individual SQL statements using SQL profiles.

For more details on holistic SQL tuning, see my book "Oracle Tuning: The Definitive Reference" and see my related notes:

Tuning with optimizer parameters



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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.