KEEP Buffer Pool and Recycle Buffer Pool

Data required by oracle user process is loaded into buffer cache, if it is not already present in cache. Proper memory tuning is required to avoid repeated disk access for the same data. This means that there should be enough space in buffer cache to hold required data for long time. If same data is required in very short intervals then such data should be permanently pinned into memory. Oracle allows us to use multiple buffers. Using multiple buffers we can control that how long objects should be kept in memory.

KEEP Buffer Pool

Data which is frequently accessed should be kept in Keep buffer pool. Keep buffer pool retains data in the memory. So that next request for same data can be entertained from memory. This avoids disk read and increases performance. Usually small objects should be kept in Keep buffer. DB_KEEP_CACHE_SIZE initialization parameter is used to create Keep buffer Pool. If DB_KEEP_CACHE_SIZE is not used then no Keep buffer is created. Use following syntax to create a Keep buffer pool of 40 MB.

DB_KEEP_CACHE_SIZE=40M

To pin an object in Keep buffer pool use DBMS_SHARED_POOL.KEEP method. This cannot be used to load data buffers into KEEP pool – Thanks to Jospeh for pointing out.

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);

Recycle Buffer Pool

Blocks loaded in Recycle Buffer pool are immediate removed when they are not being used. It is useful for those objects which are accessed rarely. As there is no more need of these blocks so memory occupied by such blocks is made available for others data. For example if ASM is enabled then available memory can be assigned to other SGA components . Use following syntax to create a Recycle Buffer Pool

DB_RECYCLE_CACHE_SIZE=20M

Default Pool

If an object is not assigned a specific buffer pool then its blocks are loaded in default pool DB_CACHE_SIZE initialization parameter is used to create Default Pool. For more information on Default Pool visit following link,

http://exploreoracle.com/2009/03/31/database-buffer-cache/
BUFFER_POOL value in storage clause of schema objects lets you to assign an object to a specific Buffer pool. Value of BUFFER_POOL can be KEEP,RECYCLE or DEFAULT.

6 thoughts on “KEEP Buffer Pool and Recycle Buffer Pool”

  1. Data that is frequently accessed will automatically be kept in the default buffer cache!

    Why create a KEEP buffer pool?

    There is NO NEED to put it in the KEEP pool when it will be in the default buffer cache anyway and if it is accessed “frequently” it won’t be aged out.

  2. Default pool does not guarantee that objects (or its all blocks) will be retained. This may result in multiple disk reads to get data. Remember, only small objects should be pinned in keep pool.

  3. Got confused. I think DBMS_SHARED_POOL is for pinning PL/SQL objects and even sequence in the SHARED POOL memory area. It should have nothing to do with the KEEP and RECYCLE POOls which are BUFFER POOLS i.e. where BLOCKS are cached when accessed for the first time.

Leave a Reply

Your email address will not be published. Required fields are marked *