The RESULT_CACHE Option

As of Oracle Database 11g, the function result cache has entered the caching fray. It offers the benefits of just-in-time package-level caching (and more!) but without the hassle. All you have to do is add the RESULT_CACHE option to the function declaration section and that’s it. It couldn’t be much easier!

The function result cache is ideal for data from tables that are queried from more frequently than they are modified, for instance lookup tables and materialized views (between refreshes). When a table’s data changes every few seconds or so, the function result cache may hurt performance as Oracle needs to fill and clear the cache before the data can be used many times. On the other hand, when a table (or materialized view) changes, say, every 10 minutes or more, but it is queried from hundreds or even thousands of times in the meantime, it can be beneficial to cache the data with the RESULT_CACHE option. Recursive functions, small lookup functions, and user-defined functions that are called repeatedly but do not fetch data from the database are also ideal candidates for the function result cache.

With Oracle Database 11gR2, the RELIES ON clause is deprecated, which means that you don’t even have to list the dependencies: Oracle will figure everything out for you!

The database does not cache SQL statements contained in your function. It ‘merely’ caches input values and the corresponding data from the RETURN clause.

Oracle manages the function result cache in the SGA. In the background. Whenever changes are committed to tables that the cache relies on, Oracle automatically invalidates the cache. Subsequent calls to the function cause the cache to be repopulated. Analogously, Oracle ages out cached results whenever the system needs more memory, so you, the database developer, are completely relieved of the burden of designing, developing, and managing the cache.

Since the function result cache is in the SGA rather than the PGA, it is somewhat slower than PGA-based caching. However, if you have hidden SELECT statements in functions, the SGA lookups thanks to the function result cache beat any non-cached solutions with context switches hands down.

Sounds too good to be true?

It is.

First, the function result cache only applies to stored functions not functions defined in the declaration section of anonymous blocks. Second, the function cannot be a pipelined table function. Third, the function cannot query from data dictionary views, temporary tables, SYS-owned tables, sequences, or call any non-deterministic PL/SQL function. Moreover, pseudo-columns (e.g. LEVEL and ROWNUM) are prohibited as are SYSDATE and similar time, context, language (NLS), or GUID functions. The function has to be free of side effects, that is, it can only have IN parameters; IN OUT and OUT parameters are not allowed. Finally, IN parameters cannot be a LOB, REF CURSOR, collection, object type, or record. The RETURN type can likewise be none of the following: LOB, REF CURSOR, an object type, or a record or collection that contains a LOB, REF CURSOR, and/or an object type.

The time to look up data from the function result cache is on par with a context switch or a function call. So, if a PL/SQL function is almost trivial and called from SQL, for instance a simple concatenation of first_name and last_name, then the function result cache solution may be slower than the same uncached function.

Inlining, or rather hard coding, of simple business rules seems to be even faster as demonstrated by Adrian Billington, although we hopefully all agree that hard coding is a bad practice, so we shall not dwell on these results and pretend they never existed.

Beware that the execution plan of a SQL statement does not inform you that a function result cache can or even will be used in clear contrast to the query result cache. The reason is both simple and obvious: RESULT_CACHE is a PL/SQL directive and thus not known to the SQL engine.

Latches

The result cache is protected by a single latch, the so-called result cache (RC) latch. Since latches are serialization devices, they typically stand in the way of scalability, especially in environments with a high degree of concurrency, such as OLTP applications.

Because there is only one latch on the result cache, only one session can effectively create fresh result cache entries at any given moment. A high rate of simultaneous changes to the result cache are therefore detrimental to the performance of a database. Similarly, setting the parameter RESULT_CACHE_MODE to FORCE is a guarantee to bring a database to its knees, as every single SQL statement will be blocking the RC latch.

Scalability issues have dramatically improved from 11gR1 to 11gR2, but latch contention still remains an issue when rapidly creating result sets in the cache.

It should be clear that the function result cache only makes sense for relatively small result sets, expensive SQL statements that do not experience high rates of concurrent execution, and SQL code that is against relatively static tables.

IR vs DR Units

The default mode of PL/SQL units is to run with the definer’s rights (DR). Such units can benefit from the function result cache without further ado. Invoker’s rights (IR) subprograms, created with the AUTHID CURRENT_USER rather than AUTHID DEFINER, cannot use the function result cache, and an attempt at compilation leads to a PLS-00999 error, at least prior to DB12c. The reason is that a user would have been able to retrieve data cached by another user, to which the person who originally requested the data should not have access because its privileges are not sufficient.

This restriction has been lifted with 12c, and the security implications have been resolved. The solution to the security conundrum is that the function result cache is per user for IR units. This means of course that the RESULT_CACHE option is only useful for functions that the same user calls many times with the same input values.

Memory Consumption

That’s all very nice, but how much memory does the function result cache gobble up?

A DBA can run EXEC DBMS_RESULT_CACHE.MEMORY_REPORT(detailed => true) to see detailed information about the memory consumption. However, the purpose of these pages is to help fellow developers to learn about optimization techniques, which means that DBMS_RESULT_CACHE is out of the question.

You can check the UGA and PGA memory consumption by looking at the data for your session from the following query:

1SELECT
2  *
3FROM
4  v$sesstat
5NATURAL JOIN
6  v$statname
7;

You can provide the name of the statistic you’re interested in. A full list of statistics can be found in the official documentation. For example, 'session uga memory' or 'session pga memory'. These are current values, so you’d check the metrics before and after you run your function a couple of times to see the PGA and UGA memory consumption of your function. Obviously, there will be no (or very little) PGA consumption in the case of the function result cache.

There are also several solutions available that calculate the various statistics for you. They typically work by checking the metrics before running a function several times, then run the function, after which they check the metrics again.

In case you need help configuring the function result cache, here’s a helping hand.