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.

Figure 3-12 is the OraPub System Monitor (OSM) latch report based solely on v$latch and will work on every Oracle release. In more recent Oracle releases, v$latch does have a wait time column, but even if your view does not have this column, you still have a good way to determine the problematic latch. The Gets column represents the number of times the Get_Latch function (see Figure 3-6) was called. The significant column is Sleeps. This is the number of times the sleep function is called. Not only is the number of sleeps important, but the sleep time is also significant. The user feels the sleep time, not the number of sleeps. Also, the sleep time multiplied by the number of latch requests is approximately the latch free wait time.