Data Buffer Cache   «Prev 

Oracle Buffer Cache

Oracle, like many other products uses a buffer cache to manage data blocks in memory. The buffer cache holds copies of data blocks read from the data files comprising the database. The buffer cache is located in the (SGA) System Global Area and is shared by all processes connected to an instance. Some advantages of using a buffer cache are
  1. eliminating physical I/O on frequently accessed blocks,
  2. providing fast access path to find block(s) in memory and
  3. help in maintaining concurrency control and multi-version consistency on blocks.
There are three main lists used to organize the buffers in the Oracle buffer cache:
  1. the dirty list (also called the write list or LRUW),
  2. the least recently used list (also called the replacement list or LRU) and
  3. the hashed chain list.
The LRUW list holds dirty buffers, that is, is a buffer that has been modified but has not been written to disk. The LRU list holds free and pinned buffers as well as dirty buffers that have not yet been moved to the LRUW list.
A free buffer is a buffer that has not been modified and is available for reuse. Pinned buffers are buffers that are currently being accessed so are not candidates for replacement. The hashed chain list holds the same buffers as the LRU and LRUW lists, but buffers on this list are arranged depending on their data block addresses. It is essentially used to cache blocks in the buffer pool. When a user process needs to access a block, it first looks in the hashed chain list to see if the block is already in memory. If found, the block can be immediately used and if not, the block has to be read from a datafile on disk into a buffer in the cache. Before a block can be read into a free buffer needs to be identified and pinned.

The user makes a request to see the PEN item.

The I/O manager checks to see if it is already in the data buffer.

If it is in the buffer, it is fetched.

and passed from the I/O Manager back to the user.

Oracle Tuning Reference
If it is not in the buffer

the PEN row is read from the Oracle disk

Once the PEN row is determined, it is placed in the data buffer

and passed back through the I/O manager.

to the user