1

I have two database instances, development and production that are fairly identical. On production, result cache is working fine, in development it is not. The value for the database parameter result_cache_max_size was set to 0 so I reset it to remove the value, bounced the database, and when the database was back up, the value was still set to 0 so I tried starting with a PFILE with no value for result_cache_max_size and same problem. Then I tried setting it to 20M (value from production) and bounced, but same results. I then invoked

SELECT dbms_result_cache.status() FROM dual;

which returns BYPASS.

The parameters result_cache_max_result and result_cache_mode are set to 5 and MANUAL on both databases.

I am using Enterprise Edition on both instances (so question Why is the result cache always disabled in Oracle 11g does not apply to me):

Development:

SYS@extdev02> select banner from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
...

Production:

extuat01> select banner from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
...

Edit: Tried bypass proc per @BalazsPapp answer but no luck. I think I tried it already, but without the second parm, which would have defaulted to false per the documentation. Today's try, including a flush proc just for fun:

SYS@extdev02> select dbms_result_cache.status() FROM dual; 

DBMS_RESULT_CACHE.STATUS()
---------------------------
BYPASS

SYS@extdev02> exec dbms_result_cache.bypass(true, false);

PL/SQL procedure successfully completed.

SYS@extdev02> exec dbms_result_cache.flush

PL/SQL procedure successfully completed.

SYS@extdev02> select dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------
BYPASS

SYS@extdev02> exec dbms_result_cache.bypass(false, false);

PL/SQL procedure successfully completed.

SYS@extdev02> select dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------
BYPASS
Improve this question

1 Answer 1

Reset to default
1

which returns BYPASS.

Then enable it:

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> exec dbms_result_cache.bypass(true, false);

PL/SQL procedure successfully completed.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
BYPASS

SQL> exec dbms_result_cache.bypass(false, false);

PL/SQL procedure successfully completed.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL>

DBMS_RESULT_CACHE.BYPASS

Improve this answer
3
  • I thought I tried that yesterday, but tried it again today, with no change in status of BYPASS
    – Mark Stewart
    Aug 2, 2019 at 19:23
  • 1
    @MarkStewart Ok, then we need the parameters from your spfile. Maybe you have set shared_pool_size to 0 and the database is unable to allocate enough memory for the result cache as described in Result Cache Can Not Be Enabled (Doc ID 563828.1). I was able to reproduce this issue on 19.4 by setting sga_target to 512M and shared_pool_size to 0 in my spfile. If that is the case, then you just simply need to change the memory parameters. Like removing shared_pool_size or setting a minimal value for it. After I removed shared_pool_size=0 from my spfile, result cache came back.
    – Balazs Papp
    Aug 2, 2019 at 20:32
  • Perfect! Checked shared_pool_size in prod; was 2240M. Set shared_pool_size in dev to current size (1280M) and bounce db and it works. Thanks for the MoS link.
    – Mark Stewart
    Aug 2, 2019 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.