Friday, March 02, 2012

Should the Oracle docs be 100% trusted

A few days ago, a member of the Oracle Forums asked a question about a number referenced in the Oracle DB documentation.  The immediate question did point to a doc bug, but a number of the responses (correctly) indicated that the specific numbers being referenced might not be absolute.  The poster countered that if the numbers can not be trusted, perhaps the documentation can not be trusted.

Like Wikipedia, the Oracle documentation is a great place to start.  It provides a lot of information, and a lot of 'facts'.  However, also like Wikipedia, blind faith in the documentation as an absolute source truth can occasionally be misplaced.

My response to that thread was

I like Tom Kyte's attitude around this, which can be paraphrased as "it ain't true until you've proven it for yourself - and even then, recheck periodically"
In an engine such as Oracle, where things can automatically adjust themselves based on statistics, it is probably unwise to take things like '20% or 20 blocks' as an absolute.
For example, consider the following: "When is it better to do a full table scan instead of using an index that is referenced in the query?"

We will often say "when you get back 'N'% of the data", and 'N' will be typically between 10 and 25%.
SQL is designed around the idea that we declare what is to be accomplished, not how it is to be accomplished.  The Oracle Server database engine is free to interpret the request in a variety of ways, and come up with different ways to implement the response. 

Once the different code paths have been determined, the optimizer can apply different weight to each path based on the cost of doing that, the number of rows involved, and so on. 
However, a much more accurate answer would be "when the optimizer has determined the total cost of the table scan to be lower than the total cost of using the index". And factors that could adjust that cost might include:

- current statistics on the table and the column
- whether the data in the column is required for any subsequent operation
- whether the column is involved as an expression in the output

which could possibly change the percentages in the answer from 5% to 50%.
For years, Oracle Tuning courses have emphasized that there are only two ways to speed things up - reduce the time waiting on resources, and reduce the time working toward the end result.  Over time the designers and developers of the Oracle engine have collected a lot of information about how things are done and where the bottlenecks occur.  Some of that research has resulted in new code paths, but some has resulted in interpreting the statistics in new ways, of changing the weight of specific code paths - making more efficient choices.
As the optimizer has been improved, it has added some quite unexpected capabilities but it has also added some interesting 'opportunities for improvement' and side effects.
The documentation is a great, and even a mandatory, starting place to understand Oracle.  However, there are several points to keep in mind, highlighted by my colleagues Joel Garry and Justin Cave:
  • The documentation is written by humans and can easily have some errors;
  • The documentation is published periodically and may not reflect each update that has occurred in the rush to deliver product;
  • The documentation normally does not reflect behaviour due to bugs;
  • The documentation can not reflect every possible decision-tree variant chosen due to statistics
So, should the documentation be 100% trusted?  My take, based on my experience, is roughly:

  • 90% of the time the concepts and the general ideas that are provided in the docs are good enough to use unchanged
  • 9% of the time, the docs are a great springboard to move you in your own direction, if you have your own tests.  A rule of thumb for this category is when the docs provide a number, and no supporting script or test.
  • 0.9% of the time, the docs may be completely insufficient, and you need to turn to My Oracle Support or to experts like Jonathan Lewis or Tanel Poder who are willing to share their knowledge.  However, this is beyond the normal day-to-day DBA activity and goes into the realm of 'very interesting stuff'.
  • 0.009% of the time, you have the opportunity to become an expert yourself.
  • And perhaps for 0.0009% of the time (if that high), the docs have mistakes - which you should report to the Documentation forum at
And to refer to an old engineering joke - that's close enough for all practical purposes

Don't give up on the documentation.


Joel Garry said...

Hard to say what percentage is really wrong, partially depending on what the right definition of wrong is, but sometimes the problem is obvious.

Funny you got my name right, with typos before and after. ;p

words:beinta pblefor
words:eivera latict

Fuzzy GreyBeard said...

Some typos corrected - thx. Could have sworn it was OK on the screen. I'm going to blame the network.

And yes, the percentage can change - haven't run 'gather stats' yet.