Using KEEP and RECYCLE Pools

We are all familiar with using DB_CACHE_SIZE either directly or by proxy using the SGA* or MEMEORY* automatic memory management settings. We know that generally speaking, adding memory to DB_CACHE_SIZE will improve performance until we have satisfied the memory requirements supporting single block, recursive reads. What happens if we add in KEEP and RECYCLE areas?

To refresh your memories, the KEEP area is a memory cache used to store object blocks that you don’t want to have aged out of the cache. The RECYCLE area is a cache area designed to hold frequently changing or blocks that you don’t want to keep.

In order to effectively use the keep and recycle pool areas, database objects such as tables, indexes and clusters must be assigned to the appropriate pool. The default, understandably, is the DB_CACHE_SIZE controlled (SGA_*, MEMORY_* controlled) pool. For these tests the following objects in a TPC-C schema where assigned as shown:

Cluster: CUSTCLUSTER – RECYCLE
Contains: C_CUSTOMER table
Cluster: DISTCLUSTER – DEFAULT
Contains: C_DISTRICT table
Table: C_HISTORY – RECYCLE (This table is partitioned)
Cluster: ITEMCLUSTER – KEEP
Contains: C_ITEM
Cluster: NORDCLUSTER_QUEUE - DEFAULT
Contains: C_NEW_ORDER table
Table: C_ORDER – DEFAULT
Table: C_ORDER_LINE – DEFAULT
Cluster: STOKCLUSTER – KEEP
Contains: C_STOCK table
Cluster: WARECLUSTER – DEFAULT
Contains: C_WAREHOUSE table

These assignments were made based on how the table was used and if it is frequently changed. Using the cache advisors in the AWR reports from test runs the sizes of the special caches were optimized. Tests were run with the caches turned on and turned off to determine what affects the caches had on performance. With the caches turned off the memory they utilized was returned to the default cache. Using a maximum allocation of 40 gb for Oracle cache areas the basic settings for the memory areas were:

NO_K_R_40:
DB_CACHE_SIZE=40gb
DB_KEEP_CACHE_SIZE=0gb
DB_RECYCLE_CACHE_SIZE=0gb

WITH_K_R:
DB_CACHE_SIZE=30gb
DB_KEEP_CACHE_SIZE=4gb
DB_RECYCLE_CACHE_SIZE=5gb

WITH_K_R_2: (As advised by the cache advisors)
DB_CACHE_SIZE=30gb
DB_KEEP_CACHE_SIZE=7gb
DB_RECYCLE_CACHE_SIZE=3gb

This figure shows the effects of use of the keep and recycle pools on performance.



Figure: TPS verses GB in the Recycle and Keep Caches

NOTE: You cannot use either DB_KEEP_CACHE_SIZE or DB_RECYCLE_CACHE_SIZE with the DB_FLASH_CACHE_SIZE or DB_FLASH_CACHE_FILE settings, if you set up to use both keep and recycle and flash, the flash doesn’t appear to get used.

So, if you have memory available and are in a situation where db file sequential reads are generating waits, assigning appropriate tables and indexes to the KEEP pool can boost performance. If you have large tables that you only want to use a working set from and then discard it, the RECYCLE cache can help.