Increasing your application performance with the client result_cache feature and ODP.NET

Introduction

In this article, I want to explain how to improve your application performance using Oracle Data Provider for .NET (ODP.NET) and the new features exposed by the underlying Oracle Call Interface. One important feature which is new in Oracle Database 11g R1 and ODP.NET is the client result cache.

The client result cache is a memory buffer that is automatically allocated on the client side 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, so improving the performance and decreasing the resource consumption on the network and the database system. It's remarkable to say that separate client processes don't share the same client result, however within the same client process, sessions with the same user settings can share the same client result cache.

Getting started with the solution

In order to use this important feature, you need to set several parameters in the the init.ora such as:

• client_result_cache_size. Specifies the maximum amount of bytes that the user process can use. The default value is 0, 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 the statements should always use the cache (force) or the SQL statements must be hinted with /*+result_cache*/. The default value of this parameter is MANUAL, indicating that caching occurs when requested by the client.

There is another issue to know about the result cache, because since the OCI client creates the cache on the client side, there might be inconsistencies between the server and client cache, once the data gets changed in the database. You need to know that the client result cache is automatically kept consistency with the database. When a change occurs on the database, this affects the results in the client side, because the OCI client receives a notification of the change. Entries in the cache are managed by the 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 client cache is only used for client_result_cache_lag parameter value for the amount of time as well as every database roundtrip will return information about the cache validity.

One of the most important benefits of client result cache is that it requires no changes to your .NET application code, but you need to make sure that two conditions are met on the client.

First, statement caching must be enabled in the client side. This is the default mode with ODP.NET 11g, so there is no need to take any actions to enable statement caching. And finally, if the result_cache_mode parameter is set to its default value of MANUAL, we have to add a hint to the SQL statement (/*+ result_cache */).

For this application example, we're going to use the dept table in the scott schema shipped as part of Oracle Database Client 11g. In the client side, we need to have installed the Microsoft Visual Studio.2008, Oracle Database Client 11g Release 1, and Oracle Data Provider for .NET.

The typical application code to access to the database using the client result cache feature is shown in the Listing 1.

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace ODPNET_ClientResultCache
{
    public class Main
    {
        using (OracleConnection objConn = new OracleConnection("Data Source=yourhost; User ID=scott; Password=tiger"))
        {
            OracleCommand objCmd = new OracleCommand("SELECT /*+ result_cache */ deptno, dname, loc FROM dept", objConn);
            objConn.Open();
            OracleDataReader objReader = command.ExecuteReader();
            while (objReader.Read())
            {
                Console.WriteLine("DeptNo={0}, Name={1}, Location={2}", objReader.GetDecimal(0), objReader.GetString(1), objReader.GetString(2));
            }
            objReader.Close();
            objConnection.Close();
        }
    }
}

Listing 1

Conclusion

In this article, I've explained how to improve your application performance using Oracle Data Provider for .NET (ODP.NET) and the client result cache feature exposed by the underlying Oracle Call Interface in Oracle Database 11g R1.