Set the following parameter:
RESULT_CACHE_MAX_SIZE=500M /* Megabytes
RESULT_CACHE_MAX_RESULT=20 /* Percentage
RESULT_CACHE_REMOTE_EXPIRATION=3600 /* Minutes
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
RESULT_CACHE_MAX_SIZE=0
Check the Status of Result Cache:
select dbms_result_cache.status() from dual;
DBMS_RESULT_CACHE.STATUS()
————————–
ENABLED
RESULT_CACHE_MODE initialization parameter to FORCE, 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 is MANUAL, meaning that the database caches query results only if you use the appropriate query hint
alter session set result_cache_mode=force;
For Manual you can use the following query:
select /*+ RESULT_CACHE */ department_id, AVG(salary)
from hr.employees
group by department_id;
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
