* 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.
Server Result Cache
Query execution without result cache hint - Oracle 11gQuery execution without result cache hint – Oracle 11g
Query execution with result cache hint.
Query execution with result cache hint - Oracle 11gQuery 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
Posts
