Tips and wisdom from 15 years of DBA experience

Monday, November 10, 2008

11g OCI client query cache

If you are an OLTP DBA, you know the value of what this feature can be. We have all worked with developers to reduce repetitive select queries in order to keep them off of our CPUs, and keep are eyes off of those mounting cache buffers chains latches. For custom apps, working with developers to develop a caching layer can be very rewarding if you have the luxury of time to engage in a project like this. For Java apps using Hibernate, details are hidden away from both you and the app developer, and chances are pretty good that if you want a Java developer to dig into how to control that caching layer, they will not be as enthusiastic about exploring it's innards as you. After all, the entire purpose of a caching layer is to prevent having to deal with data persistence directly. It is my experience that most web developers don't fully appreciate that their applications really are database applications, so concepts like locking, transaction isolation levels, deadlock protection, excess data retrival (wastage) etc. are not given sufficent attention, but I digress. For packaged apps, we are left to throw hardware and/or RAC licenses at the problem - not an easy expenditure to explain to the CEO, especially on top of the licenses for that packaged app.

Enter the promise of 11g OCI client query cache. Whether an app is using native OCI calls, JDBC, or ODBC, or some other driver interface layer, the OCIStmtExecute() and OCIStmtFetch() calls are made at some point during query processing. The OCI client query cache is implemented "below" those calls, so existing apps will continue to function with no changes. I can already hear you saying - "Yeah, that's great but we all know that app cache = db triggers and well...not on my database..." In this new client cache mechanism, Oracle hides the implementation from you, so you do not need to know whether it is implemented using triggers, interprocess communication, or some other proprietary mechanism. All you need to know is that the cache is kept consistent, and your apps are more efficient. The details are probably exposed someplace in the OCI docs, but my suspicion is that they borrowed a page from the Postgres book and implemented something using process signals such as SIGUSR2 as used in Postgres's LISTEN/NOTIFY architecture.

The init.ora param result_cache_mode controls whether all queries are to be cached on the client or only those with the /* + result_cache */ hint added. There are other init.ora parameters that control how large the cache should be, The maximum lag that the cache can have behind any database changes that have occurred, as well as parameters in the client configuration file specific to each client installation.

Only production road-time will determine whether the cache works as well as we hope it does - and if it doesn't, we will submit the appropriate bugs and hopefully they will get it patched in the next release.

No comments: