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.
Sections below:
- Buffer Cache Overview
- Introducing the Keep and Recycle Pools
- Analyze the Buffer Cache Usage
- Enabling Multiple Buffer Pools
- Dynamic Memory Allocation in Oracle9i
- Guidelines for Initialization Parameters For Oracle8i
- Allocating Segments to Different 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:
col object_name format a30
col to_total format 999.99
SELECT owner, object_name, object_type, count, (count / value) * 100 to_total
FROM (
SELECT a.owner, a.object_name, a.object_type,
count(*) count
FROM dba_objects a,
x$bh b
WHERE a.object_id = b.obj
and a.owner not in ('SYS', 'SYSTEM')
GROUP BY a.owner, a.object_name, a.object_type
ORDER BY 4),
v$parameter
WHERE name = 'db_cache_size'
AND (count / value) * 100 > .005
ORDER BY to_total desc
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):
db_block_buffers > size of db_block_buffers needed for normal operations + 50 *
( db_block_lru_latches - #latches for "keep" pool - #latches for "recycle" pool) +
#buffers for "keep" pool + #buffers for "recycle" pool.
Allocating Segments to Different Pools
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.
The syntax for the buffer pool clause is:
BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
CREATE TABLE table_name (col1 number) STORAGE (BUFFER_POOL KEEP);
ALTER INDEX index_name STORAGE (BUFFER_POOL RECYCLE);
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).
CREATE TABLE table_name (col_1 number, col_2 number) PARTITION BY RANGE (col_1) PARTITION ONE VALUES LESS THAN (10) STORAGE (INITIAL 10k BUFFER_POOL RECYCLE), PARTITION TWO VALUES LESS THAN (20) STORAGE (BUFFER_POOL KEEP)) /
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.
Start the discussion at forums.toadworld.com