Lesson 4	Multiple buffer pools
Objective	Describe the purpose of each of the three types of buffer pools.

Three Types of Buffer Pools

Prior to Oracle8, the database buffer cache consisted of a single large pool of buffers, all treated identically regardless of how the data inside them was actually being used. Oracle8 introduced the ability to configure multiple buffer pools, letting you separate objects by access pattern instead of lumping everything into one undifferentiated cache. Three types of buffer pools are supported:

Keep buffer pool
Recycle buffer pool
Default buffer pool

The diagram below shows all three side by side, along with what each one is for.

Three Types of Buffer Pools

Database Buffer Cache showing Keep, Recycle, and Default buffer pools — Figure 1: The Keep, Recycle, and Default buffer pools within the Database Buffer Cache. The Keep Pool retains frequently accessed segments in memory for as long as the instance runs. The Recycle Pool releases infrequently reused blocks as soon as they are no longer needed, so a single large scan does not displace hot data elsewhere in the cache. The Default Pool follows the standard LRU algorithm, aging out the least recently used blocks as new blocks are read in.

The Keep buffer pool is for tables, indexes, and other objects whose data you want kept in memory continuously, small lookup tables that get hit on nearly every transaction being the classic example.
The Recycle buffer pool is for objects whose data should be discarded the moment it's no longer needed, so a single large scan doesn't push everything else out of the cache behind it.
Blocks in the Default buffer pool are managed in the normal fashion: the least recently used data ages out as new data is read in. This is where every object lives unless you explicitly configure otherwise.

Other Pools in the SGA

Buffer pools aren't the only memory Oracle sets aside for specialized purposes. The SGA includes several other pools worth knowing about:

Large pool: memory for I/O server processes, RMAN backup and recovery, and session memory for shared server connections and Oracle XA distributed transaction processing.
Java pool: memory for Java objects and Java execution, including data used by the JVM running inside the database.
Streams pool: buffers replication and streaming messages in the SGA rather than in database tables. Historically this backed Oracle Streams specifically; Streams itself has been deprecated for years in favor of Oracle GoldenGate, but the pool and its underlying parameter kept their original names, and today the Streams pool is primarily exercised by GoldenGate's integrated capture and apply processes and by XStream.

Each of these has its own dynamic initialization parameter: LARGE_POOL_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE. All three are automatically sized whenever MEMORY_TARGET or SGA_TARGET is specified, alongside the buffer cache and shared pool, so in most environments you never need to set them by hand at all.

Assigning Objects to Pools

When you create a database object, such as a table or index, you can assign it to one of these buffer pools explicitly. Here are some general guidelines for making that choice:

Keep buffer pool	Assign small code tables and reference tables that are hit frequently to this pool.
Recycle buffer pool:	Assign large objects that are read randomly and infrequently, and that you don't want cluttering the rest of the cache, to this pool.
Default buffer pool:	Everything else, including objects you don't explicitly assign, goes to the default buffer pool.

Don't worry too much about the specifics of placing a given object in one of these pools right now. For this lesson, it's enough to understand that they exist and to have a general sense of what each one is for. Later, when you're creating tables and indexes in a real database, keep these pools in mind. If you have frequently accessed objects that fall into one of the categories above, you may be able to improve performance by assigning them to the appropriate pool.

Configuring and Assigning Pools

A buffer pool is simply a named collection of buffers within the database buffer cache; the cache as a whole is divided into one or more of these collections. By default, every object shares the single Default pool. To use Keep or Recycle, you first size them, the same way you would size any other non-default cache component:

ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 200M SCOPE=BOTH;
ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE = 100M SCOPE=BOTH;

Once a pool exists, you assign a specific table or index to it with the BUFFER_POOL storage clause, either at creation time or afterward:

ALTER TABLE lookup_codes STORAGE (BUFFER_POOL KEEP);
ALTER TABLE staging_import STORAGE (BUFFER_POOL RECYCLE);

This is a deliberate, one-time assignment you make as the DBA, not something Oracle does automatically based on how heavily an object happens to be used. An object doesn't get "promoted" into the Keep pool just because it was accessed frequently and aged out of the Default pool; it ends up in the Keep pool only if you put it there. You can confirm where an object currently lives with:

SELECT table_name, buffer_pool FROM user_tables WHERE buffer_pool != 'DEFAULT';

and check overall pool activity, including hit ratios per pool, with:

SELECT * FROM V$BUFFER_POOL;