Overview of Oracle Database Result Cache

Result cache introduce in 11g

It’s area of memory either in SGA/ client application memory

It’s allows results of query to be cached in SGA

Possible to reduce amount of physical I/O, logical I/O, number of sorts, amount of CPU

 

How Result cache works

When a query executes, the database searches the cache memory to determine whether the result exists in the result cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache.

When users execute queries and functions repeatedly, the database retrieves rows from the cache, decreasing response time. Cached results become invalid when data in dependent database objects is modified.

 

Result cache Type

There are two types of result cache there

Server Result cache

Used shared memory

Available to all session

Client Result cache

Using client memory

 

Understanding the DBMS_RESULT_CACHE parameter

The DBMS_RESULT_CACHE package provides an interface to allow the DBA to administer that part of the shared pool that is used by the SQL result cache and the PL/SQL function result cache

It’s including the following parameters

BYPASS specifies that all subsequent statements will bypass the result cache.

FLUSH specifies that all objects will be flushed from cache.

execute dbms_result_cache.flush

STATUS Return the current status of result cache.

Select dbms_result_cache.status() from dual;

SELECT name, value  FROM V$RESULT_CACHE_STATISTICS;

MEMORY_REPORT Print the detailed memory report for result cache.

SET SERVEROUTPUT ON

EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT

 

Result Cache Dynamic Performance view

V$RESULT_CACHE_STATISTICS

V$RESULT_CACHE_OBJECTS

V$RESULT_CACHE_MEMORY

V$RESULT_CACHE_DEPENDENCY