Oracle Performance Firefighting
by Craig Shallahamer

Get the book here



Craig Shallahamer's Blog

You were brought to this page based on an internet search and as a free service to Oracle DBAs.

The text below is an except from the book, Oracle Performance Firefighting, written by Craig Shallahamer of OraPub, Inc. Figures and tables are not included on this page, only their reference.
To order the book in either print or PDF form, click here.


©2009, 2010 by Craig Shallahamer. This is copyrighted material.
Please—Out of respect for those involved in the creation of the book and also for their familes, we ask you to respect the copyright both in intent and deed. Thank you.

-------------------------------

You hash 35,2435, which hashes to CBC 02. If the hash function and the number of buckets have not been changed, hashing this block will always point to CBC 02. So, if the block does exist in the buffer cache, it absolutely must reside in CBC 02. Referencing the CBC, you realize that you must acquire latch CS 800 in shared mode. You go through the spinning and sleeping algorithm, burning CPU and sleeping (screaming "latch free cache buffer chain!"), but finally do acquire latch CS 800.

Now with the latch in hand, you begin traversing CBC 02 in the hope of finding buffer 35,2435. You check buffer header BH 165 and discover it is associated with another buffer, so you move onto buffer header BH 145. You discover this buffer header is associated with another buffer, so you continue. But you hit the end of CBC 02, and therefore you know that buffer 32,2435 is not in the buffer cache. You release the CBC latch CS 800.

You still need to access the buffer, so you can't simply give up. While the buffer is not in memory, it is in a database file on disk (otherwise the data dictionary would not have showed this block was of interest to you). So you must make a system call to the IO subsystem requesting your single block. Just before you make the IO request, you ask the operating system for the time by issuing the gettimeofday system call. While you are waiting for the operating system to return the block, you are yelling, "db file sequential read!"

©2009, 2010 by Craig Shallahamer. This is copyrighted material.
Please—Out of respect for those involved in the creation of the book and also for their familes, we ask you to respect the copyright both in intent and deed. Thank you.


Know what's important before it's too late!

OraPub's
Performance Training

is like no other...





More Class Pics...
Get student testimonials!