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 … there’s 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 I’ve 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, I’ve 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, I’ve 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 function’s 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 what’s 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? I’ve 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. I’ve 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:

• The PL/SQL function has been defined in a package that uses invoker rights.
• The PL/SQL function is defined within an anonymous PL/SQL block.
• The PL/SQL function is defined with either OUT or IN OUT parameters.
• One of the PL/SQL function’s IN arguments used a datatype of either BLOB, CLOB, NCLOB, or REF CURSOR. Likewise, the PL/SQL function’s IN arguments cannot define a record, collection, or object that uses one of these datatypes.
• Finally, the PL/SQL function is not permitted to return a type of BLOB, CLOB, NCLOB, or REF CURSOR, nor may it return a record, collection, or object that uses one of these datatypes.

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 that’s 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 doesn’t 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 doesn’t 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 database’s initialization parameter file:

• CLIENT_RESULT_CACHE_SIZE defines the size of the client result cache for each OCI client process.
• CLIENT_RESULT_CACHE_LAG determines the maximum number of milliseconds before the OCI client query should execute a round trip to capture any data changes that might be applied to the existing cached client queries.

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 machine’s SQLNET.ORA file:

• When a value for OCI_RESULT_CACHE_MAX_SIZE is set at the client level, it overrides the maximum size of each client’s result cache that’s set via CLIENT_RESULT_CACHE_SIZE.
• Two other parameters tell Oracle 11g how to set the maximum size of the result cache for any single client process. Parameter OCI_RESULT_CACHE_MAX_RSET_SIZE specifies the size in bytes; likewise, parameter OCI_RESULT_CACHE_MAX_RSET_ROWS limits the size based on the number of rows cached.

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.

Conclusion

Oracle Database 11g’s 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 I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s 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 I’ve 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 Programmer’s 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

Previous

Back to DBAsupport.com