7 Replies Latest reply: Jun 22, 2010 1:52 AM by 780094 RSS

    Result Cache Oracle 11gR2

    780094
      Hi all,
      Currently I have some problems with result cache, or maybe I don't understand this feature properly.
      I'm trying to switch off the bypass mode, and I'm not able to do this:

      SQL> select dbms_result_cache.status from dual;

      STATUS
      ----------------------------
      BYPASS

      SQL> exec dbms_result_cache.bypass(FALSE);

      PL/SQL procedure successfully completed.

      SQL> select dbms_result_cache.status from dual;

      STATUS
      -----------------------------
      BYPASS


      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> alter system set result_cache_max_size=2M 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> alter system set result_cache_max_size=2M scope=spfile;

      System altered.

      SQL> shutdown immediate
      Database closed.
      Database dismounted.
      ORACLE instance shut down.
      SQL> startup
      ORACLE instance started.

      Total System Global Area 523108352 bytes
      Fixed Size 1337632 bytes
      Variable Size 465569504 bytes
      Database Buffers 50331648 bytes
      Redo Buffers 5869568 bytes
      Database mounted.
      Database opened.
      SQL> show parameter result

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      client_result_cache_lag big integer 3000
      client_result_cache_size big integer 1G
      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> select dbms_result_cache.status from dual;

      STATUS
      -------------------------------
      BYPASS

      SQL> set serveroutput on;
      SQL> exec dbms_result_cache.memory_report
      R e s u l t C a c h e M e m o r y R e p o r t
      [Parameters]
      Block Size = 1K bytes
      Maximum Cache Size = 0 bytes (0 blocks)
      Maximum Result Size = 0 bytes (0 blocks)
      [Memory]
      Total Memory = 9440 bytes [0.004% of the Shared Pool]
      ... Fixed Memory = 9440 bytes [0.004% of the Shared Pool]
      ... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

      PL/SQL procedure successfully completed.

      SQL>

      Is there something what I missed?


      Thanks for any advices.

      Regards,
      Piotr