Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Enable result cache on Database 12c

Babar_BaigAug 22 2017 — edited Aug 25 2017

Hi,

I am trying to enable result cache on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 by issuing the following command.

alter system set result_cache_max_size=10 scope=both;

But the system still shows old result_cache value.

SQL> show parameter result

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

client_result_cache_lag big integer 3000

client_result_cache_size big integer 0

result_cache_max_result integer 5

result_cache_max_size big integer 0

result_cache_mode string MANUAL

result_cache_remote_expiration integer 0

Please help

Regards

Babar

Ian Baugaard

Hi,

I have a strange suspicion that your size (Specified in bytes) is too small. Since all the memory components are allocated in granules (You can see this from v$sgainfo), perhaps try the following

alter system set result_cache_max_size=32m scope=both;

Regards

Ian

Babar_Baig

Still no change.

SQL> alter system set result_cache_max_size=32m scope=both;

System altered.

SQL> SHOW PARAMETER RESULT

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

client_result_cache_lag big integer 3000

client_result_cache_size big integer 0

result_cache_max_result integer 5

result_cache_max_size big integer 0

result_cache_mode string MANUAL

result_cache_remote_expiration integer 0

SQL>

SUPRIYO DEY

its happening in my case

Connected to:

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

SQL> show parameter result

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

client_result_cache_lag big integer 3000

client_result_cache_size big integer 0

result_cache_max_result integer 5

result_cache_max_size big integer 2M

result_cache_mode string MANUAL

result_cache_remote_expiration integer 0

SQL> alter system set result_cache_max_size=8M;

System altered.

SQL> show parameter result

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

client_result_cache_lag big integer 3000

client_result_cache_size big integer 0

result_cache_max_result integer 5

result_cache_max_size big integer 8M

result_cache_mode string MANUAL

result_cache_remote_expiration integer 0

SQL> alter system set result_cache_max_size=1m;

System altered.

SQL> show parameter result

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

client_result_cache_lag big integer 3000

client_result_cache_size big integer 0

result_cache_max_result integer 5

result_cache_max_size big integer 1M

result_cache_mode string MANUAL

result_cache_remote_expiration integer 0

Babar_Baig

Do I need to change any other parameter?

SUPRIYO DEY

dataseven

Hello,

Is your result cache enabled?

SELECT dbms_result_cache.status() FROM dual;

if DISABLED you need to set enabled before and then you have to change the parameter result_cache_max_size

Best regards,

1 person found this helpful

Babar_Baig

Well this is exactly what I am trying to do, enable the result cache. The query below shows the result.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()

--------------------------------------------------------------------------------

BYPASS

dataseven

result cache memory area is located in the shared pool so the value result_cache_max_sıze is consumed from shared pool

if shared_pool_size = 0 you need to change this parameter nonzero value in advance

BEst regards,

dataseven

SQL> show parameter shared_pool_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

shared_pool_size big integer 0

SQL> alter system set shared_pool_size=200M;

System altered.

SQL> show parameter shared_pool_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

shared_pool_size big integer 200M

SQL> show parameter result_cache_max_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

result_cache_max_size big integer 0

SQL> alter system set result_cache_max_size=5120000;

System altered.

SQL> show parameter result_cache_max_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

result_cache_max_size big integer 5000K

Best regards,

SUPRIYO DEY

SELECT dbms_result_cache.status() FROM dual;

Database opened.

SQL> SQL> SQL> SQL> SQL>

DBMS_RESULT_CACHE.STATUS()

--------------------------------------------------------------------------------

ENABLED

SQL> BEGIN

DBMS_RESULT_CACHE.BYPASS(TRUE);

DBMS_RESULT_CACHE.FLUSH;

END; 2 3 4

5 /

PL/SQL procedure successfully completed.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()

--------------------------------------------------------------------------------

BYPASS

SQL> show parameter cache

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

client_result_cache_lag big integer 3000

client_result_cache_size big integer 0

data_transfer_cache_size big integer 0

db_16k_cache_size big integer 0

db_2k_cache_size big integer 0

db_32k_cache_size big integer 0

db_4k_cache_size big integer 0

db_8k_cache_size big integer 0

db_big_table_cache_percent_target string 0

db_cache_advice string ON

db_cache_size big integer 0

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_flash_cache_file string

db_flash_cache_size big integer 0

db_keep_cache_size big integer 0

db_recycle_cache_size big integer 0

object_cache_max_size_percent integer 10

object_cache_optimal_size integer 10240000

result_cache_max_result integer 5

result_cache_max_size big integer 1M

result_cache_mode string MANUAL

result_cache_remote_expiration integer 0

session_cached_cursors integer 50

SQL> alter system set result_cache_max_size=2m;

System altered.

SQL> show parameter result

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

client_result_cache_lag big integer 3000

client_result_cache_size big integer 0

result_cache_max_result integer 5

result_cache_max_size big integer 2M

result_cache_mode string MANUAL

result_cache_remote_expiration integer 0

Disabled means Result cache wont be used. It has nothing to do with parameter value result_cache_max_size

See my demo above

1 person found this helpful

Babar_Baig

Thanks a lot

dataseven

yes we r on the same page

however if you set shared pool =0 you cant set result_cache_max_size with nonzero value

otherwise if result cache is disabled you dont need to set result_cache_max_size like you say

BEst regards,

unknown-7404

I am trying to enable result cache on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 by issuing the following command.

First things first.

Have you READ and FOLLOWED the instructions and examples in the documentation?

Post a link to the doc you are using and indicate which steps you followed and the result of each step.

SUPRIYO DEY

How can shared_pool_size be zero. it cant be zero.

If you see a value of zero , it's AMM or ASMM is enabled.When AMM or ASMM enabled it show a value of zero. To see the actual value of the auto tuned component

sql>select * from v$sgainfo.

1 - 14

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 22 2017

Added on Aug 22 2017

#general-database-discussions

14 comments

963 views

Oracle Database Discussions

Enable result cache on Database 12c

Comments

Post Details