Oracle Database 11g: PLSQL and OCI Result Set Caching
August 27, 2008
Synopsis. Oracle Database 11gR1 offers several new performance enhancements that limit round trips between database server, application servers, and applications when the identical data is required to answer queries, provide lookup data, or return deterministic values from a function. The final article in this series explores how the latest release of Oracle extends the ability to cache result sets to applications and PL/SQL functions.
In the prior article in this series, I demonstrated how Oracle SQL queries can cache result sets to limit the number of round trips to the database server, thereby increasing overall database throughput by reducing the frequency at which data needs to be refreshed in database memory. But wait theres more! Oracle Database 11g also offers the capability to cache result sets on behalf of deterministic PL/SQL functions as well as client application sessions.
PL/SQL Function Result Set Caching
A PL/SQL function can take advantage of result set caching features - especially useful when the function is querying data in tables that rarely changes. For example, just about every OLTP application Ive supported over the past three decades needs to obtain a list of geographic or location information from the database to support accurate data entry of post office addresses (e.g. ZIP+4 codes, state abbreviations, or country codes). And most custom-written financial applications also need to calculate complex values that are specific (and usually proprietary or top-secret) to the application itself.
To illustrate this with a practical example, Ive created table AP.CURRENCY_CONVERSIONS and populated it via the code shown in Listing 2.1. As its name indicates, this table contains currency exchange rates for six of the more common world currencies. To quickly return the most recent exchange rate for a pair of currencies as of a specific date, Ive constructed a PL/SQL function named AP.CONVERTED_AMOUNT as shown in Listing 2.2. I implemented PL/SQL function result set caching for the function by including the AP.CURRENCY_CONVERSIONS table in the RESULT_CACHE RELIES_ON directive after its RETURN declaration.
Next, I flushed the entire result cache and then populated the functions corresponding result cache by invoking a query that calls it to return the conversion amount for three pairs of currencies. Listing 2.3 shows the output from that query, as well as whats stored within the corresponding result cache for the three values.
PL/SQL Function Cache Invalidation. What happens when the contents of table AP.CURRENCY_CONVERSIONS changes? Ive demonstrated this by adding a few additional rows to the table and then querying view V$RESULT_CACHE_OBJECTS again. Note that the three values originally cached are now marked as INVALID; this means that the next execution of the PL/SQL function will result in a refresh of the cached result set. Finally, I applied some updates to three existing rows, and obtained the correct values after the cache was once again refreshed because of the changes to table AP.CURRENCY_CONVERSIONS. Ive shown the results from the actual queries and the resulting invalidation and republishing of the cached values in Listing 2.4.
Caveats. Even a feature as powerful as this one does have limits, however. If any of the conditions below exist, then PL/SQL function caching will be disabled automatically:
OCI Result Set Caching
Applications that connect to an Oracle 11gR1 database through the Oracle Call Interface (OCI) client software can also cache commonly-used result sets. Unlike SQL query results caches, however, the cached data is held within client memory. Client query caches only need to be refreshed whenever data thats part of the result set cached on the client is changed on the database server. When this happens, the client will automatically request an immediate refresh of the affected cache(s).
The net result of this feature is that round trips from the client to the server are kept to a bare minimum, and this means that the query that constructs the result set on the client doesnt have to be sent, parsed, executed, and then fetched. Database server throughput is also improved, and this also means that the client application may dramatically improve in scalability. Applications that are potentially good candidates for this feature would leverage repeatable result sets, especially small and relatively static results like lookup tables, or frequently executed queries that return the same results.
Finally, note that client result set caching doesnt require enabling server-side result caching, and vice versa. This means that they can be enabled independently and on a per-application basis.
Activating Client-Side Query Cache. Setting up client-side query cache features involves setting a few simple parameters in the databases initialization parameter file:
Overriding Database Settings at the Client Level. Three additional OCI parameters can be set up at the client machine itself to override those set at the database level settings. These parameters are listed in the client machines SQLNET.ORA file:
Viewing Active Client-Side Query Cache Metadata. View V$CLIENT_RESULT_CACHE_STATS contains information about all application client query result caches, while another view, CLIENT_RESULT_CACHE_STATS$, provides statistics about how many client result set caches exist and how often the client application refreshed a client result set cache. Listing 2.5 shows sample queries against these views.
Oracle Database 11gs ability to cache result sets for SQL queries, PL/SQL functions, and client applications offers an unprecedented opportunity to increase database throughput and application scalability without any significant impact on database performance. These features are simple to set up and monitor; moreover, since the DBMS itself makes the determination as to when a result cache needs to be refreshed, DBA involvement in the data refresh process is minimized.
References and Additional Reading
While Im hopeful that Ive given you a thorough grounding in the technical aspects of the features Ive discussed in this article, Im also sure that there may be better documentation available since its been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that Ive drawn upon the following Oracle Database 11gR1 documentation for the deeper technical details of this article:
B28274-01 Oracle Database 11gR1 Performance Tuning Guide
B28279-02 Oracle Database 11gR1 New Features Guide
B28320-01 Oracle Database 11gR1 Reference Guide
B28395-03 Oracle Database 11gR1 Call Interface Programmers Guide
B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types Reference
Also, the following MetaLink documents help clarify this feature set:
430887.1 11g New Feature: PL/SQL Function Result Cache
563828.1 Result Cache Could Not Be Enabled