Sampling from v$session_wait is very straightforward. Figure 3-11 is based on a pre-Oracle Database 10g system v$session_wait view and displays the two key columns of utmost importance to us: event_name and p2.

Regardless of the Oracle release, the second parameter (p2) is the latch number, which can be joined with v$latchname to determine the specific latch. Repeated manual samples from v$session_wait will clearly show the latch number that are sessions are waiting for. Figure 3-11 shows a sample v$session_wait-based query followed by its SQL, and then the v$latchname query to determine the specific latch of interest.

If you have an earlier Oracle system (for example, Oracle 7, Oracle8, or Oracle9i) and cannot sample from v$session_wait, you can still determine the specific latch that needs attention. For example, you can sample from v$latch or use a Statspack report. Remember that regardless of your reporting options, before you drill down into the specific latch, first ensure latching is a significant problem.