Oracle maintains a shockingly large number of enqueues.10 In Oracle Database 10g Release 2, I found 208 enqueues; in Oracle Database 11g Release 1, I found 247 enqueues. But don't panic, because there are only a handful of enqueues you're likely to encounter. Plus if you're a seasoned DBA, you have already been dealing with row- and table-level locks, which use enqueues.

When solving enqueue problems, first determine the enqueue type, then determine the SQL involved, and finally develop a solution based on your knowledge of the application and the associated Oracle internals. Before I drill down into the most common enqueue wait, the transaction (TX) enqueue, it's important to know how to determine which enqueue is being waited upon and the associated sessions for both pre-Oracle Database 10g and later systems.

Prior to Oracle Database 10g, the wait event for all enqueues was simply enqueue. This is indeed unfortunate, as it requires the firefighter to sample from either v$lock or v$session_wait to determine the enqueue name. The SQL to determine the enqueue name from v$session_wait, shown in Figure 6-31, is particularly unsavory. When run, the Figure 6-31 SQL provides details for every session waiting for an enqueue. Figure 6-32 shows one such example, where three sessions are involved with a table lock.