What is your experience with Oracle's result caching feature?

Question

We've recently updated to Oracle 11g and our DBA has suggested using result caching to improve the performance of some of our queries. A quick search of Google shows there is some criticism of the feature and how it scales. Like any cache scheme, there will be situations in which it wins big time and other situations where it fails miserably. Our usage pattern (asking the database multiple times for the same bit of information that rarely changes) seems to be well suited for result caching.

Have you had any experience with the feature either positive or negative?

Answer 1

Our first (and very preliminary) test shows considerable promise. Since a cache hit on the query we are testing avoids a full table scan (~3.5 million rows), even a few hits will easily make caching worthwhile.

So far we are just doing one query, which seems fairly safe. I'd like to set the database to cache all results, but that might be riskier.

Answer 2

I would be very wary of anything that tried to have the database cache all query results-- that is very likely going to cause lots of extra work maintaining the result cache for 90% of queries that aren't going to benefit. Oracle's cache invalidation algorithm is pretty basic and designed to ensure that stale results are never returned, so any change to an underlying table is going to force the invalidation of the all the result caches that were derived from that table. Assuming that most of your queries are hitting tables that do change with some frequency, that is probably not worthwhile overhead.

You really only want to cache results that - are expensive to run (otherwise, the marginal benefit of caching the result rather than just caching the data in the buffer cache like Oracle is already doing is minimal. Caching a single-row primary key lookup is probably never going to be worthwhile) - are constant for a reasonable period of time (ensuring that someone else can use the cache) - reference tables that are mostly static (ensuring that you don't have to spend lots of time invalidating cached results when you do DML against the table)