Figure 3-12 is the OraPub System Monitor (OSM) latch report based solely on v$latch and will work on every Oracle release. In more recent Oracle releases, v$latch does have a wait time column, but even if your view does not have this column, you still have a good way to determine the problematic latch. The Gets column represents the number of times the Get_Latch function (see Figure 3-6) was called. The significant column is Sleeps. This is the number of times the sleep function is called. Not only is the number of sleeps important, but the sleep time is also significant. The user feels the sleep time, not the number of sleeps. Also, the sleep time multiplied by the number of latch requests is approximately the latch free wait time.

Understanding the strong correlation between Oracle's wait interface and v$latch sleep time and latch requests, Steve Adams5 created a wonderful indicator named impact. The impact value is simply the number of sleeps multiplied by the number of sleeps divided by the number of gets: sleeps*(sleeps/gets). Figure 3-13 shows the OSM report latch.sql, which includes the Impact column and the impact percentage for each latch listed. My personal experience has shown that in a latch-suffering system, the Impact column clearly indicates the problematic latch and also matches perfectly with the wait event reports. Figure 3-12 also shows the top latch to be CBC by both the Wait Time and the Impact columns. Assuming latching is a significant issue, our solutions will certainly directly address reducing CBC latch contention.

Figure 3-12. The OSM latch report, with some columns removed. The Impact column is calculated as sleeps multiplied by sleeps divided by gets. Both the wait time and the impact indicate the CBC latches are where we should focus any latch-specific solution.