High latch Free – Other

I noticed that we had an issue in one of our production databases. Looking att he grid control we had this picture.

ScreenHunter_66 Aug. 21 09.34

The massive wait event is in group other, I drilled down into one of the influenced sessions and got this picture.

ScreenHunter_68 Aug. 21 09.41

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.

ScreenHunter_67 Aug. 21 09.38

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

ScreenHunter_70 Aug. 21 10.30

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.

ScreenHunter_69 Aug. 21 10.13

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 11.2.0.3 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 11.2.0.4.

We are planning to install 11.2.0.4 as soon as possible.

Advertisements

One thought on “High latch Free – Other

  1. Pingback: Result Cache “wrong result” | Oracle Tuning

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s