Data Buffer Cache   «Prev  Next»
Lesson 8 Configuring multiple buffer pools
Objective Configure multiple buffer pools.

Configure Multiple Buffer Pools

Parameters

The
  1. DEFAULT,
  2. KEEP, and
  3. RECYCLE buffer pools
are configured using the initialization parameters in the table below.
Parameter Description
DB_BLOCK_BUFFERS Total number of block buffers for all pools
DB_BLOCK_LRU_LATCHES Total number of LRU latches for all pools
BUFFER_POOL_KEEP Number of buffers and latches for the KEEP pool
BUFFER_POOL_RECYCLE Number of buffers and latches for the RECYCLE pool

The KEEP and RECYCLE pools are explicitly configured with buffers and LRU latches, the DEFAULT pool is allocated the remainder from the overall totals specified by DB_BLOCK_BUFFERS and DB_BLOCK_LRU_LATCHES.
To show the actual init.ora parameters, let's assume that we have 5000 total cache buffers to allocate. We can assign 2000 to DEFAULT, 1000 to KEEP and 1000 to the RECYCLE pools with one LRU latch per pool as follows: 

db_block_buffers = 5000
db_block_lru_latches = 3
buffer_pool_keep = (buffers:1000,lru_latches:1)
buffer_pool_recycle = (buffers:1000,lru_latches:1)

Note the syntax for the two new parameters BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE.
The minimum size of the Least Recently Used (LRU) working set is 50 buffers, so the number of buffers configured for any of the pools must be at least 50 times the number of LRU latches. Conversely, the number of LRU latches cannot exceed DB_BLOCK_BUFFERS divided by 50. Each LRU working set in a pool is equally sized at the number of buffers divided by the number of latches.
The maximum number of LRU latches that can be configured for all the pools is six times the number of CPUs on your host computer. For example, if you have an RS-6000 Model S7A processor with 12 CPUs, you can allocate a maximum of 72 LRU latches.

Assigning tables/indices to buffer pools

In Oracle, the CREATE and ALTER table/index STORAGE clause has been expanded to allow for the specification of the buffer pool name. Again, all tables and indices will default to the default pool, so the only valid values will be BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE
Here is an example of a small lookup table being assigned to the KEEP pool: 

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

The following statements accept this new STORAGE clause option:
  1. CREATE CLUSTER
  2. ALTER TABLE
  3. CREATE TABLE
  4. CREATE INDEX
  5. ALTER INDEX
  6. ALTER CLUSTER
However, in the real world, tables and indices can be moved from one pool to another with the ALTER command. For example, suppose that you discover that your CUSTOMER table is seldom re-read by your application, yet there are thousands of first-time reads hitting your default pool.
You can re-assign this table to the RECYCLE pool as follows: ALTER TABLE customer STORAGE (BUFFER_POOL RECYCLE).
You can also size multiple buffer pools. In the next lesson, you will explore buffer cache usage.