STATSPACK Enqueue Activity Analysis (penqueue.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 enqueue (lock) activity from STATSPACK history by:

• Tracking enqueue requests, successful gets, and failed gets
• Calculating wait counts and cumulative wait times
• Computing average wait times for each enqueue type
• Supporting date range and enqueue type filtering
• Showing delta values between snapshots for accurate metrics
• Supporting RAC environments with instance filtering

Script

rem penqueue.sql
rem
set lines 132
rem
col SNAP_ID format 999999 heading 'SNAP ID'
col instance_number format 99 head 'INST'
col snap_time format a15 heading 'SNAP TIME'
col EVENT 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
define start_dt='&start_date'
define end_dt='&end_date'
define nam='&name'
define instno='&inst_no'
rem
ttitle 'Enqueue Activity (&nam)'
rem
col ename format          a2    heading 'Eq';
col reqs  format 999,999,990    heading 'Requests';
col sreq  format 999,999,990    heading 'Succ Gets';
col freq  format  99,999,990    heading 'Failed Gets';
col waits format  99,999,990    heading 'Waits';
col awttm format   9,999,999.99 heading 'Avg Wt|Time (ms)' just c;
col wttm  format 999,999,999    heading 'Wait|Time (s)'    just c;
rem
clear break
clear compute
rem
select
       e.snap_id
     , to_char(e.snap_time, 'mm/dd hh24:mi:ss')          snap_time
     , se.eq_type                                        ename
     , e.instance_number
     , se.total_req#    - nvl(sb.total_req#,0)            reqs
     , se.succ_req#     - nvl(sb.succ_req#,0)             sreq
     , se.failed_req#   - nvl(sb.failed_req#,0)           freq
     , se.total_wait#   - nvl(sb.total_wait#,0)           waits
     , decode(  (se.total_wait#   - nvl(sb.total_wait#,0))
               , 0, to_number(NULL)
               , (  (se.cum_wait_time - nvl(sb.cum_wait_time,0))
                  / (se.total_wait#   - nvl(sb.total_wait#,0))
                 )
             )                                          awttm
     , (se.cum_wait_time - nvl(sb.cum_wait_time,0))/1000  wttm
from stats$snapshot e,
     stats$snapshot b,
     stats$enqueue_stat sb,
     stats$enqueue_stat se
where b.snap_id =
         (select max(x.snap_id)
      from stats$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 sb.snap_id = b.snap_id
  and sb.dbid = b.dbid
  and e.instance_number like nvl('&instno','%')
  and e.snap_time >= nvl(to_date('&start_dt'), trunc(sysdate))
  and e.snap_time <= nvl(to_date('&end_dt'), sysdate) + 1
  and sb.eq_type         = se.eq_type
  and upper(se.eq_type) like nvl(upper('&nam'),'%')
 order by
       e.snap_time,
       se.eq_type,
       e.instance_number
/
rem
undefine start_dt
undefine end_dt
undefine nam
undefine instno

Usage

SQL> @penqueue.sql
Enter value for start_date: 01-JAN-2025
Enter value for end_date: 07-JAN-2025
Enter value for name: TX
Enter value for inst_no: 1

Parameters

• start_date: Beginning date for analysis (DD-MON-YYYY format, defaults to today)
• end_date: End date for analysis (DD-MON-YYYY format, defaults to today)
• name: Enqueue type filter (TX, TM, etc., % for all types)
• inst_no: Instance number for RAC (% for all instances)

Required Privileges

• SELECT on STATS$SNAPSHOT
• SELECT on STATS$ENQUEUE_STAT
• STATSPACK must be installed and collecting data

Sample Output

Enqueue Activity (TX)

SNAP ID SNAP TIME       Eq INST     Requests    Succ Gets Failed Gets      Waits    Avg Wt      Wait
                                                                                   Time (ms)   Time (s)
------- --------------- -- ---- ------------ ------------ ----------- ---------- ----------- -----------
  12345 01/01 10:00:00  TX    1      456,789      456,745          44        234       125.5          29
  12346 01/01 11:00:00  TX    1      523,456      523,398          58        312       156.8          49
  12347 01/01 12:00:00  TX    1      678,901      678,234         667      1,234       234.5         289
  12348 01/01 13:00:00  TX    1      712,345      712,123         222        456       189.3          86

Key Output Columns

• SNAP ID: STATSPACK snapshot identifier
• SNAP TIME: Time of the snapshot
• Eq: Enqueue type (2-character code)
• INST: Instance number (RAC)
• Requests: Total enqueue requests
• Succ Gets: Successful enqueue acquisitions
• Failed Gets: Failed acquisition attempts
• Waits: Number of wait events
• Avg Wt Time (ms): Average wait time in milliseconds
• Wait Time (s): Total wait time in seconds

Understanding Enqueue Types

Common Enqueue Types

• TX: Transaction lock (row-level locking)
• TM: Table lock (DML operations)
• ST: Space transaction lock
• HW: High water mark lock
• SQ: Sequence cache lock
• CF: Control file transaction
• TC: Thread checkpoint

Interpreting Metrics

• High Failed Gets: Indicates contention
• Long Average Wait Times: Blocking issues
• Increasing Waits: Growing contention
• Failed Gets ≈ Waits: Normal blocking pattern

Performance Analysis

Contention Patterns

-- Focus on enqueues with high wait times
Enter value for name: %
-- Then look for high values in Avg Wt Time column

Transaction Lock Analysis

-- Monitor TX locks specifically
Enter value for name: TX
-- High TX waits indicate row-level contention

Table Lock Monitoring

-- Check for TM lock issues
Enter value for name: TM
-- High TM waits suggest table-level locking problems

Common Use Cases

1. Blocking Analysis

   • Identify peak contention periods
   • Track blocking patterns over time
   • Correlate with application activity

2. Capacity Planning

   • Monitor enqueue activity growth
   • Identify scaling bottlenecks
   • Plan for concurrency increases

3. Performance Tuning

   • Find hot objects causing contention
   • Optimize locking strategies
   • Validate application changes

Troubleshooting Patterns

High TX Contention

Symptoms: High TX waits and failed gets Common Causes:

• Unindexed foreign keys
• Hot rows or blocks
• Long-running transactions
• Application design issues

TM Lock Escalation

Symptoms: Increasing TM waits Common Causes:

• Missing indexes on FK columns
• Full table locks from DDL
• Exclusive table locks

Sequence Contention

Symptoms: High SQ enqueue waits Solutions:

• Increase sequence cache size
• Use NOORDER option
• Consider identity columns (12c+)

Historical Trend Analysis

Daily Patterns

-- Run for a week to see daily patterns
Enter value for start_date: 01-JAN-2025
Enter value for end_date: 07-JAN-2025
Enter value for name: %

Peak Hour Identification

Look for patterns in the SNAP TIME column:

• Morning login surge
• Batch processing windows
• End-of-day processing

RAC-Specific Considerations

Cross-Instance Analysis

-- Compare enqueue activity across nodes
Enter value for inst_no: %
-- Look for imbalanced activity

Global Enqueue Monitoring

Some enqueues are global in RAC:

• IV: Instance validation
• DD: Global deadlock detection
• DX: Distributed transaction

Integration Strategies

With Current Waits

1. Use this script for historical trends
2. Use V$ENQUEUE_STAT for current state
3. Correlate patterns with business cycles

With Blocking Scripts

1. Identify contention periods here
2. Use blocking session scripts during peaks
3. Trace specific problem transactions

Best Practices

1. Regular Monitoring

   • Check daily for unusual patterns
   • Establish baseline metrics
   • Alert on threshold breaches

2. Proactive Analysis

   • Monitor failed gets trends
   • Watch average wait time changes
   • Track requests growth

3. Problem Resolution

   • Focus on enqueues with highest wait times
   • Investigate sudden spikes
   • Document recurring patterns

Advanced Analysis

Enqueue Efficiency Ratio

-- Calculate success rate
SELECT eq_type,
       SUM(succ_req#) successful,
       SUM(failed_req#) failed,
       ROUND(SUM(succ_req#) * 100 /
             (SUM(succ_req#) + SUM(failed_req#)), 2) success_rate
FROM stats$enqueue_stat
GROUP BY eq_type
ORDER BY success_rate;

Wait Time Distribution

-- Analyze wait time patterns
SELECT eq_type,
       MIN(avg_wait_time) min_wait,
       MAX(avg_wait_time) max_wait,
       AVG(avg_wait_time) avg_wait,
       STDDEV(avg_wait_time) stddev_wait
FROM (
    -- Subquery with main query logic
)
GROUP BY eq_type;

Troubleshooting

No Data Returned

• Verify STATSPACK is installed
• Check snapshot collection is running
• Ensure date range has snapshots

Missing Enqueue Types

• Some enqueues may not have activity
• Check V$ENQUEUE_STAT for current activity
• Verify enqueue type spelling

Performance Issues

• Limit date range for faster execution
• Use specific enqueue types when possible
• Consider summary tables for long-term analysis

Related Scripts

• Current Enqueue Analysis
• Blocking Session Analysis
• Transaction Analysis
• Lock Analysis