The Oracle 11g Result Set Cache stores entire result sets in shared memory .  If a SQL query is  executed and its result set is in the cache then almost the entire overhead of the SQL execution is avoided:  this includes parse time, logical reads, physical reads and any cache contention overhead (latches for instance) that might normally be incurred.

Sounds good right?  In fact you might be thinking that the result set cache is better than the buffer cache.  However, the reality is that the result set caching is only sometimes a good idea.    This is because:

• Multiple SQLs that have overlapping data will store that data redundantly in the cache.  So the result set that contains all customers from California will duplicate some of the data in the cached result set for all customers from North America.  Therefore, the result set cache is not always as memory efficient as the buffer cache.
• Any change to a dependent object – to any table referenced in the query – will invalidate the entire cached result set.   So the result set cache is most suitable for tables that are read only or nearly read only.
• Really big result sets will either be too big to fit in the result set cache, or will force most of the existing entries in the cache out.
• Rapid concurrent creation of result sets in the cache will result in latch contention. 

We introduced diagnostics for the result set cache in Spotlight on Oracle 6.1 - part of the Toad DBA suite.  Let's examine the 11g result set cache using Spotlight as our guide.

The result set cache is part of the shared pool.  By default it is sized at only 1% of the shared pool, so is usually pretty small.  I increased the size of my result set cche to 10MB using the RESULT_CACHE_MAX_SIZE parameter.

We show the result set cache on the Spotlight on Oracle home page within the shared pool section:



We show the size of the result set cache, and the number of result sets "found" in the cache each second.  Any alarms relating to the result set cache (which we'll see a bit later in this article) will show up here.  

We also added a drilldown to show you the contents and the behavior of the result set cache.  Here's an example of that drilldown for a cache which is working well:



There's only one SQL in the cache, though it has 77 result sets.   A single statement can have many result sets - one for each unique combination of bind variables.  So for the statement above, a new result set can be cached for each value of CUST_ID that is supplied to the query.   Let's take a look at that statement in a bit more detail:

1. The RESULT_CACHE hint - identified by a red "1" above - is what allows the SQL to be cached.  In 11g release1, the only other way to make a query cached is to set the RESULT_CACHE parameter tor FORCE.  We'll see later how that is almost always a Bad Idea.
2. The Hit ratio - identified by a red "2" above - indicates how often a matching result set was found in the cache.  In the case above,  57% of executions found their  result set in the cache - pretty good!  
3. The "Execution time % saved" is our estimate of how much execution time was saved through the result cache.  In this example, more than half of the execution time was avoided.  Again, a pretty good result considering the relatively small cost in memory (only 10M)

That statement was a good candidate for caching, since the SQL statement is somewhat expensive,  has a small result set (a single number) and a limited number of possible bind variables (limited to the number of customers).  As a result, we have a good chance of finding a match in the cache and we avoid a lot of work when we do.  The pattern in the  the Result Cache statistics chart shows what we hope to see if the SQL is being cached effectively:



Initially we have a lot of result sets being created, but not many "Finds".   After a while the number of finds increases as the cache fills up with reusable results.  The number of creates decreases because the result sets are already in the cache.   We generally want to see many more finds than creates, because that indicates that the cache entries which are created are generally reused. 

Not suitable for all SQLs

If the above example makes you enthusiastic about result set caching,  brace yourself, because the following examples show how result set caching can often provide no benefit or even severely hurt performance.  Result set caching makes sense if the following are true:

• The result set is small
• The SQL statement is expensive
• The SQL statement does not experience high rates of concurrent execution
• The SQL does not generate a large number of result sets
• The SQL is against relatively static tables

If the result set is large, then it may be too big to fit into the result set cache.  The result set works best when the SQL is expensive, since a quick index lookup might be satisfied by the buffer cache almost as quickly as a result cache lookup.   Expensive aggregate queries such as the one from the previous example are ideal for the result set cache (Bert Scalzo likes to compare these to "on-the-fly in-memory materialized views"). 

If a table accessed in the query is subject to DML, then the result set caches are invalidated.  You can view these dependencies in Spotlight.  So, for instance, below we see that one SQL is dependent on the SALES_ARCHIVE table and the other on the TXN_DATA table.  If any of these tables were subject to frequent updates, then the SQLs would probably not be suitable for result set caching:



If we knew that SALES_ARCHIVE was updated only infrequently, but that TXN_DATA was very volatile, then we could surmise that the second statement above probably should not be cached.

SQLs that generate a large number of distinct result sets are probably not good candidates either.   In the SQL below,  we've cached about 5,100 result sets (1) - all that would fit in our result set cache.  However, over 2.5 million executions (2) we only found 137 matches (3).  This situation arises becasue there can be hundreds of thousands of possible TXN_ID values and they don't repeat very often.  So this SQL is a poor choice for result set caching:



Spotlight will raise a warning if the ratio of finds to creates is very low.  We generally want at least as many finds as creates, since this indicates that result sets are being reused.  Like many "hit ratios", there's no one correct value, but low values might suggest that you should take a closer look at the configuration of your result set cache and selection of SQLs for caching.

Don't force it!

Poor hit rates in the query cache probably do little real harm.  However,  high rates of concurrent result set cache creation can bring a database to its knees.   There is a single latch protecting the query cache; in effect this means that only one session can create a new result set cache entry at any moment.  If you set the RESULT_CACHE_MODE to FORCE, you'll probably kill your system, since every single SQL execution will be blocking on this latch.  In this circumstance, Spotlight will fire a general latch alarm, as well as a specific alarms on the result set cache latch:



In the Result Set Cache drilldown, we can see the rate at which sessions are sleeping on the latch, and the general level of latch waits as a percentage of total active time:



The chart above tells us that overall, about 20% of database time is spent waiting on latches   Requests for the result set cache latch "sleep" - fail to acquire the latch after trying around 2,000 times - about 95% of the time.  This is very severe latch contention and results from allowing SQL statements with high execution rates to be included in the result set cache.    See this Toadworld article for more information on latch contention.

Conclusion

The result set cache is a new 11g facility that allows complete result sets to be stored in memory.  If a result set can be re-used then almost all the overhead of SQL execution can be avoided.  The result set cache best suits small result sets from expensive queries on tables that are infrequently updated.   Applying the result set cache to all SQLs  is unlikely to be effective and can lead to significant latch contention.  In 11g release 1,  you should use the RESULT_CACHE hint to selectively cache SQL result sets.  There's more information about the Result Set cache in my upcoming book Oracle Performance Survival Guide.

Spotlight on Oracle 6.1 - available within the Toad DBA suite - includes the most advanced Result Set cache diagnostics allowing you to determine the effectiveness of your Result Set cache and alert you if it is inefficient or if Result Set cache latch contention is an issue.

Guy Harrison is a Director of Research and Development at Quest Software, and has over 20 years experience in application and database administration, performance tuning and software development. Guy is the author of Oracle Performance Survival Guide (Prentice Hall, 2009) and  MySQL Stored Procedure Programming (O’Reilly with Steven Feuerstein) as well as other books, articles and presentations on database technology.  Guy  is the architect of Quest's Spotlight® family of diagnostic products and has contributed to the development of other Quest products, such as Toad®.  Guy can be found on the internet at www.guyharrison.net, on email at  guy.harrison@quest.com and is @guyharrison on twitter.