Enqueue Wait Analysis from AWR (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 from AWR history by:

• Extracting enqueue wait events from AWR snapshots
• Calculating wait rates and average wait times
• Providing color-coded status indicators
• Focusing on today’s data for current analysis
• Computing timeout percentages and waits per second

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

SQL> @enqueue.sql

Required Privileges

• SELECT on DBA_HIST_SNAPSHOT
• SELECT on DBA_HIST_SYSTEM_EVENT
• Access to AWR repository

Sample Output

                                                      TOTAL      WAITS TIMEOUT      TIME   AVG
SNAP ID SNAP TIME       INST EVENT                   WAITS    PER SEC     PCT   WAITED  WAIT STATUS
                                                                               (ms)   TIME(ms)
------- --------------- ---- ---------------------------- --------- ------ -------- ------- ------ ------
   1234 01/15 09:00        1 enq: TX - row lock contention     1,234     15      5.2   45,678   37.0 RED
   1235 01/15 10:00        1 enq: TX - row lock contention       567      9      3.1   12,345   21.8 YELLOW
   1236 01/15 11:00        1 enq: TX - row lock contention       234      4      1.5    2,345   10.0 YELLOW
   1237 01/15 12:00        1 enq: TX - row lock contention        89      1      0.8      678    7.6 GREEN
   1238 01/15 13:00        1 enq: TX - row lock contention       456      8      2.3    3,456    7.6 GREEN

Key Output Columns

• SNAP ID: AWR snapshot identifier
• SNAP TIME: Snapshot timestamp (MM/DD HH24:MI format)
• INST: Instance number (for RAC environments)
• EVENT: Always ‘enq: TX - row lock contention’ for this script
• TOTAL WAITS: Number of waits during the interval
• WAITS PER SEC: Wait frequency (waits per second)
• TIMEOUT PCT: Percentage of waits that timed out
• TIME WAITED (ms): Total time waited in milliseconds
• AVG WAIT TIME (ms): Average wait time per event
• STATUS: Color-coded performance indicator

Understanding Status Indicators

Performance Thresholds

• GREEN: Average wait time < 10ms (acceptable)
• YELLOW: Average wait time 10-20ms (warning)
• RED: Average wait time > 20ms (critical)

Interpretation Guidelines

• Green status: Normal locking activity
• Yellow status: Moderate contention, monitor closely
• Red status: Significant contention, requires investigation

Common Causes of TX Row Lock Contention

Application Issues

1. Long-running transactions: Extended locks on rows
2. Poor transaction design: Large transaction scope
3. Missing commits: Uncommitted transactions holding locks
4. Batch processing: Large updates without commits

Database Issues

1. Hot blocks: Multiple sessions accessing same data
2. Unselective indexes: Poor WHERE clause selectivity
3. Poor partitioning: Data concentration in few partitions

Common Use Cases

1. Performance Troubleshooting

   • Identify periods of high lock contention
   • Correlate with application activities
   • Track contention patterns over time

2. Capacity Planning

   • Monitor locking trends
   • Plan for peak load periods
   • Assess scalability limits

3. Application Analysis

   • Validate transaction design
   • Identify problematic code paths
   • Optimize batch operations

Customization Options

Modify time range:

-- Last 7 days instead of today
and e.end_interval_time >= trunc(sysdate-7)
and e.end_interval_time <= sysdate

Include other enqueue types:

-- All TX enqueue waits
and upper(se.event_name) like upper('enq: TX%')

-- All enqueue waits
and upper(se.event_name) like upper('enq:%')

Adjust status thresholds:

-- More conservative thresholds
when ... < 5 then 'GREEN'
when ... < 15 then 'YELLOW'
else 'RED' END) STATUS

Troubleshooting High Contention

Investigation Steps

1. Identify blocking sessions: Use lock analysis scripts
2. Review application logic: Check transaction boundaries
3. Analyze SQL patterns: Look for inefficient queries
4. Check batch jobs: Review large data operations

Mitigation Strategies

1. Optimize transactions: Reduce scope and duration
2. Improve indexing: Reduce lock duration
3. Partition data: Distribute contention
4. Schedule batch jobs: Avoid peak periods

Related Scripts

• Lock Analysis
• Transaction Analysis
• Blocking Locks