June 13, 2010 (Modified June 14, 2010)
I found an interesting couple of lines in the June 2010 printing of the “Oracle Performance Firefighting” book. This quote is from page 43 – I added the text in the square brackets [ ] to provide context for the quote:
Digging a little deeper [into the results of the readv Linux OS call that completed in 0.6ms], we can assert that all the requested blocks must have resided in memory (but not Oracle’s buffer cache memory, since Oracle needed to request them from the operating system). We can make this assertion because a physical spinning IO device cannot return 16 nonsequential blocks (the blocks could be scattered over many physical devices) in less than a single millisecond!
[One sentence removed to cut down the amount of typing for this quote – sentence basically states that Oracle assigns the time consumed to an Oracle wait event.] Regardless of the operating system or the actual system call, Oracle gives a multiblock IO call the special name db file scattered read, because these multiple blocks can be scattered over the IO subsystem.”
This book was written by Craig Shallahamer, had six technical reviewers (I recognize the names of three), and was printed by Good Catch Publishing.
What, if anything, is wrong with the above quote?
Edit June 14, 2010:
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.
Charles Hooper's Oracle Notes 
Hi,
Is it that scattered read means the blocks get scattered in memory locations, not that they are scattered on disk?
jason.
Jason,
The point that you mention, where the book deviates from the Oracle Database documentation and just about every other book that describes the db file scattered read wait event, is what initially caught my attention.
I think that there might be multiple additional answers regarding what is wrong with the above quote (but then I could be wrong too 🙂 ).
Hmmm, I thought, and think my source is an anecdote in the Oak Table tales book, that scattered read and sequential read got their names in an all weekend session to instrument Oracle 7 to see why a benchmark showed worse results than v6, and that in fact the names were mistakenly swapped.
Niall,
I faintly remember reading something like that also, but cannot find the source. I paged through the “Oracle Insights – Tales of the Oak Table” book and did not find it in that book (I could have overlooked it – if someone finds it, please provide a reference to the source). I searched and found a reference in the “Oracle Wait Interface” book (one of that book’s authors was a technical reviewer of the Firefighting book):
http://books.google.com/books?id=14OmJzfCfXMC&pg=PA55&
That book points back to an article that Jeff Holt (worked on the “Optimizing Oracle Performance” book) wrote which was titled “Why are Oracle’s Read Events ‘Named Backwards’? A search for that article lead me to this page:
http://www.hotsos.com/e-library/abstract.php?id=16
From that article (page 1):
The article also explains why the faster disk access provided by the read() function was not used for multiblock reads.
So, what kind of a parallel read operation is the book author describing that allows Oracle to read multiple non-sequential blocks in a single call? I wonder if that operation is handled by the readv() function?
Anything else wrong with the quote? Here is a hint:
My netbook, running Windows 7 Professional and Oracle Database 11.2.0.1, is able to report read times on db file scattered read events of less than 0.6ms (the author was talking about multiblock reads of 16 blocks of 8KB size, just 128KB per read request).
To my understanding, the ‘db file scattered read’ waitevent indicate a read which reads multiple blocks (as indicated by the value in P3), to gain benefit from (potential) readahead optimisation in the underlying IO layers. These blocks are logically ordered on the filesystem layer, but get scattered through the buffercache (because the block placing depends on availability of space in the buffercache)
1) “… because a physical spinning IO device cannot return 16 nonsequential blocks (the blocks could be scattered over many physical devices)”
Data striping (or ASM)?
2) “Oracle gives a multiblock IO call the special name db file scattered read, BECAUSE these multiple blocks can be scattered over the IO subsystem.”
I think that BECAUSE (maybe) is wrong.
The number of page views on this blog article and my home page are much higher than typical. I think that I now know why. The article and my home page were mentioned in two responses in this Oracle-L thread:
http://www.freelists.org/post/oracle-l/db-file-sequentialscattered-read-physical-or-logical-io-or-both,12
(I need to determine if Cary was stating that my blog articles/comments were faulty, or if he was commenting that I am pointing out a lot of silly mistakes that are written in books, blog articles, and commercial websites.)
So, how is it that my netbook running Window 7 Professional and Oracle Database 11.2.0.1 are able to see 0.6ms access times for multiblock reads? Oracle running on the Windows platform uses direct, asynchronous IO, so there is no help from the operating system file cache. A couple of possibilies:
* Many hard drives will have at least 8MB of built-in cache memory, and possibly 32MB (or more) cache memory. I believe that my netbook’s hard drive has 16MB of built-in cache, and it may be performing read-ahead optimization.
* The disk controller may have a built-in cache set to read-ahead caching – predicting the next piece of a file that will be read. In my case, the disk controller does not have cache memory.
* The disk head might have been in just the right location over the spinning platter to quickly pick up the data from the drive.
* I might have the netbook connected to a SAN (this is not the case).
* There might have been unaccounted for time that helped the drive platter rotate into position before timing started (see the latest Quiz Night article on Jonathan Lewis’ blog for an example).
* The hard drive in the netbook might be a hybrid – part SSD and part spinning rust (this is not the case).
* The hard drive might actually be a SSD (this is not the case – yet).
—
Now on to another problem that I have with the quote. “Oracle gives a multiblock IO call the special name db file scattered read.” What could be wrong with this statement – yes, a multiblock IO *could* be reported as a db file scattered read:
* On Oracle 11.1.0.6 and above, Oracle just might decide to do a direct path read rather than a df file scattered read while performing a full table scan – I do believe that a direct path read could be a multiblock IO. Direct path reads are also used during parallel execution.
* Index pre-fetch might use a db file parallel read, where multiple non-adjacent blocks are read in a single IO call – I believe that type of access could be considered a multiblock IO.
* The direct path read temp wait events might appear as a result of a multiblock IO of a temp tablespace.
So, what has yet to be mentioned about the quote?
fast full index scans I believe
Frits,
Thank you for the reply. Your comment is a little short in terms of context. I *think* that you are indicating that fast full index scans are also likely to perform multiblock IO. If that is the case, I think that your comment applies to the second half of my previous response. Nice addition.
Sorry for being a little short. Fast full index scans are reported as db file scattered read, which follows the quote.
As far as I know, direct path reads AND direct path writes can do multiblock IO, and direct path read temp and direct path write temp can also do multiblock IO.
It also depends on the definition of ‘multiblock IO’: if you look at the control file wait events, at least the waitevents have the ability to report time taken to do IO for reading/writing multiple blocks. But these are not database blocks, but controlfile blocks. The same is true for the log file related waits.
If we start to get really creative, but also not get away a bit from physical IO, you could also wait for multiple blocks from a remote cache with RAC with the ‘gc cr multi block request’ request. Or is that too far off?
Nice expansion of the details – thank you (and all of the other responders) for adding value to this blog article.
[…] file scattered read’ waits aren’t the only multiblock I/Os out there and certainly not the only indicators of full table scans. In 11g a ‘direct path read’ can also indicate a full table scan, and events like […]