Result_cache blocking
November 12, 2008 2 Comments
Had a result_cache problem today.
If you didn’t know it already, DBA_HIST_ACTIVE_SESS_HISTORY is a real boon for getting a very good idea of a) when the problem started and b) how wide the impact was.
300 session were backing up on a latch free wait. Turns out the latch was the “Result Cache: Latch”.
The blocking session was doing a select from a view that used some result_cache function lookups.
The blocking session having been killed and given a couple of minutes to clean up, the blockage cleared in a instant.
Still trying to get to the bottom of the result cache issue – is there a serious result_cache flaw or have I just implemented it poorly?
Related info:
- http://www.pythian.com/blogs/594/oracle-11g-query-result-cache-and-the-rc-enqueue
- http://www.pythian.com/blogs/598/oracle-11g-query-result-cache-rc-latches
- http://www.pythian.com/blogs/1004/oracle-11g-result-cache-in-the-real-world
- http://www.pythian.com/blogs/660/does-oracle-11gs-result-cache-scale-poorly
OraStory 
Pingback: Result_cache blocking II « OraStory
Interesting comment from Jonathan Lewis on this post
http://uhesse.wordpress.com/2009/11/27/result-cache-another-brilliant-11g-new-feature/
“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.
“