I noticed that we had an issue in one of our production databases. Looking att he grid control we had this picture.
The massive wait event is in group other, I drilled down into one of the influenced sessions and got this picture.
Now we can see that it is the latch free event, when the wait class is other it means that it is not a significant latch or a latch that is not expected to be experienced very frequently. Oracle has several latches that are handled on its own, you can see a list here. To name a few we have cache buffers chains, library cache load lock etc.
You can list all of them using this sql
SELECT latch#, name FROM v$latchname ORDER BY 2;
The problem we have now is that we know we have a latch issue but not for which latch. Oracle has Note: How to Identify Which Latch is Associated with a “latch free” wait (Doc ID 413942.1) whcih explains how you can use a 10046 trace and awr/statspack to diagnose. I will show some more alternatives.
If the problem is happening right now, you can use the grid control performance view.
Note that it requires that you have the diagnostic pack and running enterprise edition.
Choose performance on the top menu and then blocking sessions, you will then see this picture.
Wait class column shows Other and wait event is Latch free. The interesting column is P2 Value. Each latch has additional information presented, the columns are P1,P2 and P3. The information are different for each latch and you can find the information in the latch description. This is the information for latch Free
For the event latch Free, P2 shows the latch number, we can use this number to find which latch we have issues with.
P2 shows 407, so the latch 407 is the one causing our problems. We can find this by running the sql I listed above.
SELECT latch#, name FROM v$latchname WHERE latch#=407; LATCH# NAME ---------- -------------------------- 407 Result Cache: RC Latch
We have an issue with the Result Cache: RC Latch.
If the event is gone and blocking sessions doesn’t show anything you can use the method listed in the Note listed above or you can use an ASH report. Note that ASH reports also requires diagnostic pack and enterprise edition. If you don’t have diagnostic pack or if you run standard edition I recommend to use statspack and the method described in the note shown above.
I saved an ASH report for the time I had the issue.
You can see the top user events, it is the same as we saw in grid control, Latch free.
You also have the P1/P2/P3 values for the top events below. Looking at the P2 value we can see it is 407. This view also list what information is presented in P1-3 for the various event which can be helpful.
What about he problem ? We are using 220.127.116.11 and it seems we have a few bugs in the result cache area. I believe we experiencing Bug 14095982 – “latch free” waits when result cache is used (Doc ID 14095982.8). Fixed in 18.104.22.168.
We are planning to install 22.214.171.124 as soon as possible.