To have a realistic shot at solving latch issues, our diagnosis must also include determining the specific latch that is causing the problem. For example, knowing latching is responsible for 80% of the wait time is not enough information. Knowing 75% of the wait time is associated with the CBC latches gives us the detailed information we need to develop a latch-specific solution. So detecting latch contention implies latching is consuming a significant portion of response time and also determining the problematic latch. In nearly all cases, a single latch type will be involved-for example, the library cache latch, the CBC latch, or the LRU latch.

Figure 3-10 shows a classic wait event report based on the Oracle Database 10g v$system_event view. The same information can be found near the top of both a Statspack (Figure 3-13) and an AWR report. Since the top wait event is clearly the CBC latch, we know there is significant latch contention, and we know the specific latch.

Figure 3-10. The v$system_event based OSM swpctx.sql report. Clearly, latch contention is significant, and the latch to focus on is the CBC latch.