Database Buffer Cache

Database Buffer Cache

Oracle database buffer cache is typically the largest portion of the SGA. It has data that comes from the files on disk. Because accessing data from disk is slower than from memory, The main purpose of the database buffer cache is to cache the data in memory for quicker access.

Database Buffer cache:

• Stores copies of data blocks that have been retrieved from the datafiles
• Enables great performance gains when you obtain and update data
• Managed through an LRU  algorithm.
• DB_BLOCK_SIZE determines primary Block size.

When a query is processed, the oracle server process looks in the Database buffer cache for any blocks if needs. If the block is not found in the database buffer cache, the server process reads the block from the data file and places a copy in the database buffer cache. Because subsequent requests for the same block may find the block in memory, then the requests may not require physical reads. The Oracle Server uses an LRU algorithm to age out buffers that have not been accessed recently to make some room for new blocks in the Database Buffer Cache.

The database buffer cache consists of independent sub caches. 

• DB_CACHE_SIZE
• DB_KEEP_CACHE_SIZE
• DB_RECYCLE_CACHE_SIZE

These Independent sub cache can be resized dynamically as below,

         ALTER SYSTEM SET DB_CACHE_SIZE = 100M;

DB_CACHE_ADVICE set to gather statistics for predicting different cache size behavior.

Statistics displayed by V$DB_CACHE_ADVICE

 

Sizing of the Database Buffer Cache: 

The size of each buffer in the Database Buffer Cache is equal to the size of an oracle block, and it is specified by the DB_BLOCK_SIZE parameter. The Database buffer cache consists of independent sub caches for buffer pools and for multiple block sizes. The Parameter DB_BLOCK_SIZE  determines the primary block size, which is used for the SYSTEM tablespace.

Three  parameters define the sizes of the Database Buffer Caches:  

• DB_CACHE_SIZE: Using this parameter, Sizes the default buffer cache buffers only with the primary block size (The Block size defined by DB_BLOCK_SIZE parameter)  and it always exists and can not be set Zero.
• DB_KEEP_CACHE_SIZE: Using this Parameter sizes keep buffer cache, which is used to retain blocks in the memory that are likely to be reused.
• DB_RECYCLE_CACHE_SIZE: By using this parameter, Sizes the recycle buffer cache, which is used to eliminate blocks from memory that have little chance of being reused.

Buffer Cache Advisory

The buffer cache advisory feature enables and disables statistics gathering for predicting behavior with different cache sizes. The Information provided by these statistics can help you to determine the size of  Database Buffer Cache optimally for a given workload.

The Buffer Cache Advisory information is collected and displayed through the V$DB_CACHE_ADVISE View.

The Buffer Cache  Advisory is enabled via the DB_CACHE_ADVICE   initialization parameter. It is a dynamic parameter that can be altered using ALTER SYSTEM command. Three Values  (OFF, ON, READY) is available.

 

DB_CACHE_ADVICE  parameter values: 

OFF: Advisory is turned off and the memory for the advisory is not allocated.

ON:  Advisory is turned on and both CPU and memory overhead is incurred.

Note: Attempting to set the parameter to the ON state when it is in the OFF state may lead to the Fallowing Error: 

ORA-4031  Inability to allocate memory from the Shared Pool when the parameter switched to turned ON.

If The parameter is in a READY state it can be set to ON without error because the memory is already allocated.

READY: Advisory is turned off but the memory for the advisory remains allocated. Allocating the memory before the advisory is actually turned ON  and it will avoid the risk of ORA - 4031.  If the Parameter is Switched to this state from OFF, It Is Possible that an ORA-4031 will be raised.

 

Thank you !!