Configure Multiple Buffer Pools in Oracle

Oracle’s buffer cache is divided into three types of buffer pools:

1. DEFAULT: Used for most tables and indexes unless otherwise specified.
2. KEEP: Stores frequently accessed data blocks to minimize disk I/O.
3. RECYCLE: Holds data blocks for large, infrequently re-read tables to avoid flooding the cache.

These pools help optimize memory usage by isolating different types of data access patterns, improving performance in transaction processing and decision support systems.

Modern Memory Management

In modern Oracle databases (e.g., Oracle 23ai), buffer pool configuration is managed through Automatic Shared Memory Management (ASMM) or Automatic Memory Management (AMM). These features dynamically allocate memory to buffer pools, reducing the need for manual configuration:

• ASMM: Set the SGA_TARGET parameter to allocate a total size for the System Global Area (SGA). Oracle automatically distributes memory among components, including the buffer cache.
• AMM: Set the MEMORY_TARGET parameter to manage both SGA and Program Global Area (PGA) dynamically.

To configure multiple buffer pools, you can still specify KEEP and RECYCLE pools explicitly using the DB_CACHE_SIZE parameter for the DEFAULT pool and additional parameters for KEEP and RECYCLE pools. For example, to allocate 3 GB to the DEFAULT pool, 1 GB to KEEP, and 1 GB to RECYCLE in an Oracle 23ai database, set the following in the initialization parameter file (init.ora):

DB_CACHE_SIZE = 3G
DB_KEEP_CACHE_SIZE = 1G
DB_RECYCLE_CACHE_SIZE = 1G

This configuration ensures a total of 5 GB for the buffer cache, with the DEFAULT pool receiving 3 GB, and KEEP and RECYCLE each receiving 1 GB. Oracle automatically manages Least Recently Used (LRU) latches for each pool based on the allocated size.

Buffer Pool Constraints

The minimum size for any buffer pool is 50 times the database block size per LRU latch. For example, with an 8 KB block size, each pool must have at least 400 KB (50 × 8 KB) per latch. The maximum number of LRU latches across all pools is typically six times the number of CPUs on the host system. For instance, a server with 12 CPUs supports up to 72 LRU latches.

In Oracle, you can assign tables and indexes to specific buffer pools using the STORAGE clause in CREATE or ALTER statements. Valid buffer pool values are KEEP, RECYCLE, or DEFAULT (if unspecified). Below is an example of assigning a small lookup table to the KEEP pool:

CREATE TABLE tiny_lookup_table
   (state_name  VARCHAR2(20),
    state_abbr  CHAR(2)
   )
   TABLESPACE tablespace_name
   STORAGE (INITIAL 4K
            NEXT 1K
            BUFFER_POOL KEEP);

The following statements support the BUFFER_POOL option in the STORAGE clause:

1. CREATE CLUSTER
2. ALTER TABLE
3. CREATE TABLE
4. CREATE INDEX
5. ALTER INDEX
6. ALTER CLUSTER

To reassign an existing table, such as a CUSTOMER table with infrequent re-reads, to the RECYCLE pool, use:

ALTER TABLE customer STORAGE (BUFFER_POOL RECYCLE);

Sizing buffer pools is an iterative process:

• Start with a large DEFAULT pool (e.g., 80% of total cache) and smaller KEEP and RECYCLE pools (e.g., 10% each).
• Identify tables and indexes for KEEP (frequently accessed) or RECYCLE (large, infrequently re-read) pools based on application behavior.
• Adjust pool sizes using DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE as needed.

Monitor the buffer cache hit ratio using the V$BUFFER_POOL_STATISTICS view:

• KEEP Pool: Aim for a high hit ratio, as it stores frequently accessed data. A declining ratio indicates the pool is too small.
• RECYCLE Pool: Expect a low hit ratio, as it holds data with infrequent re-reads.
• DEFAULT Pool: Balance its hit ratio with overall performance goals.

For example, query the buffer pool statistics:

SELECT name, block_size, current_size, buffers, buffer_hit_ratio
FROM V$BUFFER_POOL_STATISTICS;

Block Size Guidelines

Oracle recommends:

• Smaller block sizes (2 KB or 4 KB): Suitable for online transaction processing (OLTP) or mixed workloads.
• Larger block sizes (8 KB, 16 KB, or 32 KB): Ideal for decision support system (DSS) workloads.

The V$BUFFER_POOL view provides information on all buffer pools available in the instance, helping you monitor and tune their performance.
In the next lesson, you will explore buffer cache usage and advanced tuning techniques.