Thursday, January 18, 2007

Logical Reads and Orange Trees

My previous post was a riddle aimed to challenge us to really think about logical I/O (session logical reads). Usually we think of I/O in terms of OS block(s), memory pages, Oracle blocks, Oracle buffer cache buffers, etc. In Oracle, a logical I/O is neither a measure of the number of buffers visited, nor the number of distinct buffers visited. We could of course craft scenarios yielding these results, but these would be contrived special cases - like an episode of Law and Order only better. Instead, logical I/O is the number of buffer visits required to satisfy your SQL statement. There is clearly a distinction between the number of buffers visited and the number of buffer visits. The distinction lies in the target of the operation being measured: the visits not the buffers. As evidenced in the previous post we can issue a full table scan and perform far more logical I/O operations than there are blocks in the table that precede the high water mark. In this case I was visiting each buffer more than one time gathering up ARRAYSIZE rows per visit.

If I had to gather up 313 oranges from an orchard using a basket that could only hold 25 oranges, then it would take me at least 13 visits to one or more trees to complete the task. Don't count the trees. Count the visits.

4 Comments:

Anonymous Anonymous said...

If you had a large tractor and a chain saw and some steel cabling you could drive to each tree, chain saw it down, attach it to the table, proceed to the next tree and complete it all in one trip.

Not too different from some of the code developers create eh?

Now visiting the tree the next time after it has been chain sawed to the ground. That's the tricky part!

2/17/2007 6:49 PM  
Anonymous Anonymous said...

These comments have been invaluable to me as is this whole site. I thank you for your comment.

6/10/2007 2:36 AM  
Anonymous Anonymous said...

Thanks for the nice post!

9/08/2007 11:22 AM  
Anonymous Viagra Online said...

Writing about something special is the key to get the inspiration to write a good book.

8/23/2010 11:04 AM  

Post a Comment

<< Home