pl/sql function result cache in 11g
PL/SQL functions enable us to modularise and encapsulate our business logic, following generalised programming best practices. However, there has always been a performance penalty of varying degrees when using our own PL/SQL functions. The biggest penalties are borne when we embed SQL lookups inside PL/SQL functions or when we simply call PL/SQL functions repeatedly from SQL. Context-switching and excessive I/O can degrade performance of even the most simple queries. There is even an overhead in calling the most simple PL/SQL-only functions from within PL/SQL.
Oracle developers have tended to address this performance issue in different ways:
- avoidance: hard-code business logic repeatedly in SQL statements;
- avoidance: join to lookup tables in SQL statements;
- optimisation: cache lookup tables in user-managed PL/SQL arrays and expose them via a function;
- acceptance: pay the price for following good programming practice.
Oracle 11g attempts to address the cost of calling PL/SQL functions in two key ways. For the most marginal gains, there is subprogram inlining which removes the overhead of function calls by re-organising the source code during compilation. For potentially greater and more widespread gains, Oracle has introduced the Cross-Session PL/SQL Function Result Cache (to give it its full name) and we will see how this feature works in this article.
Note that for the remainder of this article, we will refer to the Cross-Session PL/SQL Function Result Cache as the "Function Result Cache".
an overview
The principle behind PL/SQL function result caching is simple. We specify (through syntax) that a PL/SQL function is to be cached; or rather, its results are to be cached. Each time we call the function with a new set of parameters, Oracle executes the function, adds the results to the result cache and returns the result(s) to us. When we repeat the function call, Oracle retrieves the results from the cache rather than re-execute the function. Under certain circumstances, this caching behaviour can result in significant performance gains.
Many developers will recognise this behaviour as being similar to "DIY-caching" using associative arrays (formerly known as "PL/SQL Tables"). It is quite common for developers to cache lookup tables in integer or string-indexed PL/SQL arrays. The Function Result Cache differs from this technique in two important ways:
- memory usage: associative array data is held in PGA memory. This means that cached results are available to a single session and cannot be shared. Memory usage can sometimes increase to unacceptable levels as more sessions initialise and use associative array caches. The new Function Result Cache, however, uses a global memory allocation from the shared pool (managed by Oracle) and the cached results are available across sessions;
- cache invalidation: cached reference data is dependant on its underlying data sources. It is difficult to maintain data invalidations when using associative array caches, despite attempts by Oracle to expose notification technologies in recent versions. With the Function Result Cache, however, cached results can be directly coupled to their underlying data sources. This means that cached results are invalidated and regenerated when transactions occur against the underlying data. This protects the application from using potentially stale reference data.
recommended background reading
The Function Result Cache is one of three caching initiatives added to 11g; the others being the Query Result Cache and client-side OCI caching. The Function Result Cache and Query Result Cache share the same components (such as memory allocation and parameters). For this reason, this article on the Query Result Cache is recommended background reading. It includes details of many of the shared components, the result cache architecture, how it is allocated and managed, what it contains and how to investigate it. This information is not repeated below.
setup
For the investigations in this article, the result_cache_max_size is set to 25M. All other parameters are set to default (details of which are in the recommended reading). All examples are created in the SH supplied schema.
creating a result cache function
We will begin by creating a simple cache-enabled PL/SQL function. We will base this on a Steven Feuerstein best-practice example and create a function to encapsulate the business rule for formatting a full customer name, as follows.
SQL> CREATE FUNCTION format_customer_name ( 2 p_first_name IN VARCHAR2, 3 p_last_name IN VARCHAR2 4 ) RETURN VARCHAR2 RESULT_CACHE IS 5 BEGIN 6 counter.increment(); 7 RETURN p_first_name || ' ' || p_last_name; 8 END format_customer_name; 9 /
Function created.
Note the RESULT_CACHE syntax. This is new to 11g and indicates that we want the results of this function to be cached for each unique combination of the parameters. This is the minimum syntax required to make use of the new Function Result Cache.
In terms of the function we have created, it is standalone for demonstration purposes only (we would use a package for "real" application code). In addition, we have included a call to a COUNTER package. We will use this throughout this article to keep a count of how many times our function is executed.
using a result cache function
We are ready to test our cache-enabled function. As stated, we are going to keep track of how many times this is executed, so we will initialise a counter as follows.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
In the following SQL statement, we will call our function to format the names of a small sample of CUSTOMERS.
SQL> SELECT c.cust_id 2 , format_customer_name( 3 c.cust_first_name, c.cust_last_name 4 ) AS cust_name 5 FROM customers c 6 WHERE ROWNUM <= 10;
CUST_ID CUST_NAME
---------- ------------------------------
43278 Benjamin Taylor
25980 Benjamin Taylor
47056 Benjamin Taylor
18337 Abel Aaron
21892 Abel Aaron
25449 Abel Aaron
29004 Abel Aaron
32559 Abel Aaron
37004 Abel Embrey
40559 Abel Embrey
10 rows selected.
To complete this simple example, we will see how many times the function was executed by printing the current counter, as follows.
SQL> exec counter.show('Function calls');
Function calls: 3 PL/SQL procedure successfully completed.
The counter was incremented just three times, which suggests that the result cache was used for the remainder of the rows in our query. We can verify this anecdotally by examining our query output above. We have just three customer names in our output; although these account for ten rows between them. On the first instance of each of the customer names, Oracle executed the FORMAT_CUSTOMER_NAME function and added the return value to the result cache. On subsequent instances of the customer name parameters, Oracle retrieved the formatted customer name directly from the result cache without executing the function at all.
result cache metadata
The recommended background reading describes a range of dynamic information available about the result cache. We can use some of this information to validate our assumptions about the Function Result Cache behaviour that we observed above. The V$RESULT_CACHE_STATISTICS view keeps an instance-wide record of result cache usage, which we can query as follows.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- ---------- Create Count Success 3 Find Count 7 2 rows selected.
Given that we started with a flushed (empty) result cache, these statistics state that we have made three entries to the result cache so far and have used these results a further seven times. This tallies exactly with what we surmised above.
We know from the background reading that the V$RESULT_CACHE_OBJECTS view exposes the cached queries and their dependencies. PL/SQL functions with result cache entries are also exposed by this view. We will query a small number of columns from this "wide" view as follows.
SQL> SELECT id 2 , name 3 , type 4 , row_count 5 FROM v$result_cache_objects 6 ORDER BY 7 creation_timestamp;
ID NAME TYPE ROW_COUNT
----- ----------------------------------- ---------- ----------
0 SH.FORMAT_CUSTOMER_NAME Dependency 0
1 "SH"."FORMAT_CUSTOMER_NAME"::8."FOR Result 1
MAT_CUSTOMER_NAME"#940041919019bd46
#1
2 "SH"."FORMAT_CUSTOMER_NAME"::8."FOR Result 1
MAT_CUSTOMER_NAME"#940041919019bd46
#1
3 "SH"."FORMAT_CUSTOMER_NAME"::8."FOR Result 1
MAT_CUSTOMER_NAME"#940041919019bd46
#1
4 rows selected.
Similar to the Query Result Cache objects, this view shows our function result dependencies and separate entries for each set of results. The NAME column is constructed slightly differently to that of the Query Result Cache and we can see the source function name as a dependency. We have three resultsets in the cache for FORMAT_CUSTOMER_NAME, each relating to a given combination of customer first and last name parameters. These cache entries share the same CACHE_ID (they are the same function call) but each have a different CACHE_KEY value to identify a unique invocation of the function (i.e. representing a unique set of parameter values).
pl/sql result cache and explain plan
Note that the use of the Function Result Cache will not appear in execution plans (unlike the Query Result Cache). In the following example, we will explain our previous query.
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'PLSQL_CACHE' 2 FOR 3 SELECT c.cust_id 4 , format_customer_name( 5 c.cust_first_name, c.cust_last_name 6 ) AS cust_name 7 FROM customers c;
Explained.
SQL> SELECT * 2 FROM TABLE(DBMS_XPLAN.DISPLAY(null,'PLSQL_CACHE'));
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- Plan hash value: 2008213504 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 55500 | 1083K| 405 (1)| 00:00:05 | | 1 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 1083K| 405 (1)| 00:00:05 | ------------------------------------------------------------------------------- 8 rows selected.
We have no indication that the Function Result Cache is being used for this query. This is probably to be expected, as the caching directive is in the PL/SQL source. When using the Query Result Cache, however, the CBO is aware of this status and can factor it into its arithmetic and costing mechanisms.
cross-session availability
Remember that one of the key features of the Function Result Cache is that the results are available across sessions. This distinguishes it from our own array-based caching mechanisms. We can demonstrate this behaviour quite simply. In the following example, we will connect to a new session, repeat our original query and examine our counter and the cache statistics. We will begin by starting a new session, initialising our counter and executing the query, as follows.
SQL> conn sh
Enter password: Connected.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT c.cust_id 2 , format_customer_name( 3 c.cust_first_name, c.cust_last_name 4 ) AS cust_name 5 FROM customers c 6 WHERE ROWNUM <= 10;
CUST_ID CUST_NAME
---------- ------------------------------
43278 Benjamin Taylor
25980 Benjamin Taylor
47056 Benjamin Taylor
18337 Abel Aaron
21892 Abel Aaron
25449 Abel Aaron
29004 Abel Aaron
32559 Abel Aaron
37004 Abel Embrey
40559 Abel Embrey
10 rows selected.
Oracle has returned the same rows as before. This means that we should expect a 100% cache hit, despite being in a different session to the one that cached the function results. We will verify this with our counter and the result cache statistics as follows.
SQL> exec counter.show('Function calls');
Function calls: 0 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- --------------- Create Count Success 3 Find Count 17 2 rows selected.
As expected, there were no function executions for this query and the Find Count has incremented by ten, meaning that all results were satisfied by the result cache.
result cache dependencies and protecting cache integrity
Earlier in the article we looked at V$RESULT_CACHE_OBJECTS. In this view we saw the FORMAT_CUSTOMER_NAME function listed as a dependency on itself (V$RESULT_CACHE_OBJECTS.TYPE = 'Dependency'). In addition to this, we have the V$RESULT_CACHE_DEPENDENCY view, described in the background reading.
In our examples so far, we have ignored dependencies, but these are critical to the quality of the results that we retrieve from the cache. There are two ways that Oracle maintains dependencies for the Function Result Cache. Under certain conditions these dependencies will cause a function's set of cached results to be invalidated and we will examine how below.
function recompilation
The first way that Oracle protects the integrity of the cached results is to invalidate them whenever the source function is recompiled. To demonstrate this, we will recompile the FORMAT_CUSTOMER_NAME function and repeat our standard cache tests. We will begin, however, by repeating our standard customer query to check on current cache hits, as follows.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT c.cust_id 2 , format_customer_name( 3 c.cust_first_name, c.cust_last_name 4 ) AS cust_name 5 FROM customers c 6 WHERE ROWNUM <= 10;
CUST_ID CUST_NAME
---------- ------------------------------
43278 Benjamin Taylor
25980 Benjamin Taylor
47056 Benjamin Taylor
18337 Abel Aaron
21892 Abel Aaron
25449 Abel Aaron
29004 Abel Aaron
32559 Abel Aaron
37004 Abel Embrey
40559 Abel Embrey
10 rows selected.
SQL> exec counter.show('Function calls');
Function calls: 0 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- --------------- Create Count Success 3 Find Count 27 2 rows selected.
The output tells us that our current results are still valid and we used the cache for every row. We will now recompile the FORMAT_CUSTOMER_NAME function and repeat the query, as follows.
SQL> ALTER FUNCTION format_customer_name COMPILE;
Function altered.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT c.cust_id 2 , format_customer_name( 3 c.cust_first_name, c.cust_last_name 4 ) AS cust_name 5 FROM customers c 6 WHERE ROWNUM <= 10;
CUST_ID CUST_NAME
---------- ------------------------------
43278 Benjamin Taylor
25980 Benjamin Taylor
47056 Benjamin Taylor
18337 Abel Aaron
21892 Abel Aaron
25449 Abel Aaron
29004 Abel Aaron
32559 Abel Aaron
37004 Abel Embrey
40559 Abel Embrey
10 rows selected.
SQL> exec counter.show('Function calls');
Function calls: 3 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- --------------- Create Count Success 6 Find Count 34 2 rows selected.
The recompilation of FORMAT_CUSTOMER_NAME invalidated our results and Oracle had to re-execute the function three times to reload the cache, as we can see in the above output. The V$RESULT_CACHE_OBJECTS view gives us more information, as follows.
SQL> SELECT id 2 , name 3 , type 4 , status 5 , invalidations 6 FROM v$result_cache_objects 7 ORDER BY 8 id;
ID NAME TYPE STATUS INVALIDATIONS
---------- ----------------------------------- ---------- --------- -------------
0 SH.FORMAT_CUSTOMER_NAME Dependency Published 1
1 "SH"."FORMAT_CUSTOMER_NAME"::8."FOR Result Invalid 0
MAT_CUSTOMER_NAME"#940041919019bd46
#1
2 "SH"."FORMAT_CUSTOMER_NAME"::8."FOR Result Invalid 0
MAT_CUSTOMER_NAME"#940041919019bd46
#1
3 "SH"."FORMAT_CUSTOMER_NAME"::8."FOR Result Invalid 0
MAT_CUSTOMER_NAME"#940041919019bd46
#1
4 "SH"."FORMAT_CUSTOMER_NAME"::8."FOR Result Published 0
MAT_CUSTOMER_NAME"#940041919019bd46
#1
5 "SH"."FORMAT_CUSTOMER_NAME"::8."FOR Result Published 0
MAT_CUSTOMER_NAME"#940041919019bd46
#1
6 "SH"."FORMAT_CUSTOMER_NAME"::8."FOR Result Published 0
MAT_CUSTOMER_NAME"#940041919019bd46
#1
7 rows selected.
Interestingly, the invalidation count is marked against the dependency and not the results, although the STATUS column clearly highlights the invalid results. Following an invalidation, Oracle loads new entries into the result cache, rather than refresh the existing entries, as we can see above. The cache is managed with something similar to an LRU algorithm, so the invalid results will be aged out to make room for new entries as required.
data dependencies
In addition to the DDL dependency above, there are also data dependencies for the Function Result Cache. Many PL/SQL functions query their program data from underlying tables, so when these tables change, so might the function results.
For example, suppose a function returns a value of 'X' from the cache. An update to a table used by this function means that it should now return 'Y'. The cached result for this function is now stale (i.e. it now returns a different answer to the function), but without dependencies in place, Oracle would continue to return 'X' (unless something caused the result to be reloaded, of course).
The way that Oracle enforces data dependencies for the Function Result Cache differs between 11g Release 1 and 11g Release 2. We will examine both below.
the relies_on clause in 11g release 1
In 11g Release 1, Oracle provides the RELIES_ON clause for us to declare a function's dependency on one or more underlying tables or views. If any of the underlying tables are involved in a transaction (committed or uncommitted), the function's cached results will be immediately invalidated. While the transaction is uncommitted, Oracle will continually execute the function to get the results. Once the transaction has been committed, Oracle will revert to the result caching behaviour we have seen so far.
To demonstrate the RELIES_ON clause, we will slightly modify our FORMAT_CUSTOMER_NAME function to query the customer names from the database (rather than receive them as parameters). This will make the function dependant on the CUSTOMERS table data. We will include the CUSTOMERS table name in the RELIES_ON clause, as follows.
SQL> CREATE OR REPLACE FUNCTION format_customer_name ( 2 p_cust_id IN customers.cust_id%TYPE 3 ) RETURN VARCHAR2 4 RESULT_CACHE 5 RELIES_ON (customers) IS 6 7 v_name VARCHAR2(4000); 8 9 BEGIN 10 11 counter.increment(); 12 13 SELECT cust_first_name || ' ' || cust_last_name 14 INTO v_name 15 FROM customers 16 WHERE cust_id = p_cust_id; 17 18 RETURN v_name; 19 20 END format_customer_name; 21 /
Function created.
In using this clause, we have told Oracle that any transactions against CUSTOMERS should invalidate the cached results for the FORMAT_CUSTOMER_NAME function. We will demonstrate this by executing a "no-change" update to the CUSTOMERS table and examining the effect on the result cache. As with our previous examples, we will maintain a counter throughout. We will begin by executing a sample query on the SALES table, using our function to return the customer names, as follows.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT s.cust_id 2 , format_customer_name(s.cust_id) AS cust_name 3 FROM sales s 4 WHERE ROWNUM <= 10;
CUST_ID CUST_NAME
---------- ------------------------------
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
10 rows selected.
By coincidence, the first ten SALES rows that Oracle has returned happen to be for the same customer. From what we know of the Function Result Cache, we should expect a single cache load for these results (followed by nine cache hits). We will verify this below.
SQL> exec counter.show('Function calls');
Function calls: 1 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- --------------- Create Count Success 7 Find Count 43 2 rows selected.
The cache usage is as predicted. The Create Count Success and Find Count have incremented by one and nine, respectively. We will now execute a "no change" update to an unrelated column in the CUSTOMERS table, as follows.
SQL> UPDATE customers 2 SET cust_eff_from = cust_eff_from;
55500 rows updated.
We have not committed this transaction yet, but we will repeat our example query and examine the effects on the result cache.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT s.cust_id 2 , format_customer_name(s.cust_id) AS cust_name 3 FROM sales s 4 WHERE ROWNUM <= 10;
CUST_ID CUST_NAME
---------- ------------------------------
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
10 rows selected.
SQL> exec counter.show('Function calls');
Function calls: 10 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- --------------- Create Count Success 7 Find Count 43 2 rows selected.
While there is an uncommitted transaction against CUSTOMERS, Oracle will not use the result cache for any functions that rely on this table. We can see this clearly above: the function has been executed ten times and the result cache has not been used at all. Remember that none of the CUSTOMERS data changed and neither of the columns we use in FORMAT_CUSTOMER_NAME were involved in the update. As stated, the invalidation of the results are not context sensitive and Oracle will not attempt to understand the nature of our transactions or the depth of the dependencies.
Moving on, we will now commit the transaction and examine the effect on the result cache, as follows.
SQL> COMMIT;
Commit complete.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT s.cust_id 2 , format_customer_name(s.cust_id) AS cust_name 3 FROM sales s 4 WHERE ROWNUM <= 10;
CUST_ID CUST_NAME
---------- ------------------------------
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
10 rows selected.
SQL> exec counter.show('Function calls');
Function calls: 1 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- --------------- Create Count Success 8 Find Count 52 2 rows selected.
Once the transaction is committed, we can see that "normal service" has resumed and the result cache is used as before. The RELIES_ON mechanism is therefore designed to protect us from data modifications that have the potential to change our function results. A side-effect of this is that sometimes we might invalidate perfectly good results, as above. To complete this section, we will see what happens when the customer name data itself is updated, as follows.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT format_customer_name(2) AS cust_name FROM dual;
CUST_NAME ------------------------------ Anne Koch 1 row selected.
SQL> exec counter.show('Function calls');
Function calls: 0 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- --------------- Create Count Success 8 Find Count 53 2 rows selected.
As expected, our result is already in the result cache, so we will now modify the customer's last name, as follows.
SQL> UPDATE customers 2 SET cust_last_name = UPPER(cust_last_name) 3 WHERE cust_id = 2;
1 row updated.
Without committing the transaction, we will re-query the customer name, as follows.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT format_customer_name(2) AS cust_name FROM dual;
CUST_NAME ------------------------------ Anne KOCH 1 row selected.
SQL> exec counter.show('Function calls');
Function calls: 1 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- --------------- Create Count Success 8 Find Count 53 2 rows selected.
The result cache has been bypassed completely, but we have an answer consistent with our data in the CUSTOMERS table. If we didn't use the RELIES_ON clause, Oracle would now be returning incorrect data from the cached FORMAT_CUSTOMER_NAME function results. To complete this example, we will commit the name update and execute the lookup one more time, as follows.
SQL> COMMIT;
Commit complete.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT format_customer_name(2) AS cust_name FROM dual;
CUST_NAME ------------------------------ Anne KOCH 1 row selected.
SQL> exec counter.show('Function calls');
Function calls: 1 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- --------------- Create Count Success 9 Find Count 53 2 rows selected.
Once the transaction is committed, we return to using the result cache. We have reloaded the cache with the result of our single function call, which we can see in the statistics above.
data dependencies in 11g release 2: relies_on clause deprecated
In 11g Release 2, the RELIES_ON clause is deprecated (the RELIES_ON syntax is still valid but Oracle will ignore it). Instead, Oracle manages all data dependencies internally, so we do not need to declare any dependencies between a function and its underlying source data. To demonstrate this, we will re-create the FORMAT_CUSTOMER_NAME function without the RELIES_ON clause on an 11g Release 2 database, as follows.
SQL> CREATE OR REPLACE FUNCTION format_customer_name ( 2 p_cust_id IN customers.cust_id%TYPE 3 ) RETURN VARCHAR2 4 RESULT_CACHE 5 IS 6 7 v_name VARCHAR2(4000); 8 9 BEGIN 10 11 counter.increment(); 12 13 SELECT cust_first_name || ' ' || cust_last_name 14 INTO v_name 15 FROM customers 16 WHERE cust_id = p_cust_id; 17 18 RETURN v_name; 19 20 END format_customer_name; 21 /
Function created.
This function is dependant on the CUSTOMERS table for its data but we have excluded the RELIES_ON clause that would be necessary in 11g Release 1. To demonstrate that Oracle will manage the dependencies itself, we will repeat some of our previous examples below, starting with an initial query to load and use the Function Result Cache below.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT s.cust_id 2 , format_customer_name(s.cust_id) AS cust_name 3 FROM sales s 4 WHERE ROWNUM <= 10;
CUST_ID CUST_NAME
---------- ------------------------------
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
10 rows selected.
SQL> exec counter.show('Function calls');
Function calls: 1 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- -------------------------- Create Count Success 1 Find Count 9 2 rows selected.
By coincidence, the sample resultset is the same in 11g Release 2 and, as before, we have a single cache load and nine subsequent hits. The dependency metadata for our function is as follows.
SQL> SELECT ro.id AS result_cache_id 2 , ro.name AS result_name 3 , do.object_name 4 FROM v$result_cache_objects ro 5 , v$result_cache_dependency rd 6 , dba_objects do 7 WHERE ro.id = rd.result_id 8 AND rd.object_no = do.object_id;
RESULT_CACHE_ID RESULT_NAME OBJECT_NAME
--------------- --------------------------------------------- -------------------------
1 "SH"."FORMAT_CUSTOMER_NAME"::8."FORMAT_CUSTOM CUSTOMERS
ER_NAME"#762ba075453b8b0d #1
1 "SH"."FORMAT_CUSTOMER_NAME"::8."FORMAT_CUSTOM FORMAT_CUSTOMER_NAME
ER_NAME"#762ba075453b8b0d #1
2 rows selected.
Despite the omission of the RELIES_ON clause, Oracle has identified the dependency of these results on the CUSTOMERS table, as we can see above. An interesting feature of this dependency mechanism is the point at which Oracle discovers the dependencies between our function and the table(s) it relies on. Function Result Cache dependencies are discovered after the function has been executed (although object dependencies are still identified at compilation time).
Moving on, we'll now update the CUSTOMERS table, leave the transaction pending and re-execute our sample query to see the effects of this dependency mechanism.
SQL> UPDATE customers 2 SET cust_eff_from = cust_eff_from;
55500 rows updated.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT s.cust_id 2 , format_customer_name(s.cust_id) AS cust_name 3 FROM sales s 4 WHERE ROWNUM <= 10;
CUST_ID CUST_NAME
---------- ------------------------------
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
10 rows selected.
SQL> exec counter.show('Function calls');
Function calls: 10 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- -------------------------- Create Count Success 1 Find Count 9 2 rows selected.
As with our 11g Release 1 tests, our uncommitted transaction on CUSTOMERS means that Oracle will not use the Function Result Cache at all (demonstrated by the 10 function calls above). The difference is that in 11g Release 2, we don't have to specify any dependencies ourselves and so Oracle protects our cache integrity at all times (in 11g Release 1, an omitted RELIES_ON clause would mean that the cache results would risk becoming stale as Oracle would not enforce cache integrity).
Finally, we will commit our outstanding transaction and re-execute our sample query, as follows.
SQL> COMMIT;
Commit complete.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT s.cust_id 2 , format_customer_name(s.cust_id) AS cust_name 3 FROM sales s 4 WHERE ROWNUM <= 10;
CUST_ID CUST_NAME
---------- ------------------------------
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
10 rows selected.
SQL> exec counter.show('Function calls');
Function calls: 1 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- -------------------------- Create Count Success 2 Find Count 18 2 rows selected.
Once the transaction is committed, Oracle uses the cache as expected.
data dependencies in 11g release 2: dynamic sql
We noted earlier that Function Result Cache dependencies are identified when the function is first executed and not when it is compiled. For this reason, the 11g Release 2 dependency discovery will still work when we use dynamic SQL in our functions. We will demonstrate this below by re-creating our FORMAT_CUSTOMER_NAME function without any static references to the CUSTOMERS table.
SQL> CREATE OR REPLACE FUNCTION format_customer_name ( 2 p_cust_id IN NUMBER 3 ) RETURN VARCHAR2 4 RESULT_CACHE 5 IS 6 7 v_name VARCHAR2(4000); 8 9 BEGIN 10 11 counter.increment(); 12 13 EXECUTE IMMEDIATE 14 q'[SELECT cust_first_name || ' ' || cust_last_name 15 FROM customers 16 WHERE cust_id = :bv_cust_id]' 17 INTO v_name 18 USING p_cust_id; 19 20 RETURN v_name; 21 22 END format_customer_name; 23 /
Function created.
We have masked our use of the CUSTOMERS table by removing the anchored type declaration for the parameter and by using dynamic SQL for the query. We therefore have no static dependencies on CUSTOMERS at this stage, which we can confirm as follows.
SQL> SELECT * FROM v$result_cache_dependency;
no rows selected
SQL> SELECT * FROM user_dependencies WHERE referenced_name = 'FORMAT_CUSTOMER_NAME';
no rows selected
We will now execute a sample query against our new function.
SQL> exec counter.initialise();
PL/SQL procedure successfully completed.
SQL> SELECT s.cust_id 2 , format_customer_name(s.cust_id) AS cust_name 3 FROM sales s 4 WHERE ROWNUM <= 10;
CUST_ID CUST_NAME
---------- ------------------------------
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
2 Anne Koch
10 rows selected.
SQL> exec counter.show('Function calls');
Function calls: 1 PL/SQL procedure successfully completed.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- ------------------------------ Create Count Success 1 Find Count 9 2 rows selected.
As usual, the function is executed once to load the cache and we have nine subsequent cache hits. We will check the dependencies metadata for our function's results as follows.
SQL> SELECT ro.id AS result_cache_id 2 , ro.name AS result_name 3 , do.object_name 4 FROM v$result_cache_objects ro 5 , v$result_cache_dependency rd 6 , dba_objects do 7 WHERE ro.id = rd.result_id 8 AND rd.object_no = do.object_id;
RESULT_CACHE_ID RESULT_NAME OBJECT_NAME
--------------- --------------------------------------------- --------------------------
1 "SH"."FORMAT_CUSTOMER_NAME"::8."FORMAT_CUSTOM CUSTOMERS
ER_NAME"#762ba075453b8b0d #1
1 "SH"."FORMAT_CUSTOMER_NAME"::8."FORMAT_CUSTOM FORMAT_CUSTOMER_NAME
ER_NAME"#762ba075453b8b0d #1
2 rows selected.
Despite our use of dynamic SQL and the omission of the RELIES_ON clause, Oracle has identified the results' dependency on the CUSTOMERS table, as we can see above. This means, of course, that the integrity of the function's results is protected in the same way that we saw in our earlier examples of static dependencies.
result cache functions in packages
The examples so far have been as standalone functions, but we tend to use packages in the applications we write. There is an interesting restriction with package specifications that is worth mentioning here. First, we will create a package specification with a function without a RELIES_ON clause, as follows.
SQL> CREATE PACKAGE package_name AS 2 3 FUNCTION result_cache_function ( 4 p_lookup IN INTEGER 5 ) RETURN VARCHAR2 6 RESULT_CACHE; 7 8 END package_name; 9 /
Package created.
There are no surprises here, but if we try to include the RELIES_ON clause, we see something different as follows.
SQL> CREATE OR REPLACE PACKAGE package_name AS 2 3 FUNCTION result_cache_function ( 4 p_lookup IN INTEGER 5 ) RETURN VARCHAR2 6 RESULT_CACHE 7 RELIES_ON (products); 8 9 END package_name; 10 /
Warning: Package created with compilation errors.
SQL> sho err package package_name
Errors for PACKAGE PACKAGE_NAME:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4 PL/SQL: Declaration ignored
3/13 PLS-00999: implementation restriction (may be temporary)
RELIES_ON clause is disallowed on function declaration
We cannot specify the RELIES_ON clause in the function specification. It is declared the package body only.
result cache performance investigations
The Function Result Cache is a key performance enhancement in 11g. For the remainder of this article, we will examine its performance characteristics and attempt to draw some conclusions for when it might or might not be an appropriate tool to use. Our performance tests will fall into one of two broad categories that describe the way we create and use PL/SQL functions in our applications, as follows:
- PL/SQL functions containing SQL; and
- PL/SQL functions with PL/SQL logic (i.e. no SQL).
Within each broad category, we will also distinguish between PL/SQL functions called from SQL statements and those called from within PL/SQL programs.
a note on performance measurement
Our performance tests will use Tom Kyte's Runstats utility and a TIMER package to compare the timings and resource usage of result cache with "traditional" application methods. We will be using either the SH.SALES table (over 900,000 rows), an inflated SH.CUSTOMERS table (over 550,000 rows) or 1,000,000 PL/SQL loop iterations for our tests. We will enable Autotrace for the SQL examples to reduce the screen output and report on I/O statistics. Finally, it is important to note that we will run all SQL testcases twice to mitigate the effects of physical I/O. We will flush the result cache after the first run and report on the second execution only below, validating the "fairness" of the tests with the Autotrace statistics reports.
performance (1): result cache functions with sql lookups
We will begin our performance tests by comparing various methods of looking up reference data from within PL/SQL functions. This is the area that the Function Result Cache is primarily targetting, so we will perform four comparisons as follows.
| Test | Description |
| 1a | Runstats comparison of uncached and result cache lookup functions called from SQL. |
| 1b | Runstats comparison of result cache and user-cached lookup functions called from SQL. |
| 1c | Runstats comparison of result cache lookup function called from SQL and a SQL join. |
| 1d | Timer comparison of uncached, result cache and user-cached lookup functions called in PL/SQL. |
performance (1): test package
For the "SQL in PL/SQL" tests, will create a test package of lookup functions, as follows.
SQL> CREATE PACKAGE cache_test AS 2 3 FUNCTION result_cache_prod_lookup( 4 p_id IN products.prod_id%TYPE 5 ) RETURN products.prod_desc%TYPE 6 RESULT_CACHE; 7 8 FUNCTION user_cache_prod_lookup( 9 p_id IN products.prod_id%TYPE 10 ) RETURN products.prod_desc%TYPE; 11 12 FUNCTION no_cache_prod_lookup( 13 p_id IN products.prod_id%TYPE 14 ) RETURN products.prod_desc%TYPE; 15 16 END cache_test; 17 /
Package created.
The function names are reasonably descriptive. We will be comparing the result cache to PL/SQL functions with either array-based caching or no caching at all. The package body is as follows.
SQL> CREATE PACKAGE BODY cache_test AS 2 3 TYPE prod_cache_aat IS TABLE OF products.prod_desc%TYPE 4 INDEX BY PLS_INTEGER; 5 g_prod_cache prod_cache_aat; 6 7 ------------------------------------------------------- 8 FUNCTION result_cache_prod_lookup( 9 p_id IN products.prod_id%TYPE 10 ) RETURN products.prod_desc%TYPE 11 RESULT_CACHE 12 RELIES_ON (products) IS 13 v_desc products.prod_desc%TYPE; 14 BEGIN 15 SELECT prod_desc INTO v_desc 16 FROM products 17 WHERE prod_id = p_id; 18 RETURN v_desc; 19 END result_cache_prod_lookup; 20 21 ------------------------------------------------------- 22 FUNCTION no_cache_prod_lookup( 23 p_id IN products.prod_id%TYPE 24 ) RETURN products.prod_desc%TYPE IS 25 v_desc products.prod_desc%TYPE; 26 BEGIN 27 SELECT prod_desc INTO v_desc 28 FROM products 29 WHERE prod_id = p_id; 30 RETURN v_desc; 31 END no_cache_prod_lookup; 32 33 ------------------------------------------------------- 34 FUNCTION user_cache_prod_lookup( 35 p_id IN products.prod_id%TYPE 36 ) RETURN products.prod_desc%TYPE IS 37 BEGIN 38 IF NOT g_prod_cache.EXISTS(p_id) THEN 39 SELECT prod_desc INTO g_prod_cache(p_id) 40 FROM products 41 WHERE prod_id = p_id; 42 END IF; 43 RETURN g_prod_cache(p_id); 44 END user_cache_prod_lookup; 45 46 END cache_test; 47 /
Package body created.
These are all standard techniques for looking up reference data from the database. We will now proceed with our first comparison.
comparison 1a: uncached and result cache functions from sql
Our first test will be to compare the performance of the result cache to a PL/SQL lookup function without any caching, called from SQL. We will start the Runstats utility and call the uncached function, as follows.
SQL> set autotrace traceonly statistics SQL> exec runstats_pkg.rs_start();
PL/SQL procedure successfully completed.
SQL> SELECT s.* 2 , cache_test.no_cache_prod_lookup(s.prod_id) AS prod_desc 3 FROM sales s;
918843 rows selected.
Statistics
----------------------------------------------------------
918883 recursive calls
0 db block gets
1841307 consistent gets
0 physical reads
0 redo size
26120009 bytes sent via SQL*Net to client
20623 bytes received via SQL*Net from client
1839 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
918843 rows processed
This is an expensive (but quite common) way of looking up reference data. We can see in the Autotrace report that there are approximately two LIOs per row and an enormous number of recursive calls. We will now run the same SALES query but using the Function Result Cache, as follows.
SQL> exec runstats_pkg.rs_middle();
PL/SQL procedure successfully completed.
SQL> SELECT s.* 2 , cache_test.result_cache_prod_lookup(s.prod_id) AS prod_desc 3 FROM sales s;
918843 rows selected.
Statistics
----------------------------------------------------------
75 recursive calls
0 db block gets
3705 consistent gets
0 physical reads
0 redo size
26120009 bytes sent via SQL*Net to client
20623 bytes received via SQL*Net from client
1839 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
918843 rows processed
We can see a dramatic reduction in the LIOs and recursive SQL for this method. This should translate to a good time saving. We will output the major differences between the two methods with the Runstats report, as follows.
SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 5707 hsecs
Run2 ran in 1528 hsecs
Run1 ran in 373.49% of the time
Name Run1 Run2 Diff
STAT..recursive cpu usage 3,209 5 -3,204
STAT..CPU used by this session 5,204 881 -4,323
STAT..CPU used when call start 5,208 881 -4,327
STAT..DB time 5,214 879 -4,335
LATCH.transaction allocation 9,555 1 -9,554
LATCH.DML lock allocation 42,313 1 -42,312
STAT..session pga memory -786,432 0 786,432
STAT..calls to kcmgrs 918,843 144 -918,699
STAT..index fetch by key 918,844 74 -918,770
STAT..rows fetched via callbac 918,844 74 -918,770
STAT..opened cursors cumulativ 918,856 77 -918,779
STAT..session cursor cache hit 918,855 75 -918,780
STAT..table fetch by rowid 918,856 74 -918,782
STAT..calls to get snapshot sc 918,962 175 -918,787
STAT..execute count 918,865 77 -918,788
STAT..recursive calls 919,853 1,045 -918,808
LATCH.shared pool simulator 922,743 3,813 -918,930
STAT..consistent gets - examin 1,837,709 150 -1,837,559
STAT..consistent gets 1,841,310 3,711 -1,837,599
STAT..consistent gets from cac 1,841,310 3,711 -1,837,599
STAT..session logical reads 1,841,310 3,711 -1,837,599
LATCH.Result Cache: Latch 0 1,837,830 1,837,830
LATCH.cache buffers chains 1,841,775 3,766 -1,838,009
STAT..buffer is not pinned cou 2,756,583 220 -2,756,363
Run1 latches total versus run2 -- difference and pct
Run1 Run2 Diff Pct
2,827,747 1,852,820 -974,927 152.62%
PL/SQL procedure successfully completed.
We can see a lot of differences in the report, most of them generated by the uncached function, which took almost four times as long as the result cache method. With the exception of the new Result Cache: Latch, the Function Result Cache saved us a considerable amount of time and resources (particularly I/O but also some latching). To complete this test, we will look at the result cache statistics, as follows.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- --------------- Create Count Success 80 Find Count 918823 2 rows selected.
We can see that most of our Function Result Cache data was served from the cache. The create count is insignificant compared with the number of hits and the I/O savings are impressive.
comparison 1b: result cache and user-cached functions from sql
Using the same method as the previous test, we will now compare the Function Result Cache with an array-based cache. User-defined array caches are quite common and can provide some good performance gains for PL/SQL programs, but as stated earlier, they are quite difficult to keep consistent with the database and are not shared. We expect, however, to see similar performance to the Function Result Cache, which we will use to begin the test, as follows.
SQL> exec runstats_pkg.rs_start();
PL/SQL procedure successfully completed.
SQL> SELECT s.* 2 , cache_test.result_cache_prod_lookup(s.prod_id) AS prod_desc 3 FROM sales s;
918843 rows selected. << ...autotrace report omitted... >>
The Autotrace report is roughly the same as for the previous result cache example, so has been omitted. We will now repeat the query but using the user-cache.
SQL> exec runstats_pkg.rs_middle();
PL/SQL procedure successfully completed.
SQL> SELECT s.* 2 , cache_test.user_cache_prod_lookup(s.prod_id) AS prod_desc 3 FROM sales s;
918843 rows selected. << ...autotrace report omitted... >>
The Autotrace report shows no significant differences to the Function Result Cache, so it has been omitted. We will now compare the methods with the Runstats report, as follows.
SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1539 hsecs
Run2 ran in 1441 hsecs
Run1 ran in 106.8% of the time
Name Run1 Run2 Diff
LATCH.transaction allocation 9,555 1 -9,554
LATCH.DML lock allocation 42,313 1 -42,312
STAT..session pga memory 196,608 393,216 196,608
STAT..session pga memory max 0 262,144 262,144
LATCH.Result Cache: Latch 1,837,686 0 -1,837,686
Run1 latches total versus run2 -- difference and pct
Run1 Run2 Diff Pct
1,907,206 15,943 -1,891,263 11,962.65%
PL/SQL procedure successfully completed.
We can see that these methods provide similar performance. The user-cache is slightly quicker over the entire resultset and the only major difference between the two methods is the latching incurred by the Function Result Cache. Remember that the user-cache method has incurred over 900,000 function calls, while the result cache method has incurred 0 at this stage. To complete this investigation, we will run our statistics report for the last time in this article, as follows.
SQL> SELECT name, value
2 FROM v$result_cache_statistics
3 WHERE name IN ('Create Count Success','Find Count');
NAME VALUE ---------------------------------------- --------------- Create Count Success 80 Find Count 1837666 2 rows selected.
We incurred no function calls at all with the result cache method, as suspected.
We can deduce from this that the elapsed time of one result cache lookup is roughly equivalent to the elapsed time of the context switching between SQL and PL/SQL to execute a single function call. Despite this, the additional "cost" of the Function Result Cache is probably worth bearing for the benefits it provides (such as cross-session availability and cache consistency).
comparison 1c: result cache function and join from sql
Cached lookups are a performant method for single-row lookups when encapsulating reference tables and we have seen this in the comparisons above. However, the most efficient method of retrieving reference data is usually via a join to the lookup table itself. We will therefore compare the Function Result Cache with a straight join to the PRODUCTS table, starting with the cache method, as follows.
SQL> exec runstats_pkg.rs_start();
PL/SQL procedure successfully completed.
SQL> SELECT s.* 2 , cache_test.result_cache_prod_lookup(s.prod_id) AS prod_desc 3 FROM sales s;
918843 rows selected. << ...autotrace report omitted... >>
Again, we have just over 3,500 consistent gets using the Function Result Cache for this query, so the Autotrace report has been omitted. We will now change the SALES query to join directly to PRODUCTS to avoid any PL/SQL function usage, as follows.
SQL> exec runstats_pkg.rs_middle();
PL/SQL procedure successfully completed.
SQL> SELECT s.* 2 , p.prod_desc 3 FROM sales s 4 , products p 5 WHERE s.prod_id = p.prod_id;
918843 rows selected.
Statistics
----------------------------------------------------------
40 recursive calls
0 db block gets
3577 consistent gets
2 physical reads
0 redo size
26120009 bytes sent via SQL*Net to client
20623 bytes received via SQL*Net from client
1839 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
918843 rows processed
The I/O resources used for this query are similar to the Function Result Cache method. We will output the timings and resource usage with the Runstats report, as follows.
SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1536 hsecs
Run2 ran in 844 hsecs
Run1 ran in 181.99% of the time
Name Run1 Run2 Diff
LATCH.shared pool simulator 5,532 49 -5,483
STAT..session uga memory 7,488 0 -7,488
STAT..session uga memory max 0 7,488 7,488
STAT..physical read bytes 0 16,384 16,384
STAT..physical read total byte 0 16,384 16,384
STAT..session pga memory 65,536 131,072 65,536
LATCH.Result Cache: Latch 1,837,686 0 -1,837,686
Run1 latches total versus run2 -- difference and pct
Run1 Run2 Diff Pct
1,852,610 9,747 -1,842,863 19,006.98%
PL/SQL procedure successfully completed.
The SQL join method is significantly faster (almost twice as fast) and incurs much less latching. The Result Cache: Latch statistic is again the biggest difference. We can conclude therefore that the general advice has not changed in 11g: we should continue to use SQL to join directly to our reference tables if we demand performance over encapsulation.
comparison 1d: uncached, result cache and user-cached functions from pl/sql
Our first three tests have compared the performance of lookups from SQL. We will now compare the timings of our three PL/SQL functions from within PL/SQL itself, again using the SALES table as the source of our lookup keys. This is broadly equivalent to many PL/SQL load routines that fetch data then use numerous function calls to transform it according to local business rules. Note that we will only report "wall-clock" timings below for convenience.
SQL> DECLARE
2
3 v_desc products.prod_desc%TYPE;
4
5 BEGIN
6
7 timer.snap();
8 FOR r IN (SELECT s.prod_id FROM sales s) LOOP
9 v_desc := cache_test.no_cache_prod_lookup(r.prod_id);
10 END LOOP;
11 timer.show('Not Cached ');
12
13 timer.snap();
14 FOR r IN (SELECT s.prod_id FROM sales s) LOOP
15 v_desc := cache_test.user_cache_prod_lookup(r.prod_id);
16 END LOOP;
17 timer.show('User Cache ');
18
19 timer.snap();
20 FOR r IN (SELECT s.prod_id FROM sales s) LOOP
21 v_desc := cache_test.result_cache_prod_lookup(r.prod_id);
22 END LOOP;
23 timer.show('Result Cache');
24
25 END LOOP;
26 /
[Not Cached ] 42.91 seconds [User Cache ] 1.78 seconds [Result Cache] 2.16 seconds PL/SQL procedure successfully completed.
These timings highlight some interesting facts. First, the caching methods are significantly faster than the uncached method (as expected) but are roughly comparable to each other. As with our previous SQL example, the array-cache method is slightly quicker overall. Second, the Function Result Cache appears to be much faster when used from within PL/SQL, but this is largely due to the massive reduction in SQL*Net traffic generated by the PL/SQL fetches (there is no client as such in this example, unlike the SQL examples above). We have also used a smaller column projection (of just one column) in the PL/SQL code.
We can conclude from these results that the same general principles apply to the performance of PL/SQL functions under different caching scenarios, regardless of whether they are called from SQL or PL/SQL code.
performance (2): result cache functions with pl/sql logic
So far we have looked at the result cache performance for PL/SQL functions with embedded SQL lookups. For the remaining performance investigations, we will concentrate on PL/SQL functions comprising PL/SQL-only logic (i.e. no SQL statements). Our tests are listed below and include function calls from SQL and PL/SQL as before. These will enable us to determine whether the practice of encapsulating business rules in PL/SQL functions is enhanced by the Function Result Cache.
| Test | Description |
| 2a | Runstats comparison of uncached and result cache PL/SQL-only functions called from SQL. |
| 2b | Runstats comparison of result cache function and inlined business logic called from SQL. |
| 2c | Timer comparison of PL/SQL-only function calls (uncached and result cache) with inlined logic in PL/SQL. |
performance test package (2)
For the "PL/SQL-logic-only" comparisons, will create a test package of two business rule functions: one using the result cache and one without any caching. We will return to a couple of variations on our FORMAT_CUSTOMER_NAME function from earlier in the article, as follows.
SQL> CREATE OR REPLACE PACKAGE cache_test AS 2 3 FUNCTION result_cache_cust_name( 4 p_first_name IN VARCHAR2, 5 p_last_name IN VARCHAR2 6 ) RETURN VARCHAR2 7 RESULT_CACHE; 8 9 FUNCTION no_cache_cust_name( 10 p_first_name IN VARCHAR2, 11 p_last_name IN VARCHAR2 12 ) RETURN VARCHAR2; 13 14 END cache_test; 15 /
Package created.
The functions will construct a customer name from the supplied parameters. We create the package body as follows.
SQL> CREATE OR REPLACE PACKAGE BODY cache_test AS 2 3 ------------------------------------------------------- 4 FUNCTION result_cache_cust_name( 5 p_first_name IN VARCHAR2, 6 p_last_name IN VARCHAR2 7 ) RETURN VARCHAR2 8 RESULT_CACHE 9 RELIES_ON (customers) IS 10 BEGIN 11 RETURN p_first_name || ' ' || p_last_name; 12 END result_cache_cust_name; 13 14 ------------------------------------------------------- 15 FUNCTION no_cache_cust_name( 16 p_first_name IN VARCHAR2, 17 p_last_name IN VARCHAR2 18 ) RETURN VARCHAR2 IS 19 BEGIN 20 RETURN p_first_name || ' ' || p_last_name; 21 END no_cache_cust_name; 22 23 END cache_test; 24 /
Package body created.
comparison 2a: uncached and result cache functions from sql
We will begin by using Runstats to compare the two packaged functions when called from a large CUSTOMERS dataset of over 550,000 rows. We will use a Cartesian Product to inflate the resultset, starting with the uncached function as follows.
SQL> set autotrace traceonly statistics SQL> exec runstats_pkg.rs_start();
PL/SQL procedure successfully completed.
SQL> SELECT c.* 2 , cache_test.no_cache_cust_name( 3 c.cust_first_name, c.cust_last_name 4 ) AS cust_name 5 FROM customers c 6 , (SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 10) x;
555000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1477 consistent gets
0 physical reads
0 redo size
61095910 bytes sent via SQL*Net to client
12615 bytes received via SQL*Net from client
1111 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
555000 rows processed
We will now run a similar SQL statement but using the result cached version of the PL/SQL function, as follows.
SQL> exec runstats_pkg.rs_middle();
PL/SQL procedure successfully completed.
SQL> SELECT c.* 2 , cache_test.result_cache_cust_name( 3 c.cust_first_name, c.cust_last_name 4 ) AS cust_name 5 FROM customers c 6 , (SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 10) x;
555000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1477 consistent gets
0 physical reads
0 redo size
61095910 bytes sent via SQL*Net to client
12615 bytes received via SQL*Net from client
1111 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
555000 rows processed
We can see that the resource usage is the same. We will output the Runstats report to provide more detail, as follows.
SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1223 hsecs
Run2 ran in 1353 hsecs
Run1 ran in 90.39% of the time
Name Run1 Run2 Diff
STAT..session uga memory 7,488 0 -7,488
STAT..session pga memory 65,536 0 -65,536
STAT..session uga memory max 130,928 0 -130,928
LATCH.Result Cache: Latch 0 1,110,000 1,110,000
Run1 latches total versus run2 -- difference and pct
Run1 Run2 Diff Pct
7,665 1,117,709 1,110,044 .69%
PL/SQL procedure successfully completed.
This is an interesting result. The uncached PL/SQL-logic function is quicker to use from SQL than the result cached version. This means, therefore, that the internal Function Result Cache mechanism (primarily the latching and the lookup) is slower than general SQL->PL/SQL context switching (something we are constantly trying to reduce). Business rule encapsulation has not received any boost from the new feature in this respect.
comparison 2b: result cache function and inlined logic from sql
Our second test in this category will compare the Function Result Cache with inlined logic in SQL (we might refer to this as "hard-coded" logic). We will begin with the result cache method, as follows.
SQL> exec runstats_pkg.rs_start();
PL/SQL procedure successfully completed.
SQL> SELECT c.* 2 , cache_test.result_cache_cust_name( 3 c.cust_first_name, c.cust_last_name 4 ) AS cust_name 5 FROM customers c 6 , (SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 10) x;
555000 rows selected. << ...autotrace report omitted... >>
There are no suprises in the Autotrace report so it has been omitted. We will now run the SQL statement with inlined logic, as follows.
SQL> exec runstats_pkg.rs_middle();
PL/SQL procedure successfully completed.
SQL> SELECT c.* 2 , c.cust_first_name || ' ' || c.cust_last_name AS cust_name 3 FROM customers c 4 , (SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 10) x;
555000 rows selected. << ...autotrace report omitted... >>
Again, there are no differences in the Autotrace report. We will complete the test by running the Runstats report, as follows.
SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1361 hsecs
Run2 ran in 850 hsecs
Run1 ran in 160.12% of the time
Name Run1 Run2 Diff
LATCH.shared pool simulator 2,254 27 -2,227
STAT..session uga memory 7,488 0 -7,488
STAT..session pga memory 65,536 131,072 65,536
STAT..session pga memory max 0 131,072 131,072
LATCH.Result Cache: Latch 1,110,000 0 -1,110,000
Run1 latches total versus run2 -- difference and pct
Run1 Run2 Diff Pct
1,118,370 5,318 -1,113,052 21,029.90%
PL/SQL procedure successfully completed.
The inlined-logic method is considerably faster than any method that uses PL/SQL functions. We can see this in the timings for both this test and the previous test. Unfortunately, the Function Result Cache mechanism alone does not mitigate the costs associated with PL/SQL encapsulation: it simply cannot match the performance of SQL.
comparison 2c: uncached function, result cache function and inlined logic in pl/sql
To complete our performance investigations, we will compare the timings of the uncached, result cached and inlined methods from within PL/SQL. We will not be executing any SQL in the timed tests. In the following PL/SQL block, we will fetch 1,000 customers up-front, then perform 1,000,000 iterations for each method, using the "wall-clock" as a proxy measure of performance.
SQL> DECLARE
2
3 v_cust_name VARCHAR2(4000);
4 v_first_names DBMS_SQL.VARCHAR2_TABLE;
5 v_last_names DBMS_SQL.VARCHAR2_TABLE;
6 v_loop PLS_INTEGER := 1000;
7
8 BEGIN
9
10 SELECT cust_first_name, cust_last_name
11 BULK COLLECT INTO v_first_names, v_last_names
12 FROM customers
13 WHERE ROWNUM <= 1000;
14
15 timer.snap();
16 FOR i IN 1 .. v_first_names.COUNT LOOP
17 FOR ii IN 1 .. v_loop LOOP
18 v_cust_name := cache_test.no_cache_cust_name(
19 v_first_names(i), v_last_names(i)
20 );
21 END LOOP;
22 END LOOP;
23 timer.show('Not Cached ');
24
25 timer.snap();
26 FOR i IN 1 .. v_first_names.COUNT LOOP
27 FOR ii IN 1 .. v_loop LOOP
28 v_cust_name := cache_test.result_cache_cust_name(
29 v_first_names(i), v_last_names(i)
30 );
31 END LOOP;
32 END LOOP;
33 timer.show('Result Cache ');
34
35 timer.snap();
36 FOR i IN 1 .. v_first_names.COUNT LOOP
37 FOR ii IN 1 .. v_loop LOOP
38 v_cust_name := v_first_names(i) || ' ' || v_last_names(i);
39 END LOOP;
40 END LOOP;
41 timer.show('Inlined ');
42
43 END LOOP;
44 /
[Not Cached ] 0.69 seconds [Result Cache ] 1.68 seconds [Inlined ] 0.24 seconds PL/SQL procedure successfully completed.
We can see clearly that we are penalised for using the Function Result Cache. The inlined logic is considerably quicker than any method involving function calls (subprogram inlining is a new feature of the PL/SQL optimising compiler in 11g), but the uncached version is over twice as fast as the result cached function.
conclusions
In this article we have examined the Function Result Cache mechanism and compared its performance to common alternatives. From the examples and performance results, we can conclude the following usage guidelines:
- we can achieve the best performance gains by converting functions that contain SQL lookups to use the result cache. The greater the function's workload, the greater the performance benefit when using the cache;
- result cache lookups are no faster than PL/SQL function calls or context switches. If the function's workload is trivial (and does not involve SQL), then it is not worth converting the function to use the result cache;
- for trivial PL/SQL-only functions used within PL/SQL programs, subprogram inlining is a better performance feature to use. We should only consider the Function Result Cache when the time it takes to process the function's logic is longer than the time spent in the result cache mechanism itself;
- associative array caches are slightly faster to access than the Function Result Cache. However, the benefits of the Function Result Cache (such as one instance of the results, cross-session availability, Oracle-managed SGA memory and protected data integrity) far outweigh the minor time savings we can achieve by coding our own array caches.
further reading
For more information on the Cross-Session PL/SQL Function Result Cache, particularly its restrictions, read the PL/SQL Language Reference. For more links, including several blogs on the Result Cache Latch by Pythian, see this section of the background reading referenced earlier. The utilities used in this article (Timer, Counter and a version of Tom Kyte's Runstats) are available on the Utilities page of oracle-developer.net.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, January 2008 (updated June 2010)
Back to Top