How to enable function result cache

Question

I am trying to use the Oracle Project Results Cache at 11.2.0, so I did the following test:

CREATE OR REPLACE FUNCTION get_test_value RETURN NUMBER RESULT_CACHE AS BEGIN dbms_output.put_line( 'Called' ); RETURN 0; END; SELECT get_test_value FROM dual; 

My example prints Called every time.
I also tried some other examples found on the Internet, but the cache is not used.

I tried ALTER SYSTEM SET result_cache_max_size = 10485760;
Still not working.

I tried ALTER SESSION SET result_cache_mode=FORCE; (which was not necessary) - did not help.

SELECT dbms_result_cache.status FROM dual; always returns DISABLED .

What am I doing wrong?

Answer 1

Which edition are you using? Cache functionality is only available in Enterprise Edition, so if you try to install this standard version, it will not work. This is in the licensing guide .

Answer 2

Im going to add this information here because I found it useful in solving my similar problem.

if you get DISABLED or BYPASS status, remember

* The memory area of ​​the results cache is in the shared pool, so the value of result_cache_max_size is consumed from the size of the shared pool.

check the following options

 show parameter shared_pool_size show parameter result_cache_max_size show parameter result_cache_mode 

you can also try

 BEGIN dbms_result_cache.ByPass(False); END; /