Systems Engineering and RDBMS

PL/SQL Result Cache

Posted by decipherinfosys on March 11, 2009

In Oracle 11g Release 1, new RESULT_CACHE clause is introduced for each PL/SQL function.  When a function is defined with this clause, its result is cached in SGA and is available to all sessions. It means that whenever a function is invoked with different parameter values, each time, result of the function along with the parameters is stored in the cache. So whenever that function is subsequently invoked with similar parameter values, result is obtained from the cache rather than being recomputed again.

Let us start with creating a function. We have TEST table available with following definition. Replace table name ‘TEST’ with appropriate table in the function for testing purpose in your schema.

CREATE TABLE TEST
(
OBJECT_NAME VARCHAR2(30),
OBJECT_TYPE VARCHAR2(30),
FLAGGED CHAR(1)
);

/*Create the function*/
CREATE OR REPLACE FUNCTION GET_TYPE(p_name VARCHAR2)
RETURN VARCHAR RESULT_CACHE AS
v_type   VARCHAR2(30);
BEGIN
SELECT object_Type
INTO v_type
FROM TEST WHERE object_name = p_name;
RETURN v_type;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END GET_TYPE;
/

We have defined the function with  RESULT_CACHE clause. Now let us execute function with different parameters.

/* Execute following statement 2 times.*/
SQL> SELECT get_Type(‘TEST_TABLE’) from dual;

/* Following statement 1 time.*/
SQL> select get_Type(‘TEST_FUNC’) from dual;

/* Following statement 3 times*/
SQL> select get_type(‘TEST_PROC’) from dual;

We executed GET_TYPE function total 6 times ( 2 for TEST_TABLE, 1 for TEST_FUNC and 3 for TEST_PROC). How do we verify whether RESULT_CACHE made any impact or not? For that we need to look at v$ view V$RESULT_CACHE_STATISTICS. Make sure that you have access privilege for v$ views. Execute following sql.
SQL> column name format A30
SQL> select name, value from V$RESULT_CACHE_STATISTICS;

NAME                                VALUE
------------------------------ ----------
Block Size (Bytes)                   1024
Block Count Maximum                  1568
Block Count Current                    32
Result Size Maximum (Blocks)           78
Create Count Success                    3
Create Count Failure                    0
Find Count                              3
Invalidation Count                      0
Delete Count Invalid                    0
Delete Count Valid                      0

We are interested in only two statistics ‘Create Count Success’ and ‘Find Count’). For both of these statistics value shown is 3.  We invoked function for 3 different parameter values so for the first time it couldn’t find the result in cache and hence ‘Create Count Success’ statistics shows value of 3. For subsequent execution of function (1 extra for table and 2 extra for procedure), it already found result in cache for the same parameters and hence Oracle didn’t execute the function body instead displayed it straight from the cache. As a result ‘Find Count’ statistics also shows value as 3. Now perform one extra execution for each one of them and query v$ view again. ‘Create Count’ statistic will remain at 3 and ‘Find Count’ statistic will become 6.

Create Count Success                    3
Find Count                                    6

Now let us re-create the function with option RELIES_ON clause.

CREATE OR REPLACE FUNCTION GET_TYPE(p_name VARCHAR2)
RETURN VARCHAR2 RESULT_CACHE RELIES_ON (TEST) AS
v_type   VARCHAR2(30);
BEGIN
SELECT object_Type
INTO v_type
FROM TEST WHERE object_name = p_name;
RETURN v_type;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END GET_TYPE;
/

Query statistics again after we re-create the function. Since previous result cache was dependent on the function, recreating function invalidated cache. It is obvious from following data. ‘Invalidation Count’ statistics is shown as 3.

Create Count Success                    3
Find Count                                    6
Invalidation Count                         3

Now if we execute the function for the same parameter or argument value, Oracle has to re-compute the cache again. Let us do that and examine the statistics again. For our test case ‘Create Count Success’ statistics will increment by 1.

SQL> SELECT get_Type(‘TEST_TABLE’) from dual;

Statistics after this:

Create Count Success                    4
Find Count                                    6
Invalidation Count                         3

Cache will also become invalidated, if underlying table goes through any updates because we have created function with RELIES_ON clause. Let us update the test table and see the effect. At this point we have only 1 valid result cache, which we executed above after re-creating the function.

SQL> UPDATE test
2     SET flagged = ‘Y’
3   WHERE object_name = ‘TEST_OBJECT’;
SQL> COMMIT;

And the result:

Create Count Success                    4
Find Count                                    6
Invalidation Count                         4

Even though we updated some other record in the table, result cache became invalid because of underlying table update. Dependencies can also be tracked using v$RESULT_CACHE_OBJECTS view.

Certain things we have to remember when we are using RESULT_CACHE.
•    Result of unhandled exception is not stored in the cache.
•    For every unique parameter combination, it will cache the result. If it comes to a point where more memory is required, it starts purging out results to make room for new result to be cached.
•    If table is specified to compute cache, any update in the table invalidates the cache and it should be recomputed again.
•    RELIES_ON clause cannot be specified on package specification.

This is very useful for lookup kind of data, which are used across the application very frequently and either not changed or changed very in-frequently. It can be very well used in recursion as well. With its advantage, it has some restrictions as well.  Major ones are:

•    LOBS, ref cursors, collection parameters cannot be used as input parameter and return values.
•    Functions can’t contain OUT or INOUT parameter.
•    Function cannot be pipeline function.

For complete list of restrictions, check the PL/SQL Language reference manual.

Resources:

  • Oracle developer article – here.
  • PL/SQL Reference – here.
About these ads

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers

%d bloggers like this: