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 








My favorite Oracle tuning papers of IOUG Collaborate 2006

The IOUG Collaborate 2006 conference was smashing success, being bundled with the OAUG and QUEST user group conferences. 

Attracting more than 5,000 attendees, this massive conference offered something for everyone and had a virtual who's-who of Oracle tuning experts offering a wide variety of highly-technical sessions.

As a Oracle tuning specialist, I was attracted to the Oracle tuning papers and these were my favorites. 

  • Modern Performance Myths - Craig Shallahamer
  • Histograms - Myths and Facts - Wolfgang Breitling
  • Tuning Oracle at the Block Level - Rich Niemiec
  • Welcome to my Nightmare - The Common Performance Errors in Oracle Databases - Mike Ault

If I did not include your paper, I apologize in advance, as I was unable to attend them all!

Modern Performance Myths

- Craig Shallahamer - I'm a big fan of Craig and his whitepaper notes his short list of tuning myths where he points out the shortcoming of wait event tuning and session profiling approaches.  This great papers supplements the BC articles on Busting the Oracle Myth Busters and Oracle Myths Revisited.:

Myth #1. Decreasing wait event time will always decrease Oracle response time.

Myth #2. Decreasing wait event time will always decrease end-to-end response time.

Myth #3. Profiling sessions is the best way to diagnose performance problems.

Myth #4. Focusing on where most of the time is spent is always the best approach.

Craig advocates a holistic tuning method, some quite similar to the method in my book "Oracle Tuning: The Definitive Reference":

The Holistic Problem Isolation Method (HPIM) provides a very robust performance diagnosis analysis method. The key to this method is identifying each subsystem’s bottleneck or area of contention, and establishing and understanding the relationships between subsystems.

Craig concludes about his holistic HPIM tuning  method:

To optimize a database system one must understand each subsystem: the database, the operating system, and the application.

By discovering where each system is being stressed or bottlenecked, observing the overlap, and re-focusing the search, the problem possibilities can be narrowed very quickly.

Unfortunately, most performance specialists tend to focus on one of the three main subsystems mentioned above. This results in an ill-defined problem that will translate into a lop sided solution. In many cases, the solution, while appearing to solve the problem from one subsystem’s viewpoint, actually compounds the problem from other viewpoints resulting in overall system performance degradation.

The key to making this method work is discovering and exploiting the relationship between the three subsystems. For example, if Oracle says there is a physical IO issue, you should be able to find the offending high physical IO SQL, and also uncover the IO bottleneck. The analysis does not lie, so if something looks strange or does not correlate, it forces you to dig deeper to get a better understanding of the situation.

As with any system, once you make a change, the system will adjust and the bottleneck could be very different.

Histograms - Myths and Facts

- Wolfgang Breitling

If you want to look under the covers of Oracle histograms, Wolfgang is the man to see.  His amazing whitepaper was well-researched and offers a wealth of valuable insights into how the Oracle CBO uses histograms to estimate the cardinality of intermediate result sets (for choosing optimal indexes and optimal table join order as noted in this OTN paper).  Breitling notes the performance benefits of histograms on non-indexed columns (under certain circumstances) and cautions against a one-size-fits-all solution:

Ok, so we have seen that a histogram on a non-indexed column can be beneficial for the performance of a SQL. But what about the reverse? Can a histogram on a column, indexed or not, be detrimental to the performance of a SQL?

I have always been convinced that the answer to that is “Yes”

Wolfgang has boatloads of on-point test cases to illustrate his points and offers some outstanding advice for using dbms_stats for histogram collection (i.e. method_opt=>’size skewonly’): 

Aside from the failure of dbms_stats not pro-rating the histogram obtained through sampling, it does not make sense to gather hostograms (sic) with sampling in the first place. After all, histograms are only gathered – or ought to – for columns with skewed data distribution.

Sampling is liable to miss infrequently occurring values and therefore skew the resulting histogram.

He concludes by citing these great research papers:

1. Wolfgang Breitling A Look Under the Hood of CBO: The 10053 Event

2. Wolfgang Breitling Using DBMS_STATS in Access Path Optimization

3. Wolfgang Breitling Tuning by Cardinality Feedback - Method and Examples

4. Andrew Holdsworth, et al. A Practical Approach to Optimizer Statistics in 10g. in Oracle Open World. September 17-22, 2005. San Francisco.

Tuning Oracle at the Block Level

- Rich Niemiec

Rich Niemiec is an exciting and dynamic speaker and this is a very important Oracle tuning topic.  In his paper, Rich notes that AWR and STATSPACK elapsed-time reports are very valuable to understanding block-level tuning:

The biggest problems can be quickly found in the Statspack or AWR reports.  One of the first places that you can check is the Top 5 Wait Events.  The Top 5 Wait Events section of statspack is probably the most revealing section in the entire report when you are trying to quickly eliminate bottlenecks on your system. 

This section of the report shows the Top 5 Wait Events, the full list of Wait Events, and the Background Wait Events. Identifying major wait events will help you to target your tuning efforts to the most burning issues on your system.  If TIMED_STATISTICS is true, then the events are ordered in time waited, if false, then the events are ordered by the number of waits.

Niemiec also notes his short list for Oracle block-level tuning:

First, you want to review the basics and try to tune everything without resorting to doing ANYTHING at the block level. This includes the usually suspects (summarizing the wait section about into a shorter list)

- Tune the SQL, of course… especially reads of full indexes tables and others.
- Find the hot blocks… hot blocks can cause latching issues. Bad SQL or bad indexes causes hot blocks (scanning through the same large index). Improved in 10g (shared latches).
- Do you have enough freelists or can you use ASSM.
- Do you have initrans (ITL slots) set high enough for multiple DML to the same block (pctfree not high enough to auto-generate more ITL slots). Or do you have too many (each ITL costs 24 bytes!).
- Do you have a slow I/O subsystem or poor disk caching or not enough paths and readers/writers are colliding.
- Are you on latest version so can use all the great new features!

Welcome to my Nightmare - The Common Performance Errors in Oracle Databases

- Mike Ault

I've been a Mike Ault fan for 15 years, and I'm always amazed at his ability to quickly identify and correct performance bottlenecks.  This great paper relates to my book "Oracle Silver Bullets" and show how a single change has have a dramatic positive impact on Oracle performance:

I have come up with a list of 6 major areas that the DBA or tuning expert needs to pay attention to in an Oracle environment:

• Non-use of bind variables
• Improper Index usage
• Improper memory configuration
• Improper disk setup
• Improper initialization parameter usage
• Improper PGA setup


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