Just as with the latching wait event, starting with Oracle Database 10g, each enqueue has been given its own wait event. This saves us a diagnostic step, as we can determine both the involved sessions and the enqueue type with a single simple query. Figure 6-33 shows another locking situation based on an Oracle Database 10g system. In this situation, three sessions are involved. Session 4393 has the table locked and is therefore not waiting and not shown. Sessions 4383 and 4388 are waiting to lock the table and are therefore posting a TM enqueue wait. The table involved can be determined by cross-referencing the P2 column (49911) with the object_id column in the dba_objects view. And, of course, referencing v$session will review many interesting details about the session, such as the SQL being run.

Figure 6-32. Output from the SQL shown in Figure 6-31. Three sessions are involved. Session 4388 has the table locked and is not waiting and therefore not shown. Next in the queue is session 4387, followed by session 4393. The table involved has an identifier of 49911, which can be cross-referenced from the object_id column in the dba_objects view. The ID 1 report column is actually the v$session_wait column p2.

Figure 6-33. Shown is a standard v$session_wait-based report (in particular, the OSM swswp.sql script) with three sessions involved in a TM enqueue wait. In this particular situation, session 4393 has the table locked and is not waiting on the enqueue. Session 4388 is next in the queue, followed by session 4383.