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.

Suppose the performance issue occurs at night, and you can't interactively sample from v$latch, or you simply prefer Oracle's Statspack output. Based on the partial Statspack output in Figure 3-13, the top wait event is latch free. Therefore, we know latch contention is significant (so is CPU consumption, but I will save that discussion for Chapter 5). Now the question turns to which specific latch deserves our attention. The second part of Figure 3-13 is the Latch Sleep Breakdown, located about two-thirds down the Statspack report.