Skip to Main Content

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

Comments

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

no

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,

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

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.

Post Details

Locked on Sep 22 2017
Added on Aug 22 2017
14 comments
963 views