Wait Events 1 – What is Wrong with this Quote?

8 12 2010

December 8, 2010

(Forward to the Next Post in the Series)

While reading the “Oracle Tuning the Definitive Reference Second Edition” book I found a handful of interesting suggestions regarding Oracle wait events.  For instance, page 406 of the book states that:

“Excessive db file sequential read waits on an OLTP database might indicate a suboptimal disk subsystem or configuration issues with the disk array.  High I/O waits on the db file scatter read waits associated with large-table full-table scans may not always indicate a problem, but they are most commonly found on these kinds of databases:

  • Data warehouse and Decision Support applications
  • 32-bit Oracle systems with SGAs of less than 1.5 gigabytes
  • Databases that do not have enough buffer cache space to cache their working set of frequently referenced objects”

Keeping in mind that the book is printed after the release of Oracle Database 11.2.0.1 (and possibly 11.2.0.2 for some operating system platforms), what, if anything, is wrong with the above quote?  See example 1 on this blog article for a “Top 5 Timed Events” report section that is similar to what is found on page 406 of the book.

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

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.

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.

Other pages found during a Google search of the phrase:


Actions

Information

2 responses

9 12 2010
Niall Litchfield

HI Charles

My problem with the above text is non version specific. It just isn’t clear what Don means by Excessive and High in the extract above. In the case of the db file sequential read then there are about 2k single block reads recorded in the timespan of the report – we don’t know what that timespan is Don doesn’t say (and so we can’t draw his conclusion that it is I/O bound even) – but 2k disk reads in a timeframe that you can do 25k multiblock reads doesn’t seem excessive. In that case perhaps he means excessively long. Now this makes more sense since his single block reads took on average nearly 3 seconds if the output is to be believed (Frankly I don’t believe it to be reliable but anyway). However the High multiblock reads take on average a reasonable time (and the examples given strongly suggest that High doesn’t mean slow, but caused by workload or server configuration issues).

This section of the book would have been so much better if

1) We had a time frame to understand how Don drew his conclusions – in fact each report should have the timeframe and cpu count listed as the original report does
2) We had a clearer definition of Excessive and High (maybe single block reads that take longer than 20ms for Excessive wait times, or more than 100 IOPS/Underlying Disk for high wait counts.

It would be perfectly reasonable for the casual reader to conclude that any system where these 2 disk events are the top 2 is Disk I/O bound – in the sense that Disk I/O is constraining performance. I’ve seen enough examples of statspack reports on otn and elsewhere where help with the disk subsystem is sought for databases that appear to be on average idle to know that folk do just look at the names of the top events and think that this tells them something about the database application they have.

9 12 2010
Charles Hooper

Niall,

Very well stated response. I agree completely with the listed shortcomings of that sections of the book and how to improve the book. Regarding your OTN comment, I think that it is hard for people to initially understand that a “Top 5″ report will always have something in the “Top 5″, and that something is usually an I/O type of wait event in a well performing database instance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 142 other followers

%d bloggers like this: