December 3, 2010
(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)
I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book. As I stated in a comment in the earlier blog article, I would like to clarify that my review on the Amazon site is not intended to suggest that people should not buy the book. As the publisher’s website states that the book is written for senior Oracle DBAs, I suggest that senior DBAs, Oracle Certified Masters, and other people who are able to research the book’s contents *should* buy the book and post reviews of the book that highlight specific content of the book indicating whether or not that content is correctly stated. Such feedback will be a benefit to the Oracle community, and could help to improve Oracle Database books that are published in the future. I think that we need to keep a positive outlook in order to see things improve.
With that said, what is wrong with the following quote from page 749 of the book?
“Oracle says that if the query returns less than 40 percent of the table rows in an ordered table or seven percent of the rows in an unordered table, the query can be tuned to use an index in lieu of the full-table scan, but in reality there is no fixed number because it depends on many factors like the db_block_size and db_file_multiblock_read_count.”
“… The most common cause of unnecessary full-table scans is a optimizer_mode that favors full-table scans (like all_rows) or a missing index, especially a function-based indexes.”
What, if anything, is wrong with the above quotes from the book?
The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.
A Google book search indicates that the first statement appeared in a couple of other books:
- “Oracle High-Performance SQL Tuning”
- “Oracle 9i High-Performance Tuning with Statspack”
- The previous edition of this book
Other pages found during a Google search of the first phrase:
- http://www.remote-dba.net/t_tuning_sql_optimizers.htm
- http://www.dba-oracle.com/t_fix_unnecessary_full_table_scans.htm
- deepthinking99.wordpress.com/2010/03/20/fix-unnecessary-full-table-scans/
- http://www.dba-oracle.com/art_dbazine_expert_tuning.htm
Charles Hooper's Oracle Notes 
This one might be a little more challenging.
To help you get started, notice how the quoted phrase changed from the time that it appeared in the Oracle Press book titled “Oracle 9i High-Performance Tuning with Statspack”. Does the author mean “Oracle Press” when the book states “Oracle says”? If not, where did the 40% figure originate?
I tried to find a refernce on My Oracle Support (the documentation graveyard formerly known as Metalink) to see if I could find the reference when I saw the 40% – but I couldn’t it. I think it came from the days of Oracle 5.
As I stated earlier, it is interesting to compare the text in the “Oracle High-Performance SQL Tuning” book, published by Oracle Press (McGraw-Hill) and written by the same author as the “Oracle Tuning the Definitive Reference Second Edition”, with the text in the “Oracle Tuning the Definitive Reference Second Edition” book. In the Oracle Press book the author states as fact that 40% and 7% rules, while in the Rampant book the author claims that “Oracle says” and then lists the same 40% and 7% rules. The author may be refuting his earlier advice, if the advice did not originate from the days of Oracle 5. [On a related note, I once incorrectly paraphrased one of Tom Kyte’s books (http://books.google.com/books?id=8_WChqD3nc4C&pg=PA256 ) in a Usenet thread, where I mentioned that his book states that an index access path should not be used when more than 20% of a table will be accessed. I feel fortunate that he saw the Usenet post and responded.]
Once again, the author states that the ALL_ROWS OPTIMIZER_MODE favors, no not favors – is the most common cause of, unnecessary full table scans. Are all full table scans evil? No, and index access paths are not necessarily better than an unnecessary full table scan.
If the OPTIMIZER_MODE being set to ALL_ROWS is not the most common cause of unnecessary full table scans, and missing indexes are not to blame, why isn’t Oracle using my index (https://hoopercharles.wordpress.com/2010/05/25/true-or-false-why-isnt-my-index-getting-used/ )? How should this portion of the book be fixed?
The page also states the 40% and 7% rules for index/table scans: remote-dba.net/t_tuning_sql_optimizers.htm