I have just finished an Oracle Database 11g New Features course in Vienna. There are many amazing New Features in the 11g version, one of them is the possibility to cache the result sets of statements, that access large tables but return relatively few rows. Think of it like automagically created materialized views inside the SGA. Example:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
SQL> select bytes/1024/1024 as mb from user_segments where segment_name='SALES'; MB ---------- 560
SQL> alter table sales result_cache (mode force);
Table altered.
The ALTER TABLE statement is a new feature of 11g Release 2. In Release 1, you can control the feature only with the following parameters:
SQL> show parameter result_cache_m
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_result integer 5
result_cache_max_size big integer 2080K
result_cache_mode string MANUAL
Another possibility, already introduced in 11g R1 is the RESULT_CACHE hint. Now let’s look at the effect of the ALTER TABLE statement:
SQL> set timing on
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 421541477
4 219308832
3 926004170
9 4438820.16
Elapsed: 00:00:03.72
This was the first time, the sales table was accessed after the ALTER TABLE above. The runtime signalizes we have got a full table scan here (there are no indexes on the table anyway). Of course, blocks of the table are now cached in the database buffer cache – as in previous versions. But now, also the result set is cached!
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 421541477
4 219308832
3 926004170
9 4438820.16
Elapsed: 00:00:00.01
That is obvious by runtime already, so I omit AUTOTRACE here. If the table gets changed, the result set gets „stale“, similar like a materialized view would:
SQL> update sales set amount_sold=1 where rownum<2;
1 row updated.
Elapsed: 00:00:00.02
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 421541477
4 219308832
3 926002938
9 4438820.16
Elapsed: 00:00:03.08
Second access after the DML will again use the (newly cached) result set – even if the statement is slightly different, in the same way as materialized views can be used for query rewrite, even if the SELECT differs from the query that built the materialized view. That gives me the opportunity to introduce the new SUPERFAST hint 🙂
SQL> select /*+ superfast */ channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 421541477
4 219308832
3 926002938
9 4438820.16
Elapsed: 00:00:00.00
The „hint“ is just a strange commentar for the engine and is simply ignored, of course. The speed comes from using the cached result set, stored in the SGA by the previous SELECT with the 3 seconds runtime.
Addendum: See these two fine postings by Alex Fatkulin that talk about the 11gR2 improvements regarding the latch contention issue that surfaced with the Result Cache feature in 11gR1:
11GR2 Result Cache Scalability
Result Cache Latch in 11GR2: Shared Mode Gets
Thanks for the comments below that mentioned possible drawbacks of Result Caching! Much appreciated 🙂
Pickleball spielen 
#1 von dombrooks am November 27, 2009 - 17:26
It’s good but just watch out for latching issues as it’s protected by a single latch (or was in 11.1 at least)
#2 von Uwe Hesse am Dezember 1, 2009 - 12:25
Dom,
maybe you are referring to an article by Alex Fatkulin of Pythian, that points out problems with latch contention using the Result Cache feature:
http://www.pythian.com/news/683/oracle-11g-result-cache-tested-on-eight-way-itanium/
There is a quite instructive argument about this on Ask Tom, with Tom Kyte basically recommending to think of Result Caching as „JIT Materialized Views“ and questioning the appropriateness of the test case above:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:676698900346506951
To some degree, the arguments in that thread get relativated by the new possibility of 11gR2, demonstrated in the posting, to designate a table for result caching instead of using the initialization-parameter RESULT_CACHE_MODE=FORCE resp. using the hint RESULT_CACHE.
#3 von Rahul am Dezember 1, 2009 - 20:33
HI,
In an OLTP system, it there are frequent updates to the data set by multiple threads, is this a good design? DB has to keep caching the result set which will be stale with more updates. With Materialized views, we can control when it will be refreshed. Please respond if I have missed something.
#4 von Uwe Hesse am Dezember 2, 2009 - 15:11
Rahul,
a cached result set does not get „refreshed“ like a materialized view would. The next query after a modification of the table simply stores its result set newly in the SGA. So for the (multiple) DML-doing sessions, there is no overhead at all.
There could be a problem, if many concurrent sessions query the same table designated for result caching, though, resp. if you would put the parameter RESULT_CACHE_MODE=FORCE with ALTER SYSTEM.
That may lead to latch-contention, slowing down the queries. Also, if you modify the table frequently, it is unlikely that your queries benefit much from cached result sets, because the result sets are constantly „stale“.
As with so many features, you should test it thoroughly before using it in production.
An easy way to get a proper test environment would be to setup a snapshot standby database, capture the production workload and replay it on the test DB with the 11g New Feature Database Replay 🙂
#5 von dombrooks am Dezember 2, 2009 - 16:09
> maybe you are referring to an article by Alex Fatkulin of Pythian
Partly, but mostly from experience of implementing code using result cache in a system which was upgraded to 11gR1 18 months ago and diagnosing and resolving the subsequent latch free performance problems which have been subsequently been experienced.
#6 von Ben Krug am Dezember 8, 2009 - 04:37
This can be a nice feature, and, by the way, one that has been available in MySQL for quite a long time. 🙂
#7 von Uwe Hesse am Dezember 14, 2009 - 14:56
Ben,
I have noticed that also in passing. But why not learn from the (former) competition? They rarely have something to learn from, though 🙂
#8 von Jonathan Lewis am Dezember 16, 2009 - 15:21
A point that someone (possibly Connor McDonald) mentioned at the UKOUG conference is the threat when two processes both want a result that has just been invalidated.
One process will start to rebuild it, the other will wait for up to 10 seconds (in 11.2) for the build to complete and then decide to query the base table if it’s not ready.
So if it takes (say) 15 seconds to rebuild a result cache entry, the first process will take 15 seconds to complete, the second will take 25 because it waits 10 seconds then spends 25 building.
The upshot is that you should be cautious about using the feature for results that take more than about 9 seconds to rebuild if they are liable to popular use combined with occasional invalidation.
#9 von Uwe Hesse am Dezember 21, 2009 - 09:51
Jonathan,
thank you for sharing this information about the possible drawback of Result Caching!
It’s always good to know the little pitfalls, a new feature can impose.
To summarize: Due to the possible wait of another session to store the result set into the result cache, a query that usually takes about 10 seconds (but less than 30 seconds; else the benefit of the stored result set probably outweighs the time, waited for the other session to store it) faces the risk to take about the doubled time as usual.
#10 von Anonymous am Mai 14, 2010 - 02:04
The „10 seconds“ timer that Jonathan mentions is started only after the result rows have started to be sent to the client. So, if user A issues a query that takes 2 minutes (e.g. to do hash joins, sorts, etc.) then all subsequent users will wait those 2 minutes for user A to complete the query, but once the rows start flowing to the client they will wait at most 10 seconds longer for the result to be published by user A. This scenario can be verified in sql*plus using dbms_lock.sleep().
#11 von Uwe Hesse am Mai 18, 2010 - 20:44
Dear Anonymous,
your statement „[…] all subsequent users will wait those 2 minutes for user A to complete the query […]“ contradicts Jonathan Lewis statement „[…] the other will wait for up to 10 seconds (in 11.2) for the build to complete and then decide to query the base table if it’s not ready.“, doesn’t it? Must find some spare time to test that somewhen…
#12 von RC am März 4, 2011 - 02:31
The latch scalability issue don’t seem to be present in 11.2 (http://afatkulin.blogspot.com/2010/06/11gr2-result-cache-scalability.html#comments).
#13 von Uwe Hesse am März 14, 2011 - 16:39
Thank you for the information!
#14 von raviswam am Juni 15, 2016 - 10:46
A view based on table for which result_cache is enabled (‚Alter table result_cache (mode force)‘) does not use result_cache unless hinted (/*+ result_cache */) to do so.
I had expected it to work without hint. Your understanding the same ?