Oracle Pools Used to Store the Actual Data in Memory

In this article, I will focus on the Oracle pools that are used to store the actual data in memory. The initialization parameters DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE, and DB_RECYCLE_CACHE_SIZE are the determining factors for memory used to store data.

DB_CACHE_SIZE refers to the total size in bytes of the main buffer cache (or memory for data) in the SGA. Two additional buffer pools are DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE. These additional two pools serve the same purpose as the main buffer cache (DB_CACHE_SIZE), with the exception that the algorithm to maintain the pool is different for all three available pools. Note that the BUFFER_POOL_KEEP, DB_BLOCK_BUFFERS, and BUFFER_POOL_RECYCLE parameters have been deprecated and should no longer be used. Unlike BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE, DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE are not subtracted from DB_CACHE_SIZE; they are allocated in addition to DB_CACHE_SIZE.

The main buffer cache (defined by DB_CACHE_SIZE) maintains the LRU (least recently used) list and flushes the oldest buffers in the list. While all three pools utilize the LRU replacement policy, the goal for the main buffer cache is to fit most data being used in memory.

The keep pool (defined by DB_KEEP_CACHE_SIZE) is hopefully never flushed; it is intended for buffers that you want to be “pinned” indefinitely (buffers that are very important and need to stay in memory). Use the keep pool for small tables (that fit in their entirety in this pool) that are frequently accessed and need to be in memory at all times.

The recycle pool (defined by DB_RECYCLE_CACHE_SIZE) is a pool from which you expect the data to be regularly flushed because too much data is being accessed to stay in memory. Use the recycle pool for large, less important data that is usually accessed only once in a long while (ad hoc user tables for inexperienced users are often put here).

The following examples give a quick look at how information is allocated to the various buffer pools. Remember, if no pool is specified, then the buffers in the main pool are used.

 

• Create a table that will be stored in the keep pool upon being accessed:

 

• Alter the table to the recycle pool:

 

 

• Alter the table back to the keep pool:

 

 

• Find the disk and memory reads in the keep pool: