One pretty cool new feature of Oracle 11g that DBAs may not be aware of is the client result cache. This stores the result sets of queries hinted with /*+ result_cache */ locally in-memory on the client. It’s much better than just doing it in your own code: the server is aware of it and signals invalidation when the underlying data changes (and no AQ triggers necessary). The client library then directs the query execution to the closest valid source. Apart from the hinting, this is completely transparent to the application, so this is potentially a very quick win for improving the performance of applications that need to often lookup data that (relatively) rarely changes, yet must always be the latest version. It cuts the load on the server, and it also saves on network roundtrips and the associated latency. Even if the application code cannot be changed, this mode can be enabled (for all queries of suitable data types) globally for the client.

At my current site we use Coherence (formerly Tangosol) to do this on a larger scale. That makes sense if you have many dozens or hundreds of clients looking at the same data – but for fewer clients, or for clients that need to cache different data, there is no need for that kind of complexity (and expense!). Coherence is a very capable product that does a lot more than just cache result sets – but if that’s all you need… Maybe you should look into memcached.

Speaking of roundtrips, I was having a discussion with a fellow DBA recently about the use of AUTOCOMMIT. He was of the opinion that you should never use it in Oracle; it only exists in SQL Server to help compensate for the latter’s higher overhead in locking. Well, consider the common access pattern of INSERT → COMMIT → INSERT → COMMIT..., for example, when logging time series data. Using AUTOCOMMIT in this scenario halves the network latency – very significant if you are connecting over a WAN and/or traversing many firewalls. And it’s another “quick win”, if you have access to the application’s source. These are both a good example of a situation where it pays for the business as a whole to have the Devs and the DBAs working closely together.

Finally Craig “the hammer” Shallahamer is on Twitter now, dispensing his wisdom 140-chars at a time…