Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.
Tuesday, February 28, 2012
Using KEEP and RECYCLE Pools
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
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:
WITH_K_R_2: (As advised by the cache advisors)
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.