As this was a RAC on Exadata machine, AWR report was not that much helpful, though immediately was clear the main problem was a latch free event. The problem was, which latch was responsible here.
The performance chart in OEM did not provide SQL text (as apparently those calls were one-time SQL calls - candidates for dealing with them by setting cursor_sharing to FORCE).
A good step was to call the following query:
select name, (misses/decode(gets,0,1,gets) )*100 ratio, (immediate_misses/decode(immediate_gets,0,1,immediate_gets))*100 immediate_ratio, spin_gets, wait_time from v$latch where wait_time > 0 order by 2 ;While one can not be 100% sure the query indicates the right latch, it at least gives some clues. This time the only distinctive ratio (around 40%) was Result Cache: RC Latch. The other one with around 10% was resource manager latch but there the number of gets and misses in total was fairly small, so it was not that interesting.
I've looked for this latch name and found few articles on the subject:
- High "Latch Free" Waits with Contention on 'Result Cache: RC Latch' when RESULT_CACHE_MODE = MANUAL on Oracle 12c (Doc ID 2002089.1) on Metalink
- https://dban00b.wordpress.com/2015/04/21/311/
No comments:
Post a Comment