Result caching has been around since Oracle database 11g release 1, but it was only recently that I was introduced to its full benefits in PL/SQL functions.  I was at a client site last month looking at a very poorly-written query that included millions of calls to PL/SQL functions, one for every row queried. The query had a ton of optimizer hints and still took hours to execute.

A colleague of mine at the Accenture Enkitec Group who is an Oracle developer by trade put forth that enabling result caching in the PL/SQL functions called could make a big difference in query performance without changing the actual query.  I had heard of result caching when querying tables, but had not used it before nor its PL/SQL function variation.  After enabling the result caching in the functions called by the previously-mentioned query, performance was dramatically increased.  I am now a believer and I want to make you one as well.

I set up a simple demonstration on a 11g release 1 database.  Here is my test table:

SQL> desc jontab
 Name                                      Null?    Type
 —————————————– ——– —————————-
 ID1                                                NUMBER
 LABEL1                                             VARCHAR2(15)
 PI1                                                NUMBER
 DATE1                                              DATE
 URL1                                               VARCHAR2(500)
 ID2                                                NUMBER
 LABEL2                                             VARCHAR2(15)
 PI2                                                NUMBER
 DATE2                                              DATE
 URL2                                               VARCHAR2(500)
 ID3                                                NUMBER
 LABEL3                                             VARCHAR2(15)
 PI3                                                NUMBER
 DATE3                                              DATE
 URL3                                               VARCHAR2(500)
 ID4                                                NUMBER
 LABEL4                                             VARCHAR2(15)
 PI4                                                NUMBER
 DATE4                                              DATE
 URL4                                               VARCHAR2(500)
 ID5                                                NUMBER
 LABEL5                                             VARCHAR2(15)
 PI5                                                NUMBER
 DATE5                                              DATE
 URL5                                               VARCHAR2(500)
SQL> select count(*)
 2  from jontab;

COUNT(*)
 ———-
 1048576

Now a simple PL/SQL function for testing:

create or replace FUNCTION jonfunc (employee_id_in IN number)
 RETURN number
 AS
 v_empno number;
 BEGIN
 SELECT empno
 INTO v_empno
 FROM scott.emp
 WHERE empno = employee_id_in;
 RETURN v_empno;
 EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
 /* Return an empty record. */
 RETURN 0;
 END jonfunc;
 /

I compared the data in the scott.emp table with the column data I planned to use in my test table and the function will NEVER return a value other than zero.

Now I need to run a test query and trace it.  I use the level 12 of the 10046 event in order to get the most complete information I can.  The tracefile_identifier is just to make identifying the trace file easier:

alter session set statistics_level=ALL;
 alter session set tracefile_identifier = ‘jon_trace’;
 alter session set events = ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’;

select id1, jonfunc(id1)
 from jontab;

alter session set events = ‘10046 TRACE NAME CONTEXT OFF’;

I then processed the trace file using tkprof, sorting it by query execution time in descending order:

tkprof jon11g_ora_3308_jon_trace.trc output.log sort=EXEELA,FCHELA

Here is the particular session regarding the calls to our test function:

SQL ID: 5r2ba62345bqa
 Plan Hash: 56244932
 SELECT EMPNO
 FROM
 SCOTT.EMP WHERE EMPNO = :B1
call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        0      0.00       0.00          0          0          0           0
Execute 1048572     56.97      72.48          0          0          0           0
Fetch   1048572      4.26       6.56          0    1048572          0           0
——- ——  ——– ———- ———- ———- ———-  ———-
total   2097144     61.23      79.05          0    1048572          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 92     (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
—————————————-   Waited  ———-  ————
SQL*Net message to client                       1        0.00          0.00
SQL*Net message from client                     1       14.47         14.47
latch: shared pool                             51        0.00          0.03
********************************************************************************

There were 1048572 calls to this function taking a total elapsed time of 79 seconds.  Let’s enable result caching in the test function:

create or replace FUNCTION jonfunc (employee_id_in IN number)
 RETURN number
 RESULT_CACHE  --enabling result caching
 AS
 v_empno number;
 BEGIN
 SELECT empno
 INTO v_empno
 FROM scott.emp
 WHERE empno = employee_id_in;
 RETURN v_empno;
 EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
 /* Return an empty record. */
 RETURN 0;
 END jonfunc;
 /

After rerunning the test query and processing the resulting trace file:

SQL ID: 5r2ba62345bqa
Plan Hash: 56244932
SELECT EMPNO
FROM
SCOTT.EMP WHERE EMPNO = :B1

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           0
——- ——  ——– ———- ———- ———- ———-  ———-
total        3      0.00       0.00          0          1          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92     (recursive depth: 1)

Rows     Row Source Operation
——-  —————————————————
0  INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=0 size=4 card=1)(object id 73202)

********************************************************************************

There was now only one execution of this function which resulted in sub-second performance removing the entire 79 seconds from the previous execution.  It used the result cache exclusively after the first execution since it returned the same value for all subsequent calls.

There is also an option for the result caching enabling that can be used to invalidate the cache whenever a change occurs in the underlying table.  After the result cache is invalidated (cleared), the next call causes an actual execution to create the cache once more.  The option could have been used in the test function with this modified line:

RESULT_CACHE relies on(emp);

I hope that this simple demonstration has convinced you of the usefulness of this option.

Update:

In Oracle version 11g, both release 1 and 2, you cannot enable result caching in a function with invoker rights (i.e. AUTHID CURRENT_USER clause).  If you do, you will receive the following error during compilation:

PLS-00999: implementation restriction 
(may be temporary) RESULT_CACHE is 
disallowed on subprograms in 
Invoker-Rights modules

This limitation is fixed in Oracle version 12c.  As a work around, you can place the ivoker rights in a package or procedure that calls the function with result caching enabled.

 

Advertisements

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