As Published In July/August 2008

TAGS dotnet, All

By Mark A. Williams

Put performance in your pocket with the client result cache.

Oracle Data Provider for .NET (ODP.NET) enables you to take advantage of features exposed by the underlying Oracle Call Interface. One such feature new in Oracle Database 11g Release 1 and Oracle Database 11g Release 1 Client is the client result cache.

The client result cache is a portion of memory that is automatically allocated (on the client) to cache query results. Caching query results on the client makes it possible to avoid round-trips to the server to execute, query, and fetch data. This, in turn, leads to better performance and decreased resource consumption on the network and the server.

Note that separate processes do not share a client result cache—there is one cache per client process. However, within a single client process, sessions with the same user settings can share the same client result cache.

This column shows you how to use the client result cache to increase returns on your query performance in ODP.NET 11g client applications.

A Consistent Picture

The client result cache is automatically kept consistent with the database. When a change occurs in the database that affects or invalidates the results in the client result cache, the Oracle Call Interface client receives a notification of the change.

Entries in the cache are managed by a least recently used (LRU) algorithm, and they do not expire. This means that entries in the cache can be removed when space is needed to hold new entries, but in the absence of such space pressure, entries can remain in the cache for as long as the cache exists.

The characteristics and behavior of the client result cache are controlled by three server-side parameters:

CLIENT_RESULT_CACHE_SIZE. Specifies (in bytes) the maximum size of the client result cache. The default value is 0 (zero), and the parameter must be set to a value greater than 32K to enable the client result cache.

CLIENT_RESULT_CACHE_LAG. Specifies (in milliseconds) the maximum amount of time the client result cache can lag behind potential server-side changes that would affect the result set. The default lag value is 3,000 milliseconds (3 seconds).

RESULT_CACHE_MODE. Specifies whether result set caching happens automatically or on demand. The default value of this parameter is MANUAL, indicating that caching occurs when requested by the client.

Note that each of these parameters is static, so after making changes to their values, you must restart the database instance. I recommend reviewing the Oracle Call Interface Programmer's Guide, 11g Release 1 (11.1) documentation for additional details on the above parameters, cache monitoring statistics, and supported query types.

Client Contributions

One of the big benefits of the client result cache is that it requires no changes to your .NET application code. To use the client result cache, however, you need to make sure that two conditions are satisfied on the client.

First, statement caching must be enabled in the client application. Fortunately, this is the default mode with ODP.NET 11g, so it is likely that you will not need to take any action to enable statement caching. If you have disabled statement caching, you can enable it either in the registry or via the database connection string in your code. See the Oracle Data Provider for .NET Developer's Guide for further details on statement caching.

Next, if the RESULT_CACHE_MODE server parameter is set to its default value of MANUAL, you must add a hint to your SQL statement. A hint "directs" Oracle Database to perform an operation in a specific manner. The client result cache hint is named result_cache, and it would follow the SELECT keyword in a SQL statement:

select /*+ result_cache */ column1, column2, ...

Reaping the Returns

The Sample code for this column uses the EMPLOYEES table in the HR sample schema, which ships as part of Oracle Database. To successfully execute the code, you will need the following:

Next Steps READ more about ODP.NET Oracle Data Provider for .NET Developer's Guide DOWNLOAD the sample code for this column VISIT the .NET Developer Center

• Microsoft Visual Studio 2005 or Visual Studio 2008 (any edition)
• Oracle Database 11g Release 1
• Oracle Database Client 11g Release 1
• Oracle Data Provider for .NET 11.1.6.2.x

After downloading the zip file, extract it to a directory and adjust the connection string in the Program.cs file (shown in Listing 1) as noted in the source code comments.

Code Listing 1: Program.cs

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace JulAug2008
{
  class Program
  {
    static void Main(string[] args)
    {
      // be sure to change for your environment
      string constr = "User Id=hr;" +
                      "Password=hr;" +
                      "Data Source=orademo;" +
                      "Enlist=false;" +
                      "Pooling=false";

      // used to track execution duration
      DateTime timeStart;
      DateTime timeEnd;
      double totalSeconds1;
      double totalSeconds2;

      // create and open connection
      OracleConnection con = new OracleConnection();
      con.ConnectionString = constr;
      con.Open();

      // create command object to use for the select statement
      OracleCommand cmd = con.CreateCommand();

      // used to read results
      OracleDataReader dr;

      // simple select on employee table will be executed repeatedly and timed
      // this statement will not use the client-side cache
      cmd.CommandText = "select first_name, last_name from employees";

      // mark beginning time of the test
      timeStart = DateTime.Now;

      // execute the statement in a loop and read through the results
      // again, this is not cached
      for (int i = 0; i < 10000; i++)
      {
        dr = cmd.ExecuteReader();
        while (dr.Read())
          ;
        dr.Dispose();
      }

      // mark ending time for this test
      timeEnd = DateTime.Now;

      // calculate total seconds for this test
      totalSeconds1 = Math.Round(timeEnd.Subtract(timeStart).TotalSeconds, 4);

      // display results with no caching
      Console.WriteLine("Caching disabled: " + totalSeconds1 + " seconds");

      // simple select on employee table will be executed repeatedly and timed
      // this statement will use the client-side cache
      cmd.CommandText = "select /*+ result_cache */ first_name, last_name from employees";

      // mark start time for this test
      timeStart = DateTime.Now;

      // execute the statement in a loop and read through the results
      // again, this IS cached
      for (int i = 0; i < 10000; i++)
      {
        dr = cmd.ExecuteReader();
        while (dr.Read())
          ;
        dr.Dispose();
      }

      // mark end time for this test
      timeEnd = DateTime.Now;

      // calculate total seconds for this test
      totalSeconds2 = Math.Round(timeEnd.Subtract(timeStart).TotalSeconds, 4);

      // display results with no caching
      Console.WriteLine("Caching enabled: " + totalSeconds2 + " seconds");
      Console.WriteLine();

      // display percent difference
      Console.WriteLine("Percent difference: " +
        Math.Round(((totalSeconds1 - totalSeconds2) / totalSeconds2) * 100, 2) + "%");

      // if running in debug mode from the IDE,
      // this prevents the command prompt window from closing
      // before examining the output
      Console.WriteLine();
      Console.WriteLine("ENTER to continue...");
      Console.ReadLine();
    }
  }
}

The code illustrates the performance benefits of using the client result cache, by repeatedly executing a simple SELECT statement and measuring the execution time without the cache:

cmd.CommandText = "select first_name, last_name from employees";

versus the time with the cache:

cmd.CommandText = 
"select /*+ result_cache */ first_name, last_name from employees";

Of course, results will vary, but on a test system, the sample code executed in an average time of 1.75 seconds without a client result cache and in 0.81 seconds with the client result cache.

Queries that are repeatedly executed and return the same results benefit most from the client result cache. A best practice is to use the client result cache with read-mostly to read-only data.

Finally, note that client result caching is not specific to ODP.NET development. Client result caching is also available in JDBC, PHP, ODBC, OLE DB, and OCI development in Oracle Database 11g.