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

Set the result cache buffer in Oracle

Set the following parameter:


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


Check the Status of Result Cache:

select dbms_result_cache.status() from dual;


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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.