What is your experience with the Oracle Results Caching feature?
We recently updated to Oracle 11g and our DBA suggested using result caching to improve the performance of some of our queries. A quick Google search reveals that there is some criticism of this feature and its scope. As with any cache scheme, there will be situations in which it gains a lot of time and other situations where it fails. Our usage pattern (with querying the database multiple times for the same bit of information that rarely changes) seems to be a good fit for caching results.
Have you had any experience with positive or negative characteristics?
source to share
I would be very wary of anything that was trying to get the database cache of all the query results - it would likely cause a lot of extra work, maintaining the result cache for 90% of the queries that would be of no use. Oracle's cache invalidation algorithm is quite simple and is designed to ensure that stale results are never returned, so any change to the underlying table will invalidate all result caches retrieved from that table. Assuming most of your queries end up in tables that change with some frequency, this is probably not worth the overhead.
You really only want to cache results that are expensive (otherwise the marginal benefit of caching the result, rather than just caching data in the buffer cache as Oracle does, is minimal. Single-line primary lookup caching is never advisable) - persistent for a reasonable amount of time (ensuring that someone else can use the cache) - reference tables, which are mostly static (ensuring you don't have to spend a lot of time invalidating cached results when you do DML on the table)
source to share
Our first (and very preliminary) test shows significant promise. Since the cache hitting the query we're testing avoids a full table scan (~ 3.5 million rows), even a few hits will easily make caching worthwhile.
So far, we're just making one request, which seems to be pretty safe. I would like to set up a database to cache all results, but that might be more risky.
source to share
In a RAC environment, I've always seen a return value valid in the same instance, but when used with functions that frequently updated table data, we had complaints that the value was out of date when the function was called on a different instance than the instance. on which the update was performed.
source to share