Friday, August 27, 2010

"Cache Fusion" Behavior for Result Cache

With Oracle 11gR2 RAC result cache misses will result in result being retreived from another instances. This behavior was not there on 11gR1.

11gR1 PL/SQL Language Refrence guide's Result Caches in Oracle RAC Environment section "Cached results are stored in the system global area (SGA). In an Oracle RAC environment, each database instance has a private function result cache, available only to sessions on that instance. If a required result is missing from the private cache of the local instance, the body of the function executes to compute the result, which is then added to the local cache. The result is not retrieved from the private cache of another instance."

From 11gR2 PL/SQL Language Refrence guide's Result Caches in Oracle RAC Environment section "Cached results are stored in the system global area (SGA). In an Oracle RAC environment, each database instance manages its own local function result cache. However, the contents of the local result cache are accessible to sessions attached to other Oracle RAC instances. If a required result is missing from the result cache of the local instance, the result might be retrieved from the local cache of another instance, instead of being locally computed."

Other behaviors are same such as result cache invalidations, where invalidation is global.

A new hidden parameter has been introduced in 11gR2 to control this result cache behavior
_result_cache_global TRUE (default) boolean (type) Are results available globally across RAC?
v$result_cache_statistics view has two new statistics
Global Hit Count
Global Miss Count
(Undocumented at the time of writing)