Oracle Query Result Cache

Introduction

The server result cache is a memory pool within the shared pool.
When a query executes, the database looks in the cache memory to determine whether the result exists in the cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache.
When users execute queries and functions repeatedly, the database retrieves rows from the cache, decreasing response time. Cached results become invalid when data in dependent database objects is modified.

Cache_ID can be obtained from execution plan, and used to retrieve cache information from V$RESULT_CACHE_OBJECTS

Database Parameters:

RESULT_CACHE_MAX_SIZE

This parameter sets the memory allocated to the server result cache. The server result cache is enabled unless you set this parameter to 0, in which case the cache is disabled. By default, on database startup, Oracle Database allocates memory to the server result cache in the shared pool.When using the MEMORY_TARGET initialization parameter to specify the memory allocation, Oracle Database allocates 0.25% of MEMORY_TARGET to the result cache.The size of the server result cache grows until reaching the maximum size.
The database employs an LRU algorithm to age out cached results, but does not otherwise automatically release memory from the server result cache. You can use the DBMS_RESULT_CACHE.FLUSH procedure to purge memory
Oracle Database will not allocate more than 75% of the shared pool to the server result cache.

RESULT_CACHE_MAX_RESULT

This parameter sets the maximum amount of server result cache memory that can be used for a single result. The default is 5%, but you can specify any percentage value between 1 and 100. You can set this parameter at the system or session level.

RESULT_CACHE_REMOTE_EXPIRATION

This parameter specifies the expiration time for a result in the server result cache that depends on remote database objects. The default value is 0 minutes, which implies that results using remote objects should not be cached.

RESULT_CACHE_MODE

RESULT_CACHE_MODE specifies when a Result Cache operator is spliced into a query's execution plan. MANUAL - The ResultCache operator is added only when the query is annotated (that is,hints). FORCE - The ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).FORCE mode is not recommended because the database and clients attempt to cache all queries, which can create significant performance and latching overhead.

Result Cache Report

EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 4064K bytes (4064 blocks)
Maximum Result Size = 203K bytes (203 blocks)
[Memory]
Total Memory = 10696 bytes [0.003% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

Flush Result Cache

exec dbms_result_cache.flush;

Result Cache Statistics

col name format a30
col value format a10
SELECT NAME, VALUE FROM V$RESULT_CACHE_STATISTICS;
NAME                           VALUE
------------------------------ ----------
Block Size (Bytes)             1024
Block Count Maximum            0
Block Count Current            0
Result Size Maximum (Blocks)   0
Create Count Success           0
Create Count Failure           0
Find Count                     0
Invalidation Count             0
Delete Count Invalid           0
Delete Count Valid             0
Hash Chain Length              0
Find Copy Count                0
Global Hit Count               0
Global Miss Count              0


Note that "Find Count" is aggregated at the cache level. 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-1062211/，如需轉載，請註明出處，否則將追究法律責任。