Using Oracle 11g client result_cache with ODP.NET

With Oracle 11g OCI clients can store result sets on the client.
I was wondering how ODP.NET will use this feature and if there are any coding constraints.

Required setup:
The feature must be enabled server side by setting the init.ora parameter client_result_cache_size (restart required) to the amout of bytes that a user process can use.
Several sqlnet.ora parameters allow the client to customize the size of the cache. They are optional and I didnt bother testing them.
The parameter result_cache_mode specifies whether statements should always use the cache (force) or whether the SQL statements must be hinted with /*+ result_cache */ (manual which is the default).


Cache consistency
Since the the OCI client creates the cache on the client side, there might be an iconsistency between the server and the client cache, if data gets changed in the database. OCI provides two refresh possibilites.
1. The client cache is only used for client_result_cache_lag amount of time.
2. Every database roundtrip (e.g. for non cached statements) will return information about the cache validity.

Application coding
Application programmers must therefore add the /*+ result_cache */ hint (assuming manual mode). The OCI guide also describes a few other constraints in chapter 10, more OCI advanced topics.
One constraint is probably not obvious. In order to use the cache, the client statement must once loop through all the rows until end of fetch (-1403, no data found).

So far, so good.

My typical application server code looks like this (of course not with hard coded connection strings and variables, but typically with a using constraint for the connection and using a connection pool).


decimal sal = 0;
using (OracleConnection connection = new OracleConnection("Data Source=DB1; User ID=scott; Password=tiger"))
{
OracleCommand command = new OracleCommand("SELECT /*+ result_cache */ sal " +
" FROM EMP " +
" WHERE deptno = 10", connection);
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
sal += reader.GetDecimal(0);
}
reader.Close();
connection.Close();
}
return sal;

This statement fullfills all the requirements to use the cache. But what about my using construct with the connection pooling mechanism? Since I am opening a (pooled) connection all the time, I will get a database roundtrip. This roundtrip will also bring back cache invalidation information.

Assume, that I change after every call the database table emp. The following will happen.

1. execution and creation of cache
==> Database=a, Application=a

Database gets changed to b

2. execution brings back invalidation information
local cache is used for the last time (since the database values changed)
==> Database=b, Application=a

Database gets changed to c

3. execution cannot use the local cache since it has been invalidated by the previous step, a database roundtrip happens, and again a creation of local cache
==> Database=c, Application=c

Database gets changed to d

Like 2
==> Database=d, Application=c

Database gets changed to e
Like 3
==> Database=e, Application=e


Clearly, this coding style produces roundtrips which are contradictory the to goal of the local cache. With that database roundtrip I could use the result_cache on the server side and there is little use of the local cache.

So, you will get the best performance if the the connection and command remain open. We just use another reader object.

OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
sal += reader.GetDecimal(0);
}
reader.Close();


If this is then the only SQL activity in your application, the cache will be used for the time of the client_result_cache_lag setting.

Lesen Sie den vollständigen Eintrag unter http://ursmeier.blogspot.com/2008/04/using-oracle-11g-client-resultcache.html