A Useful Oracle 11g New Feature

Server Result Cache

• Enables query result to be cached in memory which can be used during future execution of a similar query by bypassing the regular processing thereby returning the results faster.

• Decreases the wait time for both physical and logical IO by directly fetching the results from the cached memory.

• Cached result set is completely shareable between the sessions and various statements as long as they share a common execution plan.

• Server result cache is the new component of SGA that caches results of queries and is managed by automatic memory management.

• New parameter RESULT_CACHE_MAX_SIZE is used to enable result cache by setting the maximum size of the cache.

• A new optimizer hint allows use of result cache at the query level.

Query execution without result cache hint.

Query execution without result cache hint – Oracle 11g

Query execution with result cache hint.

Query execution with result cache hint – Oracle 11g

Parameters  related to Result Cache

RESULT_CACHE_MAX_RESULT  :  specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use
RESULT_CACHE_MAX_SIZE    :  specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache.
RESULT_CACHE_REMOTE_EXPIRATION   : specifies the number of minutes that a result using a remote object is allowed to remain valid
RESULT_CACHE_MODE    : specifies when a ResultCache operator is spliced into a query’s execution plan.

How to find result cache information:
V$RESULT_CACHE_STATISTICS
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS
V$RESULT_CACHE_DEPENDENCY

DBMS_RESULT_CACHE – PL/SQL API for result cache management:
Functions : Status – displays the current status of the result cache.

SELECT DBMS_RESULT_CACHE.status FROM dual;
STATUS
—————————————————–
ENABLED

Flush: remove all objects from the result cache and release memory.

I will cover some more features in next post . Keep reading