DB Buffer cache is one of the key components of the SGA.
It is the area in memory where data blocks read from the datafiles is stored when a query requesting for the data is run. This cache is for improved performance so that further requests for the same data will directly access the data buffers in cache thus reducing physical I/O. The internal algorithm for maintaining blocks in the buffer cache, writing data from the data buffer cache to the disks, removing buffers from the LRU lists etc will be a very complex piece logic, but the description below will give a brief idea of the same.
Data blocks that are loaded into memory are stored as a linked list of DB buffers on an LRU list.
Two such lists are maintained. The LRU list and the LRUW list. The LRUW list consists of dirty buffers which need to be written to disk. While searching for a particular data buffer, Oracle does not search through the entire list of buffers, but uses a hashing algorithm to determine the bucket in which the block will reside and then retrieves data from the desired block/s. The information of the data buffer lists is stored in Buffer Headers. These buffer headers contain metadata information of the data buffer lists like the Data block address(DBA), pointer to the previous block, pointer to the next block, the touch count, type of block(data, undo etc). These Buffer Headers are stored as a doubly linked list called a Hash Chain in the permanent area of the shared pool. The LRU and the LRUW lists are protected by a chain latch machanism, to protect the consistency and integrity of data. If data is to be read from/written to any data block, the chain latch has to be taken in exclusive mode. Since there can be multiple server processes trying to access the same data, there can be a contention for these chain latches. Contention for these hash chains has been reduced in later Oracle releases by increasing the number of Hash Chains.
Since data is continuously read from, written to the data buffers these LRU lists have to be properly managed as data had to be read from/written to disk continuously. This reading from/writing to disk is done asynchronously by the DBWR background processes. Each time the data buffers are accessed the touch count is incremented for that bufer. Data buffers are moved up and down or out of the LRU list based on this touch count algorithm.The LRU list is divided into 2 parts, the Hot and Cold portions. The separation point is specified by the _DB_PERCENT_HOT_KEEP | RECYCLE | DEFAULT initialization parameter. Data Buffers are added onto the list at this point and each time a data buffer is accessed and the time interval in which it is accessed basis the _DB_AGING_TOUCH_TIME parameter, the touch count is increased and it is moved up and down this list.
From Oracle 8i onwards, based on reuse frequency of data Oracle has come up with the concept of multiple buffer pools.
Data which is frequently accessed is kept in the KEEP pool. The size of this pool is controlled by the BUFFER_POOL_KEEP parameter. Data that is not frequently accessed such as FTS on large tables, is kept in the RECYCLE pool. The size of this is controlled by the BUFFER_POOL_RECYCLE parameter. For all other purposes the DEFAULT pool is used. This is controlled by the DB_BLOCK_BUFFERS parameter.
The pool in which the table data should reside can be specified at the time of TABLE CREATION as a STORAGE parameter.
It can also be set using the ALTER TABLE command.
From Oracle 9i onwards you can have multiple block sizes in the DB Buffer Cache. The main purpose for this is to transport tablespaces between databases having multiple db block sizes. The DB_nK_BLOCK_SIZE has to be set when creating tablespaces with block size other than default value, else you will get an error when creating the tablespace. The buffer cache parameters for the standard db block sizes are DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE. For the non standard block size there is no Cache for KEEP and RECYCLE. There is one single DB_nK_CACHE_SIZE pool for the non standard block size.
Tuning the BUFFER pool –
From Oracle 10g, there is an Automatic Database Diagnostice Monitor which periodically analyses data from the AWR and provides recommendations for tuning the BUFFER CACHE.
From Oracle 10g the Buffer Cache is automatically tuned by setting up Automatic Shared Memory Management – ASMM. This is set up by setting the STATISTICS_LEVEL parameter to TYPICAL or ALL and the SGA_TARGET parameter to a Non Zero parameter. The db_cache_size, shared_pool_size, java_pool_size and large_pool_size parameters are automatically adjusted within limits of the SGA_TARGET parameter, which cannot exceed the value of SGA_MAX_SIZE. If any of db_cache_size, shared_pool_size, java_pool_size and large_pool_size parameters are set, it serves as the minimum size for that respective shared area. Setting SGA_TARGET to 0 turns off ASMM.
Quote for the day
“Do What You Can With All You Have, Wherever You Are.”- Theodore Roosevelt