Configure, Use, flush ,disable the result cache in Oracle

Result Cache is used as a buffer in SGA for keeping the most recent result of the queries when they fetch again by user then it return result directly from Result cache buffer area.

Check the Status of Result Cache

select dbms_result_cache.status() from dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------
ENABLED


For Enable/using the RESULT CACHE
We need to set the following parameters. These will set the result cache buffer in Oracle.

RESULT_CACHE_MAX_SIZE=500M /* Megabytes
RESULT_CACHE_MAX_RESULT=20 /* Percentage
RESULT_CACHE_REMOTE_EXPIRATION=3600 /* Minutes

Parameter meaning

RESULT_CACHE_MAX_SIZE:memory allocated to the server result cache.
RESULT_CACHE_MAX_RESULT:maximum amount of memory a single result cache can use, in percentage  (default is 5 percent)
RESULT_CACHE_REMOTE_EXPIRATION: By default, any result that involves remote objects is not
cached. Thus, the default setting of the RESULT_CACHE_REMOTE_EXPIRATION parameter is 0.
You can, however, enable the caching of results involving remote objects by setting an explicit
value for the RESULT_CACHE_REMOTE_EXPIRATION parameter


Disable the server result cache
We need to set the size parameter equal to zero.

RESULT_CACHE_MAX_SIZE=0

Alter system set RESULT_CACHE_MAX_SIZE=0 scope=both;


RESULT_CACHE_MODE
RESULT_CACHE_MODE initialization parameter to FORCE means the database caches all query results unless
you specify the /*+ NO_RESULT_CACHE */ hint to exclude a query’s results from the cache. The default and the recommended value of this parameter should be MANUAL, meaning that the database caches query results only if you use the appropriate query hint.

Change the parameter RESULT_CACHE_MODE

alter session set result_cache_mode=force;
alter session set result_cache_mode=MANUAL; -- Default


For Manual you can use the HINTS in the query:

-- For using result cache
select /*+ RESULT_CACHE */ department_id, AVG(salary)
from hr.employees
group by department_id;

-- For not using result cache
select /*+ NO_RESULT_CACHE */ prod_id, SUM(amount_sold)
from sales
group by prod_id
order by prod_id;


Clear the result cache

execute dbms_result_cache.flush


Enable result cache of Table

ALTER TABLE sales RESULT_CACHE (MODE FORCE);


View memory usage stats of Result Cache

SET SERVEROUTPUT ON
EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT