24 September 2011

Players report disabled RESULT CACHE...why?

The 14 September quiz tested players' knowledge of the Oracle Database 11g function result cache feature.

Two players wrote to say that their tests did not reproduce the results claimed by our verification code.

It seemed as though the result cache was not working for them. They are both running Enterprise Edition. One player reported the following:

SQL> show parameter result_cache

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 =128M;

System altered.

SQL> show parameter result_cache

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> select dbms_result_cache.status from dual;

STATUS
--------------------------------------------------------------------------------
BYPASS
My Result Cache is disabled and after repeated tries, I am unable to enable it and have no clue how I can fix this.

I am at a loss to explain to these players how to get the result cache feature enabled. So I thought I would invite the PL/SQL Challenge community to offer its wisdom. Any ideas?

8 comments:

  1. Hi.

    Set the RESULT_CACHE_MAX_SIZE parameter to a none-zero number to enable the feature. See:

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams206.htm#sthref616

    Cheers

    Tim...

    ReplyDelete
  2. ... and restart the instance...

    ReplyDelete
  3. Hello All,

    Please make sure that a database restart was performed AFTER modifying parameter RESULT_CACHE_MAX_SIZE.

    Though it is defined as a dynamic parameter,
    if its value is zero when the instance starts,
    then the result cache feature is disabled.

    This seems to be a little bit different from the behavior of other dynamic parameters,
    but it's worth a try.

    Looks like in the case of that specific
    database the value of the parameter was explicitly set to 0 in the instance init file,
    otherwise it would have more than probably derived a non-zero value from other memory parameters (SHARED_POOL_SIZE, SGA_TARGET, MEMORY_TARGET).

    Hope this will help.

    Thanks & Best Regards,
    Iudith

    ReplyDelete
  4. Which version of 11g? According to Metalink note 563828.1, versions 11.1.0.6 to 11.1.0.7 have an issue that may cause the result cache not to function if a minimum value has not been set for the shared_pool_size. The following is the example solution from the MetaLink article:

    *.result_cache_max_size=5242880
    *.shared_pool_size=7000000


    SQL> show parameter result_cache_max_size

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    result_cache_max_size big integer 5M


    SQL> SELECT dbms_result_cache.status() FROM dual;

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

    ReplyDelete
  5. The simple solution looks to be, to resume the use of the result cache by issuing: 'dbms_result_cache.bypass(false)'

    However, I have once experienced with a database that I had initially configured without enough memory and ignoring the installation warnings, that I could not enable the result cache. I had tried anything I could think of, and when my hands were in my hair, I just decided to recreate the database with enough memory and all problems vanished.

    I hope this helps.

    Regards,
    Rob.

    ReplyDelete
  6. Hi All, I am posting my settings and outcome of the quiz result for 14th Sept 2011.
    As you can see my result_cache is enabled now.
    But still I am getting different result!

    SQL> conn / as sysdba
    Connected.
    SQL> set serveroutput on
    SQL> set serveroutput on
    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 3M
    result_cache_mode string MANUAL
    result_cache_remote_expiration integer 0
    SQL> select dbms_result_cache.status from dual;
    STATUS
    --------------------------------------------------------------------------------
    ENABLED

    SQL> conn scott/tiger
    Connected.
    SQL> CREATE TABLE plch_employees
    (
    employee_id INTEGER
    , last_name VARCHAR2 (100)
    , first_name VARCHAR2 (100)
    , date_of_birth DATE
    , salary NUMBER
    )
    /
    2 3 4 5 6 7 8 9
    Table created.

    SQL> BEGIN
    INSERT INTO plch_employees
    2 3 VALUES (100
    4 , 'Feuerstein'
    5 , 'Steven'
    6 , DATE '1958-09-23'
    , 100000);
    7 8
    9 INSERT INTO plch_employees
    10 VALUES (200
    11 , 'Ellison'
    , 'Larry'
    , DATE '1954-05-10'
    12 13 14 , 10000000000000000);
    15
    16 COMMIT;
    17 END;
    18 /

    PL/SQL procedure successfully completed.

    SQL> CREATE OR REPLACE FUNCTION plch_dob (
    employee_id_in IN plch_employees.employee_id%TYPE)
    2 3 RETURN VARCHAR2
    4 RESULT_CACHE
    5 IS
    6 l_return DATE;
    7 BEGIN
    8 SELECT date_of_birth
    9 INTO l_return
    10 FROM plch_employees
    11 WHERE employee_id = employee_id_in;
    12
    13 RETURN TO_CHAR (l_return);
    14 END;
    15 /

    Function created.

    SQL> grant execute on plch_dob to hr;

    Grant succeeded.

    SQL> alter session set nls_date_format='YYYY-MM-DD';

    Session altered.

    SQL> set serveroutput on;
    SQL> BEGIN
    DBMS_OUTPUT.put_line (plch_dob (100));
    END;
    2 3 4
    5 /
    1958-09-23

    PL/SQL procedure successfully completed.

    SQL> conn hr/hr
    Connected.
    SQL> set serveroutput on;
    SQL> ALTER SESSION SET nls_date_format='YYYY-MON-DD'
    2 ;

    Session altered.

    SQL> BEGIN
    DBMS_OUTPUT.put_line (hr.plch_dob (100));
    END;
    / 2 3 4
    1958-SEP-23

    PL/SQL procedure successfully completed.

    ReplyDelete
  7. Hello Viju,

    Something is not completely clear in your example.

    You seem to create the function plch_dob under
    schema SCOTT, and then grant access to schema HR.

    However, on the second test, you call the following:
    BEGIN
    DBMS_OUTPUT.put_line (hr.plch_dob (100));
    END;
    ( and this function owned by HR seems to exist !)

    So, can it be that you have created the same function under BOTH schemas, that is, both SCOTT and HR, and then you effectively have two different functions (though with identic code) with their results cached ?

    This could explain the output you see, in case that each of the two functions used the current
    NLS_DATE_FORMAT when it was first called and cached in the result cache.

    Just a supposition ...

    Thanks & Best Regards,
    Iudith

    ReplyDelete
  8. Viju, I too am having difficulty duplicating your results; however, if the GRANT to HR is performed after calling PLCH_DOB from the SCOTT schema then the cached result is discarded.

    One of the cautions to be observed with RESULT_CACHE is to insure that there is no dependence on not repeating execution of the cached function. The quiz of September 14 demonstrates one situation that should be avoided; i.e., caching a function with behavior that depends on variables other than those passed as formal arguments. Another case that can be problematic is caching functions with side-effects; e.g., updating a table or generating output.

    Cached function results may be discarded for several reasons: the state of the function has been altered (including granting privileges, even when the privilege has already been granted); changes to tables the function depends on; result cache exhaustion, etc. The declared assumptions for the quiz make these cases extremely unlikely; however, even within the constraints of the quiz it is possible that cached results will be discarded.

    ReplyDelete