How to Enable and Disable 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

RESULT_CACHE_MODE
Setting the RESULT_CACHE_MODE to FORCE means the database will cache all query results unless you use the /*+ NO_RESULT_CACHE */ hint to prevent this. The default and recommended setting is MANUAL, which means results are only cached when you include the proper hint.

Enable the RESULT CACHE

We need to set the following parameters to configure the result cache in Oracle.

-- For Enable the Result cache for all queries

ALTER SYSTEM SET RESULT_CACHE_MODE = FORCE;
ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE = 200M;

-- For manual which use Hint in query for use result cache 

ALTER SYSTEM SET RESULT_CACHE_MODE = MANUAL;

Example: Enable for query using Hint
SELECT /*+ RESULT_CACHE */ * FROM employees;

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 Result Cache

We need to set the size parameter equal to zero.

-- For disable the result cache
ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE = 0;

--Command to alter the parameter
Alter system set RESULT_CACHE_MAX_SIZE=0 scope=both;

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 in Oracle

EXEC DBMS_RESULT_CACHE.FLUSH;