Managing Multiple Buffer Pools

Problem
You’re using automatic memory management, but have decided to allocate a minimum value for the
buffer pool component. You’d like to configure the buffer pool so it retains frequently accessed
segments, which may run the risk of being aged out of the buffer pool.

Solution
You can use multiple buffer pools instead of Oracle’s single default buffer pool, to ensure that frequently used segments stay cached in the buffer pool without being recycled out of the buffer pool. In order to implement multiple buffer pools in your database, you need to do two things: create two separate buffer pools—the KEEP buffer pool and the RECYCLE buffer pool. Once you do this, you must specify the BUFFER_POOL keyword in the STORAGE clause when you create a segment. For example, if you want a segment to be cached (or pinned) in the buffer pool, you must specify the KEEP buffer pool.

Note: Neither the KEEP nor the RECYCLE pool is part of the default BUFFER CACHE. Both of these pools are outside the default buffer cache.

Here’s how you create the two types of buffer pools.
In the SPFILE or the init.ora file, specify the two parameters and the sizes you want to assign to
each of the pools:
db_keep_cache_size=1000m
db_recycle_cache_size=100m

Here’s how you specify the default buffer pool for a segment:

SQL> alter table employees storage (buffer_pool=keep); Table altered.

How It Works
Configuring a KEEP buffer pool is an ideal solution in situations where your database contains tables that are referenced numerous times. You can store such frequently accessed segments in the KEEP buffer cache. By doing this, you not only isolate those segments to a specific part of the buffer pool, but also ensure that your physical I/O is minimized as well. How large the KEEP buffer pool ought to be depends on the total size of the objects you want to assign to the pool. You can get a rough idea by summing up the size of all candidate objects for this pool, or you can check the value of the DBA_TABLES view (BLOCKS column) to figure this out. While we’re on this topic, we’d like to point out the counterpart to the KEEP buffer pool—the RECYCLE buffer pool. Normally, the Oracle database uses a least recently used algorithm to decide which objects it should jettison from the buffer cache, when it needs more free space. If your database accesses very large objects once or so every day, you can keep these objects from occupying a big chunk of your buffer cache, and instead make those objects age right out of the buffer cache after they’ve been accessed.
You can configure such behavior by allowing candidate objects to use the RECYCLED buffer pool either when you create those objects, or even later on, by setting the appropriate storage parameters, as shown in the following examples (note that you must first set the DB_RECYCLE_CACHE_SIZE initialization parameter, as shown in the “Solution” section of this recipe.
You can execute the following query to figure out how many blocks for each segment are currently
in the buffer cache:

SQL> select o.object_name, count() number_of_blocks from dba_objects o, v$bh v where o.data_object_id = v.objd and o.owner !=’SYS’ group by o.object_name order by count();

When your database accesses large segments and retrieves data to process a query, it may
sometimes age out other segments from the buffer pool prematurely. If you need these prematurely
aged-out segments later on, it requires additional I/O. What exactly constitutes a large table or index
segment is subject to your interpretation. It’s probably safe to think of the size of the object you’re
concerned with by considering its size relative to the total memory you have allocated to the buffer
cache. Oracle figures that if a single segment takes up more than 10% of (non-sequential) physical reads, it’s a large segment, for the purpose of deciding if it needs to use the KEEP or RECYCLE buffer pools. So, a handful of such large segments can occupy a significant proportion of the buffer cache and hurt the performance of the database.

If you have other segments that the database accesses, let’s say, every other second, they won’t age
out of the buffer pool since they are constantly in use. However, there may be other segments that will be adversely affected by the few large segments the database has read into its buffer cache. It’s in such situations that your database can benefit most by devoting the RECYCLE pool for the large segments. Of course, if you want to absolutely, positively ensure that key segments never age out at all, then you can create the KEEP buffer cache and assign these objects to this pool.