Oracles function result cache (FRC) can in certain cases give a considerable performance boost. Application context is another useful feature. How does FRC work when the function result relies on context-settings? And how can we make them play well together?
Application contexts can be a very useful feature. One advantage is that they are easily and quickly readable from “everywhere”, but only modifiable through a specified package, providing security and control over the settings. Contexts can be local to a single session or global across all sessions.
I got a question about the use of FRC in cooperation with application context a couple a weeks ago. The case was to do different lookups, depending on the application context settings. I did a quick test and found out that:
- FRC is not context aware
- FRC seems to rely on a hash of the input-parameters to do the result-lookup
- Context-values can be passed as default-values in the function parameter list to make it context-aware
I’ve set up the following example:
- A small key-value lookup function.
- The results are dependent on a context value.
- I’ve inserted a one second sleep to indicate when the result cache kicks in.
SQL> CREATE TABLE KeyValues ( 2 Id NUMBER PRIMARY KEY, 3 DomainId NUMBER NOT NULL, 4 KeyName VARCHAR2(100) NOT NULL, 5 KeyValue VARCHAR2(100) NOT NULL, 6 CONSTRAINT KeyValues_DomainKey_uq UNIQUE(DomainId, KeyName) 7 ); Table KEYVALUES created. SQL> BEGIN 2 INSERT INTO KeyValues 3 VALUES (1, 1, 'Key1', 'Domain 1 value 1'); 4 INSERT INTO KeyValues 5 VALUES (2, 1, 'Key2', 'Domain 1 value 2'); 6 INSERT INTO KeyValues 7 VALUES (3, 2, 'Key1', 'Domain 2 value 1'); 8 INSERT INTO KeyValues 9 VALUES (4, 2, 'Key2', 'Domain 2 value 2'); 10 COMMIT; 11 END; 12 /
We create a session context and a corresponding package to manipulate it
SQL> CREATE CONTEXT MyContext USING pMySession; Context MYCONTEXT created. SQL> CREATE OR REPLACE PACKAGE pMySession AS 2 cContextName CONSTANT VARCHAR2(10) := 'MyContext'; 3 cContextDomainKey CONSTANT VARCHAR2(10) := 'DomainId'; 4 5 PROCEDURE SetDomain(pDomainId IN NUMBER); 6 END pMySession; 7 / Package PMYSESSION compiled SQL> CREATE OR REPLACE PACKAGE BODY pMySession AS 2 PROCEDURE SetDomain(pDomainId IN NUMBER) 3 IS 4 BEGIN 5 dbms_session.set_context(cContextName, cContextDomainKey, pDomainId); 6 END SetDomain; 7 END pMySession; 8 / Package Body PMYSESSION compiled
We create our simple key-value store package:
SQL> CREATE OR REPLACE PACKAGE pKeyValue AS 2 FUNCTION GetKeyValue(pKeyName IN VARCHAR2) RETURN VARCHAR2; 3 END pKeyValue; 4 / Package PKEYVALUE compiled SQL> CREATE OR REPLACE PACKAGE BODY pKeyValue AS 2 FUNCTION GetKeyValue(pKeyName IN VARCHAR2) RETURN VARCHAR2 3 IS 4 vResult KeyValues.KeyValue%type; 5 BEGIN 6 SELECT KeyValue 7 INTO vResult 8 FROM KeyValues kv 9 WHERE kv.KeyName = pKeyName 10 AND DomainId = sys_context(pMySession.cContextName, pMySession.cContextDomainKey); 11 12 dbms_lock.sleep(1); 13 14 RETURN vResult; 15 END GetKeyValue; 16 END pKeyValue; 17 / Package Body PKEYVALUE compiled
Let’s try it out by first setting the domain-id in the context before we call the function. We see that we get different results, depending on the value of the context:
SQL> CALL pMySession.SetDomain(1);
Call completed.
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 1 value 1
Elapsed: 00:00:01.024
SQL> CALL pMySession.SetDomain(2);
Call completed.
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 2 value 1
Elapsed: 00:00:01.028
Let’s implement function result cache:
SQL> CREATE OR REPLACE PACKAGE pKeyValue AS 2 FUNCTION GetKeyValue(pKeyName IN VARCHAR2) RETURN VARCHAR2 RESULT_CACHE; 3 END pKeyValue; 4 / Package PKEYVALUE compiled SQL> CREATE OR REPLACE PACKAGE BODY pKeyValue AS 2 FUNCTION GetKeyValue(pKeyName IN VARCHAR2) RETURN VARCHAR2 RESULT_CACHE 3 IS 4 vResult KeyValues.KeyValue%type; 5 BEGIN 6 SELECT KeyValue 7 INTO vResult 8 FROM KeyValues kv 9 WHERE kv.KeyName = pKeyName 10 AND DomainId = sys_context(pMySession.cContextName, pMySession.cContextDomainKey); 11 12 dbms_lock.sleep(1); 13 14 RETURN vResult; 15 END GetKeyValue; 16 END pKeyValue; 17 / Package Body PKEYVALUE compiled
We flush the result-cache to make sure it’s empty before we run the test:
SQL> BEGIN
2 dbms_result_cache.FLUSH;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> CALL pMySession.SetDomain(1);
Call completed.
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 1 value 1
Elapsed: 00:00:01.017
SQL> CALL pMySession.SetDomain(2);
Call completed.
Elapsed: 00:00:00.001
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 1 value 1
Elapsed: 00:00:00.003
Result cache kicks in and we get the wrong result. This proves that FRC is NOT context-aware!
There’s only one entry in the result-cache, since the signature of the two calls is the same:
SQL> SELECT id, type, hash, name
2 FROM V$RESULT_CACHE_OBJECTS
3 WHERE NAME LIKE '%GETKEYVALUE%';
ID TYPE HASH NAME
---------- ---------- ---------- -------------------------------------------------------
424 Result 4150185036 "VEI"."PKEYVALUE"::11."GETKEYVALUE"#963b7b52b7a7c411 #2
What if we send in the domain as a part of the parameters?
SQL> CREATE OR REPLACE PACKAGE pKeyValue AS 2 FUNCTION GetKeyValue(pKeyName IN VARCHAR2, pDomainId IN NUMBER) RETURN VARCHAR2 RESULT_CACHE; 3 END pKeyValue; 4 / Package PKEYVALUE compiled SQL> CREATE OR REPLACE PACKAGE BODY pKeyValue AS 2 FUNCTION GetKeyValue(pKeyName IN VARCHAR2, pDomainId IN NUMBER) RETURN VARCHAR2 RESULT_CACHE 3 IS 4 vResult KeyValues.KeyValue%type; 5 BEGIN 6 SELECT KeyValue 7 INTO vResult 8 FROM KeyValues kv 9 WHERE kv.KeyName = pKeyName 10 AND DomainId = pDomainId; 11 12 dbms_lock.sleep(1); 13 14 RETURN vResult; 15 END GetKeyValue; 16 END pKeyValue; 17 / Package Body PKEYVALUE compiled
Flushing and run the test again:
SQL> BEGIN
2 dbms_result_cache.FLUSH;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT pKeyValue.GetKeyValue('Key1', 1) Value FROM dual;
VALUE
--------------------
Domain 1 value 1
Elapsed: 00:00:01.024
SQL> SELECT pKeyValue.GetKeyValue('Key1', 2) Value FROM dual;
VALUE
--------------------
Domain 2 value 1
Elapsed: 00:00:01.017
SQL> SELECT pKeyValue.GetKeyValue('Key1', 1) Value FROM dual;
VALUE
--------------------
Domain 1 value 1
Elapsed: 00:00:00.004
SQL> SELECT pKeyValue.GetKeyValue('Key1', 2) Value FROM dual;
VALUE
--------------------
Domain 2 value 1
Elapsed: 00:00:00.004
Correct values are returned and the result cache kicks in, lowering response times, quite as expected.
We can also see that we have two entries with differen hash keys in the result-cache-objects-view:
SQL> SELECT id, type, hash, name
2 FROM V$RESULT_CACHE_OBJECTS
3 WHERE NAME LIKE '%GETKEYVALUE%';
ID TYPE HASH NAME
---------- ---------- ---------- -------------------------------------------------------
424 Result 4003681268 "VEI"."PKEYVALUE"::11."GETKEYVALUE"#57c377f814f80786 #2
439 Result 1819365617 "VEI"."PKEYVALUE"::11."GETKEYVALUE"#57c377f814f80786 #2
But we don’t want to pass the domain explicitly, so we pass the context as a default:
SQL> CREATE OR REPLACE PACKAGE pKeyValue AS 2 FUNCTION GetKeyValue( 3 pKeyName IN VARCHAR2, 4 pDomainId IN NUMBER DEFAULT SYS_CONTEXT(pMySession.cContextName, pMySession.cContextDomainKey)) 5 RETURN VARCHAR2 RESULT_CACHE; 6 END pKeyValue; 7 / Package PKEYVALUE compiled SQL> CREATE OR REPLACE PACKAGE BODY pKeyValue AS 2 FUNCTION GetKeyValue( 3 pKeyName IN VARCHAR2, 4 pDomainId IN NUMBER DEFAULT SYS_CONTEXT(pMySession.cContextName, pMySession.cContextDomainKey)) 5 RETURN VARCHAR2 RESULT_CACHE 6 IS 7 vResult KeyValues.KeyValue%type; 8 BEGIN 9 SELECT KeyValue 10 INTO vResult 11 FROM KeyValues kv 12 WHERE kv.KeyName = pKeyName 13 AND DomainId = pDomainId; 14 15 dbms_lock.sleep(1); 16 17 RETURN vResult; 18 END GetKeyValue; 19 END pKeyValue; 20 / Package Body PKEYVALUE compiled
Flush and test:
SQL> BEGIN
2 dbms_result_cache.FLUSH;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> CALL pMySession.SetDomain(1);
Call completed.
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 1 value 1
Elapsed: 00:00:01.014
SQL> CALL pMySession.SetDomain(2);
Call completed.
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 2 value 1
Elapsed: 00:00:01.184
SQL> CALL pMySession.SetDomain(1);
Call completed.
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 1 value 1
Elapsed: 00:00:00.003
SQL> CALL pMySession.SetDomain(2);
Call completed.
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 2 value 1
Elapsed: 00:00:00.003
Yes: We get correct values and the result cache kicks in!
SQL> SELECT ID, TYPE, HASH, NAME
2 FROM V$RESULT_CACHE_OBJECTS
3 WHERE NAME LIKE '%GETKEYVALUE%';
ID TYPE HASH NAME
---------- ---------- ---------- -------------------------------------------------------
424 Result 4003681268 "VEI"."PKEYVALUE"::11."GETKEYVALUE"#57c377f814f80786 #2
439 Result 1819365617 "VEI"."PKEYVALUE"::11."GETKEYVALUE"#57c377f814f80786 #2
As a finishing touch, exposing the extra context-parameter is not necessary an ideal solution. So we create a wrapper-function to hide it:
SQL> CREATE OR REPLACE PACKAGE pKeyValue AS 2 FUNCTION GetKeyValue(pKeyName IN VARCHAR2) RETURN VARCHAR2; 3 END pKeyValue; 4 / Package PKEYVALUE compiled SQL> CREATE OR REPLACE PACKAGE BODY pKeyValue AS 2 FUNCTION GetKeyValue_internal( 3 pKeyName IN VARCHAR2, 4 pDomainId IN NUMBER DEFAULT SYS_CONTEXT(pMySession.cContextName, pMySession.cContextDomainKey)) 5 RETURN VARCHAR2 RESULT_CACHE 6 IS 7 vResult KeyValues.KeyValue%type; 8 BEGIN 9 SELECT KeyValue 10 INTO vResult 11 FROM KeyValues kv 12 WHERE kv.KeyName = pKeyName 13 AND DomainId = pDomainId; 14 15 dbms_lock.sleep(1); 16 17 RETURN vResult; 18 END GetKeyValue_internal; 19 20 FUNCTION GetKeyValue(pKeyName IN VARCHAR2) RETURN VARCHAR2 21 IS 22 BEGIN 23 RETURN GetKeyValue_internal(pKeyName); 24 END GetKeyValue; 25 26 END pKeyValue; 27 / Package Body PKEYVALUE compiled
Flush and test:
SQL> BEGIN
2 dbms_result_cache.FLUSH;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> CALL pMySession.SetDomain(1);
Call completed.
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 1 value 1
Elapsed: 00:00:01.025
SQL> CALL pMySession.SetDomain(2);
Call completed.
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 2 value 1
Elapsed: 00:00:01.012
SQL> CALL pMySession.SetDomain(1);
Call completed.
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 1 value 1
Elapsed: 00:00:00.003
SQL> CALL pMySession.SetDomain(2);
Call completed.
Elapsed: 00:00:00.001
SQL> SELECT pKeyValue.GetKeyValue('Key1') Value FROM dual;
VALUE
--------------------
Domain 2 value 1
Elapsed: 00:00:00.003
Yes: Correct values, speed and encapsulation!
In the result_cache_objects-view we find that the internal function is result-cached:
SQL> SELECT ID, TYPE, HASH, NAME
2 FROM V$RESULT_CACHE_OBJECTS
3 WHERE NAME LIKE '%GETKEYVALUE%';
ID TYPE HASH NAME
---------- ---------- ---------- ----------------------------------------------------------------
424 Result 1463954747 "VEI"."PKEYVALUE"::11."GETKEYVALUE_INTERNAL"#57c377f814f80786 #2
439 Result 482222609 "VEI"."PKEYVALUE"::11."GETKEYVALUE_INTERNAL"#57c377f814f80786 #2
These findings also indicate that results based on for example nls-settings may not be correct.
The wrapper-function may introduce extra context-switches when called from SQL-statements, but I’m not sure, and this blog-post is long enough. 🙂
- nice theory... 