Database Buffer Cache

Database Buffer cache is one of the most important components of System Global Area (SGA). Database Buffer Cache is the place where data blocks are copied from datafiles to perform SQL operations. Buffer Cache is shared memory structure and it is concurrently accessed by all server processes.

Working of Database buffer Cache

Buffer Cache is organized into two lists

Write List

Write list contains dirty buffers. These are the data blocks which contain modified data and needed to be written to datafiles.

Least Recent Used (LRU) List

Buffers owned by LRU list are categorized into Pinned , Clean, Free or Unused and Dirty buffers. Pinned buffers are currently being used while Clean buffer are available for use. Although Clean buffers contain some data but it is sync with block content stored in datafiles, so there is no need to write these buffer to disk. Free buffer are empty and haven’t been used yet. Dirty buffers are those which needed to be moved to write list.

When oracle server process requires a specific data block, it first searches it in Buffer cache. If it finds required block, it is directly accessed and this event is known as Cache Hit. If searching in Buffer cache fails then it is read from datafile on the disk and the event is called Cache Miss. If the required block is not found in Buffer cache then process needs a free buffer to read data from disk. It starts search for free buffer from least recently used end of LRU list .In process of searching, if user process finds dirty block in LRU list it shifts them to Write List. If the process can not find free buffers until certain amount of time then process signals DBWn process to write dirty buffers to disks.

By default accessed buffers are moved to most recently used end of the LRU list. Search for free buffers is initiated from least recently used end of LRU list, this means that recently accessed buffers are kept in cache for longer time. But when a Full table scan happens, oracle process puts the blocks of table to least recently used end of LRU list. This means that they are quickly re-acclaimed by oracle process. When a table is created, a storage parameter Cache | NoCache| Cache Reads is required. If a table is created with Cache parameter, then the data block of table are added to most recently used end inspite of full table scan.

Size of the Database Buffer Cache

Oracle allows different block size for different tablespaces. A standard block size is defined in DB_BLOCK_SIZEinitialization parameter . System tablespace uses standard block size. DB_CACHE_SIZE parameter is used to defiane size for Database buffer cache. For example to create a cache of800 mb, set parameter as below

DB_CACHE_SIZE=800M

If you have created a tablesapce with bock size different from standard block size, for example your standard block size is 4k and you have created a tablespace with 8k block size then you must create a 8k buffer cache as below.
DB_8K_CACHE_SIZE=256M

Click following link to read about Keep buffer pool and recycle buffer pool,

Keep Buffer Pool and Recycle Buffer Pool

19 thoughts on “Database Buffer Cache”

  1. Hi Guys,
    It is very nice document , I have little confusion
    If my database buffer cache is 4 k and i have to read 8k size table.
    Did the qurey fails?

  2. Your database must contain 8k buffer cache to handle tablespace with 8k block size (if 8k is not your standard block size).

  3. Really , i searched a lot for db cache working but the info i found hear is really a non-comparable with others ……

    very helpfulll

  4. Everything was fine except that when a buffer is accessed then it does go to MRU of LRU list. If I am not wrong, then it should go to mid of the LRU list. There from, on the basis of touch count it does earn the right to placed on hot end means MRU end or on the cold end means LRU end of the LRU list. Please, suggest where am I wrong?

Leave a Reply

Your email address will not be published. Required fields are marked *