11gr2 SQL result cache test notes
September 3, 2011 3 Comments
Pros:
- SQL gets very good performance if the SQL is cached
- Transparent to app
- Low cost: only need several MB memory and no extra license
Cons:
- Any DML will invalidate the SQL result caches (have table level option in 11gR2).
- Unstable SQL response time: app don’t like it
- OLTP favor: expensive query and few DML
- Concurrence/Scalability issues: only protected by 3 latches in 11gR2. Reference: Oracle 11G Result Cache in the Real World and 11GR2 Result Cache Scalability
My Conclusion:
- not suitable for busy OLTP db.
Notes:
- I have another blog entry to go through Result Cache : Result cache in oracle 11g
- A very good ppt by Julian Dyke: Result Cache Internals
- If a session is building a result cache, the other session may wait 10 seconds (default) to timeout. It is controlled by parameter hidden “_result_cache_timeout”.
About Alex Zeng
Alex,
things changed quite a bit in 11GR2 (the way latch dynamics works), see http://afatkulin.blogspot.com/2010/06/11gr2-result-cache-scalability.html
I read your blog about the behavior in 11GR2. It did better than 10GR1. I updated the link to my blog.
I have a few more notes:
1. The real system is much more complex which could make contentions worse.
2. The result timeout value is 10 seconds by default. It may causes some issue as discussed in below blogs. Although it can be changed by setting hidden parameter “_result_cache_timeout” lower, it still could be a problem.
http://uhesse.wordpress.com/2009/11/27/result-cache-another-brilliant-11g-new-feature/
http://orastory.wordpress.com/2008/11/12/result_cache-blocking/
http://orastory.wordpress.com/2008/11/13/result_cache-blocking-ii/
3. We run into a deadlock on RC resources in DB which has real loads. Setting “_result_cache_timeout” to 1 second is a workaround.
4. In our real load tests, it causes a SQL’s elapse time from 100us to 20,000us. The SQL’s elapse time is around 200ms without result cache. That makes application has a much wide range of response time. They are not happy about it.
So I think it’s still not good for OLTP system. Do you agree?
Thanks!
Pingback: Result cache in oracle 11g « Oracle Explorer: Standing on the shoulders of giants