Using Oracle's wait interface, detecting Oracle latch contention is very straightforward. As I have mentioned, nearly every production Oracle system will have latch contention, but that does not imply there is a problem worthy of our attention. Only when the contention rises to the top of our wait event and ORTA reports is it significant and worthy of our precious time to improve the situation.

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.