|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 the release of Oracle, the database buffer cache consisted of one large pool of buffers that were all treated the same way. Oracle
introduced the capability of having multiple buffer pools for different purposes. Three types of buffer pools are supported:
- Keep buffer pool
- Recycle buffer pool
- Default buffer pool
The following Slideshow explains the purpose of each:
- The keep buffer pool is used for tables, indexes, and other objects whose data you want kept in memory all the time
- The recycle buffer pool is for objects whose data should be overwritten the moment that it's no longer needed.
- Blocks in the default buffer pool are managed in the normal fashion - the least recently used data is aged out as the new data is read in.
Three Buffer Pool Types
Assigning objects to Pools
When you create a database object, such as a table, you have the option of assigning it to one of these buffer pools. Here are some guidelines for assigning objects to pools:
|Keep buffer pool
||Assign small code tables that are referenced frequently to this pool.
|Recycle buffer pool:
||Assign large objects that are read randomly, and that you don't want cluttering the cache to this pool.
|Default buffer pool:
||All other objects, including those that you don't explicitly assign, should go to the default buffer pool.
Do not worry too much about the specifics of placing a given object in one of these pools. For now, it's enough to understand that they exist, and for you to have some idea of their purpose. Later, when you find yourself creating tables and indexes
in a real database, keep these buffer pools in mind. If you have frequently accessed objects that fall into one of the categories listed above,
you may be able to improve performance by assigning that object to the appropriate buffer pool.
A buffer pool is a collection of buffers. The database buffer cache is divided into one or more buffer pools.
You can manually configure separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks.
You can then assign specific schema objects to the appropriate buffer pool to control how blocks age out of the cache.
The possible buffer pools are as follows:
- Keep pool: This pool is intended for blocks that were accessed frequently, but which aged out of the default pool because of lack of space. The goal of the keep buffer pool is to retain objects in memory, thus avoiding I/O operations.
- Recycle pool: This pool is intended for blocks that are used infrequently. A recycle pool prevent objects from consuming unnecessary space in the cache.
- Default pool: This pool is the location where blocks are normally cached. Unless you manually configure separate pools, the default pool is the only buffer pool.