Create Multiple Data Buffer Pools

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 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.

ALTER TABLE sample_table CACHE; 

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.
In the next lesson, we will look at configuring multiple buffer pools.