Enqueue Wait Analysis (enqueue.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script analyzes TX row lock contention wait events from AWR history, providing:

• Wait event statistics between consecutive AWR snapshots
• Waits per second calculation
• Timeout percentage for lock requests
• Average wait time with color-coded severity (GREEN/YELLOW/RED)
• Data for the last 24 hours by default

The Script

rem psysev.sql
rem
set lines 132
rem
set pagesize 200;
col SNAP_ID format 999999 heading 'SNAP ID'
col instance_number format 99 head 'INST'
col end_interval_time format a15 heading 'SNAP TIME'
col EVENT_NAME format a35 heading 'EVENT'
col total_waits format b999,999,990 heading 'TOTAL WAITS'
col waits_per_sec format b99,990 heading 'WAITS|PER|SEC'
col timeout_pct format b990.0 heading 'TIMEOUT|PCT'
col time_waited_micro format b999,999,990 heading 'TIME WAITED(ms)'
col avg_wait format 990.0 heading 'AVG|WAIT|TIME(ms)'
rem
rem
rem
clear break
clear compute
rem
select /*+ ordered use_nl(e x b se sb) */
       e.snap_id,
       to_char(e.end_interval_time, 'MM/DD HH24:MI') end_interval_time,
       e.instance_number,
       se.event_name,
       se.total_waits - sb.total_waits total_waits,
       ((se.total_waits - sb.total_waits) /
        ((to_date(to_char(e.end_interval_time,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')
       -  to_date(to_char(b.end_interval_time,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')) *60*60*24)) waits_per_sec,
       -- (se.total_waits - sb.total_waits)
      -- / ((e.end_interval_time - b.end_interval_time) ) waits_per_sec,
       (se.total_timeouts - sb.total_timeouts)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) )*100 timeout_pct,
       (se.time_waited_micro - sb.time_waited_micro)/1000 time_waited_micro,
       (se.time_waited_micro - sb.time_waited_micro)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) )/1000 avg_wait,
  (case
when
(se.time_waited_micro - sb.time_waited_micro)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) )/1000
< 10 then 'GREEN'
when
(se.time_waited_micro - sb.time_waited_micro)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) )/1000
< 20 then 'YELLOW'
else 'RED' END) STATUS
from dba_hist_snapshot e,
     dba_hist_snapshot b,
     dba_hist_system_event se,
     dba_hist_system_event sb
where b.snap_id =
         (select max(x.snap_id)
      from dba_hist_snapshot x
     where x.snap_id < e.snap_id
       and x.dbid = e.dbid
       and x.instance_number = e.instance_number)
  and b.dbid = e.dbid
  and b.instance_number = e.instance_number
  and se.snap_id = e.snap_id
  and se.dbid = e.dbid
  and se.instance_number = e.instance_number
  and sb.snap_id = b.snap_id
  and sb.dbid = b.dbid
  and sb.instance_number = b.instance_number
  and e.end_interval_time >= trunc(sysdate)
  and e.end_interval_time <= sysdate + 1
  and sb.event_name = se.event_name
  and upper(se.event_name) like upper('enq: TX - row lock contention')
order by
      e.end_interval_time
      , e.instance_number
      , se.event_name
/
rem
undefine start_dt
undefine end_dt
undefine nam
undefine instno

Usage

-- Basic usage (last 24 hours)
@enqueue.sql

-- No parameters required

Required Privileges

SELECT ON DBA_HIST_SNAPSHOT
SELECT ON DBA_HIST_SYSTEM_EVENT

Sample Output

SNAP ID SNAP TIME       INST EVENT                               TOTAL WAITS    WAITS TIMEOUT TIME WAITED(ms)    AVG STATUS
                                                                                PER SEC     PCT                  WAIT
                                                                                                                TIME(ms)
------- --------------- ---- ----------------------------------- ------------ ------- ------- -------------- ------- ------
  45678 12/06 10:00        1 enq: TX - row lock contention            1,234       21     0.0         5,670     4.6 GREEN
  45679 12/06 11:00        1 enq: TX - row lock contention            2,567       43     0.5        15,402     6.0 GREEN
  45680 12/06 12:00        1 enq: TX - row lock contention            8,901      148     2.3       125,614    14.1 YELLOW
  45681 12/06 13:00        1 enq: TX - row lock contention           15,234      254    15.2       456,702    30.0 RED
  45682 12/06 14:00        1 enq: TX - row lock contention            3,456       58     1.0        27,648     8.0 GREEN

Key Output Columns

• SNAP ID - AWR snapshot ID
• SNAP TIME - End time of the AWR snapshot
• INST - Instance number (for RAC)
• EVENT - Wait event name (TX row lock contention)
• TOTAL WAITS - Number of waits in the snapshot interval
• WAITS PER SEC - Wait frequency
• TIMEOUT PCT - Percentage of waits that timed out
• TIME WAITED(ms) - Total wait time in milliseconds
• AVG WAIT TIME(ms) - Average wait time per occurrence
• STATUS - Severity indicator:
  • GREEN: < 10ms average wait
  • YELLOW: 10-20ms average wait
  • RED: > 20ms average wait

Common Use Cases

Monitor Locking Trends

-- Run during business hours
@enqueue.sql
-- Look for increasing waits or RED status

Post-Incident Analysis

-- After locking issues reported
@enqueue.sql
-- Identify when problem started and severity

Capacity Planning

-- Regular monitoring
@enqueue.sql
-- High timeout % indicates severe contention

Understanding Row Lock Contention

Common Causes:

1. Application design issues (long transactions)
2. Hot rows/blocks being updated frequently
3. Missing indexes causing full table scans during updates
4. Batch processes conflicting with OLTP
5. Uncommitted transactions

Key Metrics:

• High waits per second: Many concurrent lock requests
• High timeout %: Sessions giving up on locks
• High average wait: Long-held locks

Troubleshooting Steps

-- Find current blocking sessions
SELECT * FROM v$lock WHERE block = 1;

-- Identify locked objects
SELECT object_name, object_type
FROM dba_objects
WHERE object_id IN (
  SELECT object_id FROM v$locked_object
);

-- Find blocking SQL
SELECT sql_text
FROM v$sql
WHERE sql_id IN (
  SELECT sql_id FROM v$session
  WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1)
);

Related Scripts

• Blocking Locks - Current blocking analysis
• Lock Analysis - Detailed lock information
• Active Sessions - Current session activity