Create Multiple Data Buffer Pools and Tuning Options

Oracle Database supports the division of the buffer cache into three separate buffer pools: DEFAULT, RECYCLE, and KEEP. These buffer pools are designed to optimize the management of data within the database buffer cache based on specific usage patterns and priorities. However, I recommend checking the latest Oracle Database documentation or release notes for the most up-to-date information as Oracle Database features and capabilities may have evolved since my last update.
The three buffer pools serve different purposes:

1. DEFAULT Buffer Pool:
   • The DEFAULT buffer pool is the primary buffer pool in Oracle Database.
   • It caches frequently accessed data blocks for typical database operations.
   • Data blocks in the DEFAULT pool are managed based on a least recently used (LRU) algorithm.
2. RECYCLE Buffer Pool:
   • The RECYCLE buffer pool is used to cache data blocks that are unlikely to be reused frequently.
   • It is particularly suitable for temporary or transient data, such as sort or hash join temporary data.
   • Data blocks in the RECYCLE pool are also managed using an LRU algorithm but have lower priority compared to the DEFAULT pool.
3. KEEP Buffer Pool:
   • The KEEP buffer pool is designed for caching critical data that must always remain in memory.
   • It is typically used for important reference or lookup tables that need to be readily available without being aged out of the cache.
   • Data blocks in the KEEP pool are managed with a higher priority to ensure they are retained in memory.

To implement and configure these buffer pools, you can use the DBMS_RESOURCE_MANAGER or ALTER TABLESPACE commands. Remember that the effectiveness of these buffer pools depends on your specific database workload and requirements. Analyzing your workload and making adjustments accordingly can help optimize database performance.

Prior to Oracle8, there were very few tuning options for the buffer cache other than assigning buffers using the db_block_buffer parameter. In most cases, the (LRU) least-recently-used algorithm governing the buffer cache worked quite well, and no other tuning was required. However, certain data access patterns have been identified that could benefit from alternative buffer aging algorithms. Starting with Oracle 8 and later, the DBMS allowed the buffer cache to be divided into up to three separate buffer pools to accommodate multiple cache management strategies and assignment of objects to specific buffer pools.

1. DEFAULT
2. RECYCLE
3. KEEP

The DEFAULT pool is used by Oracle for all objects that are not explicitly assigned to one of the other pools. There is always a DEFAULT pool and it will most likely be the largest of the pools under normal circumstances. When the RECYCLE and KEEP pools are not configured, the DEFAULT buffer pool operates the same as the Oracle7 buffer cache.

The RECYCLE pool was created to hold blocks from tables that are accessed randomly and seldom re-read by Oracle. For These types of tables, the blocks will not have a high re-use rate. However, in Oracle7, these blocks could age-out other blocks that are re-read frequently. The Oracle RECYCLE buffer pool is specifically designed to provide working sets of buffers that can be rapidly aged out of the cache.
By isolating blocks from large, randomly accessed tables away from the rest of the buffer cache the RECYCLE pool relieves the pressure these blocks can place on the LRU list. This allows buffers from other objects to age less quickly and increases the chances of subsequent cache hits.
The RECYCLE pool can also be used to place a restriction on the number of buffers any particular table or index can consume in the buffer cache. Since blocks from objects assigned to the RECYCLE pool will not likely be needed again soon, the pool itself can often be far smaller than the number of buffers these objects would occupy in the DEFAULT pool. This results in more memory available for the other pools, increasing their efficiency.

The KEEP pool acknowledges that there are some Oracle tables whose blocks would be better off if they aged slower (or not at all) out of the cache. The Oracle KEEP pool allows objects to be effectively pinned into the buffer cache and excluded from the LRU aging process. The KEEP pool is used to ensure the presence of buffers in the cache regardless of when they were last accessed. This feature should be used carefully, though, as pinning infrequently used objects into the pool can be a waste of memory.

Legacy Note: In Oracle7 the KEEP pool can be simulated with the

ALTER TABLE sample_table CACHE; 

command. This will keep the specified table in the most recently used end of the data buffer.
In the next lesson, we will look at configuring multiple buffer pools.