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?
Hi.
ReplyDeleteSet 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...
... and restart the instance...
ReplyDeleteHello All,
ReplyDeletePlease 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
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:
ReplyDelete*.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
The simple solution looks to be, to resume the use of the result cache by issuing: 'dbms_result_cache.bypass(false)'
ReplyDeleteHowever, 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.
Hi All, I am posting my settings and outcome of the quiz result for 14th Sept 2011.
ReplyDeleteAs 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.
Hello Viju,
ReplyDeleteSomething 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
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.
ReplyDeleteOne 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.