Saturday, September 18, 2010

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.

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:

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


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

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