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.

While not shown in Figure 3-13, more recent Oracle releases and Statspack reports will show the Time Waited column. The time waited value should be very close to the wait time reported by Oracle's wait interface. If so, then use this number to determine the latch that needs attention. If the Time Waited column is not provided, you'll need to manually calculate the Impact column, as discussed earlier. Just copy and paste the Statspack values into a spreadsheet and create an Impact column. The top latch should clearly rise above the rest.