SQL Query Cache Result

A dedicated memory buffer stored in the shared pool can be used for storing the retrieving the cached result.  It's cached the result of a query or query block for future reuse by retrieves the data from the database and cache the result in the SQL query cache result.  If a next session using the same query, it is veyr fast because it retrieves the result directly from the cache instead of the disks.  In a RAC environment, each instance has a private result cache and cannot be used by another instance.  However, invalidations work across instance and to handle synchronization between RAC instances, a special process RCGB is use.

• RESULT_CACHE_MODE

       MANUAL:  use the RESULT_CACHE hint to specify results to be stored
       in the cache.
       FORCE:  All results are stored in the cache

• RESULT_CACHE_MAX_SIZE: depends on other memory settings.  It's 0.25% of MEMORY_TARGET or 0.5% SGA_TARGET or 1% of SHARED_POOL_SIZE
• RESULT_CACHE_MAX_RESULT:  Sets max of cache memory for a single result.  5% is the defaults
• RESULT_CACHE_REMOTE_EXPIRATION:  Sets the expired time (default 0)

NAME                                         TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer   3000
client_result_cache_size             big integer   0
result_cache_max_result             integer         5
result_cache_max_size                big integer   2M
result_cache_mode                      string           FORCE
result_cache_remote_expiration  integer     0

Select /*+ RESULT_CACHE */ col1, col2
From T1

If you don't want to store the result of a query in a result cache, you must use NO_RESULT_CACHE hint in your query.  For example..

Select /*+ NO_RESULT_CACHE */ col1, col2
FROM T1

Query Result Cache Views:
V$RESULT_CACHE_STATISTICS:  cache settings and memory usage statistics
V$RESULT_CACHE_MEMORY:  memory blocks and statistics
V$RESULT_CACHE_OBJECTS:  objects cache results
V$RESULT_CACHE_DEPENDENCY:  cache results and dependencies