Using the KEEP and RECYCLE Pool

Sometimes, data blocks are read in the
database, and are infrequently reused. Often in a database we also have
data blocks that are of frequent use (hot blocks). It is for these types of
database blocks (and the objects associated with them) that Oracle offers
the keep and recycle buffer pools. In this topic we will quickly review
the database buffer cache, what the keep and recycle pools are, how to
determine which objects to put in the keep pool, how to enable the buffer
pool features, and finally how to assign objects to the buffer pools.

Buffer
Cache Overview

The part of the SGA called the Buffer Cache
holds copies of the data blocks that have been read from the data files.
Oracle will keep frequently accessed blocks in memory to avoid the much
slower physical I/O to access the blocks. Those data blocks that are not
frequently used will be replaced over time with other database blocks.

Oracle segments (i.e. tables and indexes) with
different usage patterns should be treated differently when it comes to
how long Oracle keeps the data in the buffer cache. Before the advent of
the keep and recycle pools, all of the blocks would compete for the same
space in the cache. Blocks that are accessed often should remain cached,
but could be replaced by blocks from a table that is accessed only once.
Now, with the new keep and recycle buffer pools, we can define a set of
memory storage for those objects who’s blocks are transient in nature,
and those who’s blocks tend to be a bit more permanent.

Introducing
the Keep and Recycle Pools

With the Multiple Buffer Pools Feature, the
keep and recycle pools can be used to segment these conflicting blocks.
Once the buffer pools are allocated, specific segments (tables and
indexes) must be manually assigned to a specific buffer pool (or they
default to being assigned to the default buffer pool). Note that in Oracle 10g, the terms are changed to the keep cache and the recycle cache. However, many people still refer to these two as pools. Some of the Oracle documentation still uses the term pool when referring to these caches.

Any segments whose blocks tend to be
accessed with less frequency should be assigned to the recycle pool so
that it does not flush the other segments, either in the default cache or
the keep pool. Segments that contain frequently accessed blocks should be
assigned to the keep buffer pool so that the blocks of those segments will
not be inadvertently removed, thus impacting performance.

Note that the keep buffer pool is used to
retain objects in memory that are likely to be reused. Keeping these
objects in memory reduces physical I/O operations. For optimal performance
you should make sure that you tune your SQL to reduce logical I/O
operations as well as physical operations.

Analyze
the Buffer Cache Usage

If you are to use the keep buffer pool you
need to generate a list of candidate segments to assign to that buffer
pool. One method of determining which segments to assign to the keep
buffer pool is to determine the percentage of the cache being used by each
object. The following query (run as SYS) can aid in this analysis:

This query will list candidate objects that
consume more than .5% of the database buffer cache, and present them as
candidates for the KEEP buffer pool.

The easiest way to determine how much space
you need to allocate to the keep buffer pool is to assign the object you
want to store there and then approximate the size of the keep pool needed
by summing the sizes of all of the objects dedicated to this pool, as seen
in this code example:

SELECT SUM(bytes)
FROM dba_segments
WHERE segment_type = 'KEEP'
/

You can also use the V$DB_CACHE_ADVICE to
size all pools configured on an instance. Make the initial cache size
estimate, run the representative workload, then simply query the
V$DB_CACHE_ADVICE view for the pool you want to use. Here is an example of
using the feature to determine the benefit of adding more memory to the
KEEP buffer pool:

SELECT size_for_estimate,
buffers_for_estimate,
estd_physical_read_factor,
estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'KEEP'
AND block_size = (SELECT value FROM v$parameter
WHERE name = 'db_block_size')
AND advice_status = 'ON'

Enabling
Multiple Buffer Pools

The following initialization parameters are
used to define buffer pools. Note that there are different parameters for
Oracle 8i compared with Oracle 9i and Oracle 10g:

Table 1: Initialization Parameters for Buffer Pools

Parameter
Name

Description

Example

BUFFER_POOL_KEEP

This parameter is used in Oracle 8i, and is depreciated in Oracle 9i. This parameter is not available in 10g. Defines the KEEP buffer pool.

alter system set buffer_pool_keep=(buffers:400, lru_latches:2);

BUFFER_POOL_RECYCLE

This parameter is used in Oracle 8i, and is depreciated in Oracle 9i. This parameter is not available in 10g. Defines the RECYCLE buffer pool.

alter system set buffer_pool_recycle=100;

DB_BLOCK_BUFFERS

This parameter is used in Oracle 8i and is still available in Oracle 9i and 10g. It defines the number of buffers (of size db_block_size) for the database buffer cache.

Note that the keep and recycle buffer pools are created from this total amount with the remainder allocated to the default buffer pool. If you are using Oracle 10g, then you will not be able to define the keep and recycle caches with this parameter.

alter system set db_block_buffers=1000;

DB_CACHE_SIZE

Available in Oracle 9i and 10g. This creates the buffer cache for the default database block size. The parameter is defined in bytes, kilobytes (k), megabytes (m) or gigabytes (g). This parameter should be used instead of db_block_buffers.

alter system set db_cache_size=100000000;

alter system set db_cache_size=100m;

alter system set db_cache_size=100000k;

DB_nK_CACHE_SIZE

Available in Oracle 9i and 10g. This creates a buffer pool of block size nK. The block size of the pool can be any power of two up to 16, or 32 on some systems. You can not create a nK cache that is the same block size as the database block size.

alter system set db_2k_cache_size=100000000;

DB_KEEP_CACHE_SIZE

This is the 9i and 10g replacement for the buffer_pool_keep parameter. In 10g, you will not be able to use the buffer_pool_keep parameter. You should change to this parameter in 9i.

alter system set db_keep_cache_size =100m;

DB_RECYCLE_CACHE_SIZE

This is the 9i and 10g replacement for the buffer_pool_recycle parameter. In 10g, you will not be able to use the buffer_pool_recycle parameter. You should change to this parameter in 9i.

alter system set db_recycle_cache_size=100m;

There are a few items to take note of with
regards to the differences between the way the buffer pool is managed between the
Oracle 8i version and the Oracle 9i and 10g versions. First, note that the keep and recycle buffer pools
in 8i consume memory that is already allocated to the database buffer
cache. Thus, they reduce the total overall memory available to the buffer
cache. Second note that these buffer pools require that lru latches be
allocated at the time the buffer pools are assigned. This often meant
increasing the db_block_lru_latches parameter in the parameter file, and it
meant a reduction in LRU latch availability on the main database buffer
cache.

In contrast, Oracle 9i and 10g are much easier.
First, the memory allocated comes from core available shared memory, thus
the keep and recycle buffer pool adds to the total amount of memory
allocated to the SGA. Further, you no longer need to allocate LRU latches
to the buffer pools. Lastly, the buffer pools are now allocated in bytes,
kilo-bytes or mega-bytes rather than blocks. Note though that the keep and
recycle buffer pools only apply to the default database block size and are
not available for other database block sizes. Still, all in all Oracle 9i and 10g
makes things much easier! Also note that the initialization parameters for the keep and recycles pools are mutually exclusive for the 8i and 9i/10g versions.

Dynamic
Memory Allocation in Oracle 9i and 10g

Oracle 9i and 10g allow you to dynamically allocate
or deallocate memory from the database buffer cache or the keep or recycle
buffer pools, providing there is enough memory available. Use the alter
system command to resize the buffer caches up or down, as required. Here
is an example of such an operation:

alter system set db_cache_size=100m scope=both;
alter system set db_keep_cache_size=100m scope=both;
alter system set db_recycle_cache_size=10m scope=both;

In this first example we are changing the
default cache size to 100 megabytes dynamically, so that assuming no error
is generated, this change will take effect dynamically. In the second
example we are setting the keep buffer pool to 100m, and in the final
example the recycle buffer pool is set to 10m. You can size
the caches up or down, as required. Note that dynamic sizing is not
available if your database buffer cache or your keep or recycle pools are
allocated using the old Oracle 8i parameters db_block_buffers,
buffer_pool_keep, or buffer_pool_recycle.

As always there is a catch. The catch to
dynamic memory management is that to add memory you must have prepared the
system for such an event by setting the parameter sga_max_size. This
parameter defines the maximum overall size of the SGA, and no component of
the SGA can be dynamically expanded such that it exceeds the size defined
by sga_max_size. Also note that on a number of systems, sga_max_size will
cause the system to allocate memory up to sga_max_size at instance
startup, essentially making that memory unavailable to other processes or
databases. The only exception to this appears to be Solaris 2.8.

Guidelines
for Initialization Parameters For Oracle 8i

If you are using the keep or recycle buffer
pools in Oracle 8i, you will need to set the db_block_lru_latches
parameter. This parameter must be set to at least:

1 + #latches for "keep"
pool + #latches for "recycle" pool.

Note that the maximum number of LRU latches
that can be specified is limited to:

2 x #CPUs x #possible buffer pools.

Further, you must also configure the
db_block_buffers to at least this minimum setting (though rarely is this a
problem):

Now that memory has been allocated for the different buffer pools, we need to get database segments to use those pools. The BUFFER_POOL clause is used to define
the default buffer pool for an object. This clause is valid for CREATE and
ALTER table, cluster, and index DDL statements. If not specified, the
object will go into the DEFAULT buffer pool.

When the default buffer pool of an object
is changed using the alter statement, all buffers that currently contain
blocks of the altered segment remain in the buffer pool they were in
before the alter statement. Newly loaded blocks and any blocks that have
aged out and are reloaded will go into the new buffer pool.

A few miscellaneous notes need to be
covered at this point. First, a buffer pool is not permitted for a
clustered table. The buffer pool for a clustered table is specified at the
cluster level. For an index-organized table, a buffer pool can be defined
on both the index and the overflow segment.

For a partitioned table, a buffer pool can
be defined on each partition. The buffer pool is specified as a part of
the storage clause for each partition. A buffer pool can be defined during
a simple alter table as well as modify partition, move partition, add
partition, and split partition (for both new partitions).

A buffer pool can be defined during a
simple alter index as well as rebuild, modify partition, split partition
(for both new partitions), and rebuild partition. Finally a buffer pool
can also be defined for each partition of a global or local partitioned
index.