Result Cache menedzsment

Preface

The Server Result Cache is a memory pool in the shared pool. This pool contains a SQL query result cache, which stores results of SQL queries, and stores PL/SQL Function result cache, which stores PL/SQL functions returned values. I wrote about this here.

When a query starts, the database looks in the cache memory, and checks if the result exists in the cache or no. If the results exists, then retrieves it from memory, this saves a lot of time. If not, then the database executes the query, and stores the result in the result cache.

The result cache benefit depends on the type of its use, etc. for OLAP databases enjoy significant benefit of result cache, because it executes queries on millions of rows, but the results are some simple numbers.

There are two main initialization parameter of database

RESULT_CACHE_MAX_SIZE maximum size in bytes that the database allocates to result cache in SGA.

RESULT_CACHE_MAX_RESULT maximum amount of server result cache memory that can be used for single result. The default is 5%, and can't be bigger than 75% of shared pool.

The Server result cache size

When the database starts, it allocates memory in the shared pool for server result cache. The size of allocated memory depends on the memory size of shared pool and the memory management system. The database uses the following algorithm: 

• when the size of memory is set by MEMORY_TARGET initialization parameter, then database allocates 0,25% of MEMORY_TARGET to the result cache 
• when the size of shared pool is set by SGA_TARGET, then database allocates 0,50% of SGA_TARGET to the result cache 
• when the SHARED_POOL_SIZE is set, then database allocates 1% of SHARED_POOL_SIZE to the result cache 

If the result of query is greater than cache, then it will not be stored in cache. The Oracle Database uses LRU (Least Recently Used) algorithm to delete data from the cache.

Result Cache Mode

RESULT_CACHE_MODE initialization parameter can have two values.

MANUAL default, the query results will stored in result cache, only if the query uses the /*+ RESULT_CACHE */ hint. This is the recommended value.

FORCE every query result is stored in cache. If we don't want to store all in the cache, then use the /*+ NO_RESULT_CACHE */ hint. 

RESULT_CACHE can be table annotation, its values are DEFAULT or FORCE.