In Figure 6-32, session 4388 has the table locked, is not waiting for the lock, and therefore is not shown. The first session in queue is session 4387, followed by session 4393. A simple way to determine the SQL being run and hence the table involved is to query the session's sql_address or sql_hash_value from v$session. For the TM enqueue, the table can be easily identified since the p2 column (also known as the ID 1 column) contains the object_id, which can be referenced in dba_objects. This makes determining the object of contention very simple. Oracle is constantly adding enqueues, so I have found the best way to get enqueue identifier details is to perform a very specific Internet search.

Figure 6-31. The core and working SQL from the OSM swenq.sql script, which provides enqueue wait details for pre-Oracle Database 10g systems.

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.