Optimizer Costing 1 – What is Wrong with this Quote?

December 5, 2010 (Modified December 6, 2010)

(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 498 of the book (you might need to view the page directly in the book to fully determine the context of the quote)?

“CPU Based Optimizer Costing

The recently added CPU costing feature, controlled by the _optimizer_cost_model = hidden parameter, enhances the CBO’s capabilities by allowing it to estimate the number of machine cycles necessary for an operation…  Generally, CPU costs are not considered significant unless the entire Oracle instance is using excessive CPU resources.

I/O Costing

… The I/O cost is proportional to the number of physical data blocks read by the operation.  However, the CBO has no prior information on the data buffer contents and cannot distinguish between a logical read (in-buffer) and a physical read.  Due to this shortcoming, the CBO cannot know if the data blocks are already in the RAM data buffers.  The best environment for using CPU costing is for all_rows execution plans, where cost is more noteworthy than with first_rows optimization.”

What, if anything, is wrong with the above quote 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 a slightly modified version of the statement appeared in one other book:

• The previous edition of the book

Other pages found during a Google search of the phrase:

• rampant-books.com/t_oracle_cpu_costing.htm
• dba-oracle.com/art_builder_cpu_io.htm
• dba-oracle.com/art_otn_cbo_p6.htm
• Using Explain Plan from the Oracle documentation library

I am reminded of a quote from page 7 of the book, quite literally the first sentence in the book:

“Oracle tuning is a complex endeavor, and it does not help that Oracle databases are changing constantly.”

Edit: December 6, 2010: Added a couple of more quoted sentences under the heading “CPU Based Optimizer Costing” to reduce the risk of misunderstanding the context of the quote.

DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT 4 – What is Wrong with this Quote?

Decmber 5, 2010

(Back to the Previous 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 530 of the book (please excuse the length of this quote, I tried to make it as short as possible without losing the context of the material, and without destroying the sentence structure)?

“Remember, the db_file_multiblock_read_count parameter is used to tell Oracle how many blocks to retrieve in the single I/O operation and the setting is platform-dependent. The most common settings ranged from 4 to 64 blocks per single multi-block I/O execution.

The ‘automatically tuned’ db_file_multiblock_read_count in 10gr2 and beyond uses external disk workload statistics that are gathered via the dbms_stats.gather_system_stats package to determine the optimal setting.

A sub-optimal setting for db_file_multiblock_read_count can running SQL performance because it can cause the optimizer to favor full-scan access. This would cause some beginners to adjust for this by turning the wrong knob, lowering the setting for optimizer_index_cost_adj instead of using dbms_stats.gather_system_stats.

10gr2 and beyond, the db_file_multiblock_read_count is not used to estimate the average number of blocks read and a separate metric for the estimated number of actual block reads. Instead, the optimizer computes two new values, one for optimizer costing and another for the number of I/O requests.”

What, if anything, is wrong with the above quote 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.