* 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
APTIMASTERS is basically an online aptitude site which provides the help for the students to make their aptitude skills more enhanced so that they can overcome the entrance examinations for CAT,MAT,UPSC ,Placement Exams etc .
ReplyDeleteNice
ReplyDeleteThis is very informative post. You get to know new advancement in various technologies. Oracle 11g introduced its new feature that is called Server Result Cache. You can study the entire flow in the figure. You are doing great work.
ReplyDelete