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.
PleaseOut 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.
-------------------------------
Let's walk through an abstracted scenario. I'm leaving out a lot of detail, but this story will work fine for our purposes. Suppose you are a server process who must query a row, which resides in a specific block. Based on the SQL statement and the data dictionary, you know the file and block number. You're all about speed, so you're hoping the block already resides in the buffer cache. To check this, you need to get the buffer's buffer cache memory address, which resides in its buffer header.
To find the buffer header, you must access the CBC structure. You hash on the file and block number, which points you to a hash bucket. Based on this hash bucket, you look up the associated CBC latch and contend for it. After a few spins, you are able to acquire the latch, so you begin your sequential CBC search. The first buffer header is not the buffer you're interested in, and unfortunately, there is no second buffer header in this cache chain, so you know the buffer does not currently reside in the buffer cache.
You release the CBC latch and make a call to the operating system, asking for your data block. While you're waiting, you're posting a db file sequential read wait event. Finally, you receive the block from the operating system and hold it in your PGA memory. Because you did not make a direct read, before you, or any other server process, can access the buffer, it must be properly inserted into the buffer cache and update all the appropriate structures.
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
PleaseOut 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.
|