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.
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.
A recent performance problem on a new supercluster M7 environment forced the removal of result caching at the database level in order to remove enq: RC – Result Cache: Contention waits that were causing serious bottlenecks. Code that utilized result caching was migrated from a non-engineered three-node RAC environment to the supercluster containing four RAC nodes. After the migration, the result cache-enabled code would run for hours, much longer than anticipated on a more powerful system. A trace of the session revealed a large amount of the enq: RC – Result Cache: Contention waits. It was first thought that an increase to the result_cache_max_size initialization parameter was needed, since the default value was being used. An increase brought no relief of these waits, however. Oracle Support suggested setting the result_cache_max_size initialization parameter to zero to disable the cache. No code change was necessary since disabling the result cache would result in ignoring the result cache calls in the code. Once the result cache was disabled, the program execution completed in minutes. It is not clearly understood yet why the result caching exhibited this type of behavior in an engineered system, but it is recommended to disable the result cache if this type of wait is seen.