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 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
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.
Click following link to read about Keep buffer pool and recycle buffer pool,