I met quite interesting situation with Result Cache configuration last week, so I think it worst a short blog post.
- You want to enable Result Cache when it was explicitly disabled before (by setting RESULT_CACHE_MAX_SIZE=0)
- ASMM is used to manage memory, SGA_TARGET >=0
- 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;
So what is the problem ?
Situation and solution are described in:
NOTE:1088172.1 Result Cache Can Not Be Enabled
- 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.