Unable to turn on Result Cache with ASMM

I met quite interesting situation with Result Cache configuration last week, so I think it worst a short blog post.

Pre-Reqs:

  1. You want to enable Result Cache when it was explicitly disabled before (by setting RESULT_CACHE_MAX_SIZE=0)
  2. ASMM is used to manage memory, SGA_TARGET >=0
  3. SHARED_POOL_SIZE=0 => managed automatically

When You have increased the value of parameter RESULT_CACHE_MAX_SIZE and rebooted instance, You still see RESULT_CACHE_MAX_SIZE=0 and Result Cache is not enabled:

SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
———————————————
DISABLED

So what is the problem ?

Situation and solution are described in:

NOTE:1088172.1 Result Cache Can Not Be Enabled

Comments:

  • Result Cache is managed as subarea of Shared Pool
  • to explicitly set RESULT_CACHE_MAX_SIZE You have to increase the value of SHARED_POOL_SIZE to be equal or greater than value of RESULT_CACHE_MAX_SIZE
  • The Query Result Cache feature is available in Oracle Enterprise Edition only, so when you set parameter RESULT_CACHE_MAX_SIZE to the value >0 in Standard Edition, then after restart You will get RESULT_CACHE_MAX_SIZE=0 and this feature will be disabled because not Enterprise Edition.
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s