Function result cache and application context – don’t get burned

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. 🙂

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s