Learn Tips and Best Practices from the Oracle JDBC Development

  • October 1, 2018

Boost the Java apps performance through Client Side ResultSet Cache

Nirmala Sundarappa
Principal Product Manager

What is Client Side ResultSet Cache?

Java applications can reuse the result sets of frequent/identical queries, cached in the driver's memory on the client side. This feature improves the performance by saving network roundtrips to the database and saving server-side resources as the query results are cached on the client side and not re-executed with every invocation. Also, the cache is shared across multiple connections with the same attributes (same user, same session settings, etc.,).

What happens to the cache if the data changes on the server side?

Client Side ResultSet Cache feature includes a sophisticated cache invalidation mechanism making sure that results of the query are in sync with the database. A cached result is automatically invalidated whenever the data changes on the server or when the session attributes change or when a local transaction that affects the data is started. 

How do you enable Client Side ResultSet Cache?

Though, the client side resultset cache is enabled by default in JDBC Thin driver version 18.3, it does require some settings on the server side for it take affect. Set a few parameters related to cache size (memory that you want to allocate for the cache) and cache lag (maximum time in milliseconds before the JDBC driver makes a round trip to get any database changes related to the query) on the server side to enable this feature. Steps 1-2 show the required settings. Refer to server side documention for using Client Result Cache for more details.
If you want to disable this feature then, set oracle.jdbc.enableQueryResultCache to false without the need to remove any SQL hints from the queries.This can be set either as a connection property or as a system property. Refer to JDBC Developer's guide for more details on this feature.

Step 1: Make sure that the database server is configured to support resultset caching. Add the following configurations in init.ora and restart the database server.

CLIENT_RESULT_CACHE_SIZE =  100MB -  Set to client result set cache size
CLIENT_RESULT_CACHE_LAG = 1000 - Specifies the max time in milliseconds that the cache can lag

Step 2: Add SQL hints to cache the result set for the required queries. SQL hints can be present at three levels. Query level has the highest precedence, later the table annotations and then the server initialization parameter.
  • Query Level :  Enable or Disable client result cache for a single query using the SQL hints /*+ result_cache */ or /*+ no_result_cache */. This is the recommended way to identify the queries in the applications that are worth being cached.
    SELECT /*+ result_cache */ first_name, last_name from employees where 
    employee_id < 150
  • Table Annotation: You can also identify the read-mostly tables on the server using annotations too. You can use the RESULT_CACHE  MODE (FORCE) clause to enable the client side result set cache while creating or altering the table. To disable it, use RESULT_CACHE MODE (DEFAULT)
    ALTER TABLE dept result_cache (MODE FORCE);
  • Server side:  The session parameter RESULT_CACHE_MODE = (FORCE or MANUAL), enables or disables client side resultset cache for all queries for the database session.  This parameter must be added in init.ora. The recommendation is to use MANUAL.

How to check if the Client Side ResultSet Cache is enabled and working ?

Use the method below to find if the query results are retrieved from the cache or from the database.

try (ResultSet rs = statement.executeQuery("select /*+ result_cache */ empno, ename from emp")) {
  System.out.println("isFromResultSetCache:" + rs.isFromResultSetCache());                             





Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.