Thursday, January 26, 2012

Database Buffer Cache: The Internals

Guy, I'll try to be simple and sweet to deliver the concept I want to put down here... so here goes nothing

Database Buffer Cache:

We all know that RAMs are faster than Hard Disks. Since Shared Pool is used to cache recently used SQL and PL/SQL stamenets to avoid parsing (high on CPU), Database Cache does the same thing, but instead it caches the blocks (the data) that satisfies the SQL condition.

So When user issues a SQL statement, the server process first sees if the block satisfying the SQL are already there in memory, if not it goes to the disk and put the blocks in buffer cache and  returns the result to the user. Now the subsequent request satisfying the same SQL criteria by same or different user will get the blocks from memory thus avoid I/O to disk. SIMIPLE isn't ?

By the way this is all happening in SGA!

The LRU List:

When Oracle buffer cache is full, oracle LRU (least recently used) algorithm  makes room for subsequent requests for new buffers by aging out the buffers that have been least recently accessed. This allows the Oracle Server to keep the most recently requested data buffers n the Buffer Cache while the less commonly used data buffers are flushed.

LRU also works in shared pool cache!

O boy I love this diagram. Now lets assume this is LRU list. On your left is the MRU (most recently used) side and on your right is LRU (least recently used).

When a request is made for data, the user’s Server Process copies the blocks (A, B, C) from disk into buffers at the beginning (Most Recently Used) of the LRU List. These buffers stay on the LRU List but move toward the  least recently used end as additional data blocks are read into memory and then eventually aged out.

FULL TABLE SCANS are not affected by this! block resulted from FTS will always be copied over to least recently used end so that the all the buffers in the cache are not affected!

Dirty Buffers:

When server process get the data block from disk to memory, both memory and disk are synced. Now when blocks in memory are changed possible due to any DML statement submitted by SQL user, then these blocks in memory get changed and  are now called as 'Dirty Buffers' .

Now dirty buffers can not be over written if oracle needs more blocks to be placed from disc into memory and there is not enough space until these dirty blocks are written to disk first.

The Dirty List:

The mechanism for managing these dirty buffers is called the Dirty List or Write List. Using a checkpoint queue, this list keeps track of all the blocks that have been modified by users through the DML statements, but have not yet been written to disk.

Database Writer (DBW0):

Dirty buffers are written to disk by the Oracle background process Database Writer (DBW0).


1. When server process doesn't find data already in db buffer cache, it goes to the data files and read the data from there.

2. Before the data is read from disk however, the free buffer should be there in db cache to hold that data into memory.

3. while making room for the data in memory, server process moves the dirty buffers from  LRU list to and Dirty List

4.DBW0 writer writes dirty buffers from dirty list to data files when it hits a pre-determined threshold. DBW0 process can also write directly from the LRU list to data files if it doesn't find any free buffers there.


  1. "So When user issues a SQL statement, the server process first sees if the block satisfying the SQL are already there in memory, if not it goes to the disk and put the blocks in buffer cache.."

    How does oracle know which block it needs to read the data from? I mean, once Oracle knows it needs to read (e.g.) block#500, it is easy to check whether that block id in cache or not. But how does it know it needs to read block#500?

    1. Oracle keeps track by rowids of a certain block...not sure if that answers your question though