Global Blocking and Waiting Locks Analysis (gvlockb.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 provides comprehensive analysis of blocking and waiting lock relationships across all RAC instances. It identifies lock contention situations, deadlock scenarios, and session blocking chains that can severely impact database performance. The script focuses specifically on locks where sessions are waiting (REQUEST > 0), showing both the blocking sessions and the sessions being blocked.

The Script

rem gvlockb.sql
ttitle 'Global Blocking/Waiting Locks'
rem
set lines 600
rem
col ID1 format 9999999999
col ID2 format 9999999999
col LMODE format 999
col REQUEST format 999
col SID format 9999
col inst_id format 999 heading 'INST'
col username format a9
col osuser format a8
col module format a15
rem ADDR             RAW(4)
rem KADDR            RAW(4)
rem TYPE             VARCHAR2(2)
col CTIME format 999999
col BLOCK format 99999
col blocker format a7
rem
break on id1 on id2 skip 1
rem
select /*+ ordered use_nl(l,s) */
   l.ID1,
   l.ID2,
   decode(l.block, 0, null, 'Blocker') blocker,
   l.inst_id,
   l.SID,
   s.username,
   s.osuser,
   s.module,
   l.LMODE,
   l.REQUEST,
   l.TYPE,
   -- l.ADDR,
   -- l.KADDR,
   l.CTIME,
   l.BLOCK,
   --s.sql_hash_value
   s.sql_id, sw.event
from gv$lock l,
     gv$session s,
  gv$session_wait sw
where (l.id1, l.id2) in
   (select id1, id2
      from gv$lock
     where request > 0)
  and s.sid(+)=l.sid+0
  and s.sid = sw.sid(+)
  and s.inst_id = sw.inst_id(+)
  and s.inst_id(+) = l.inst_id
order by l.id1, l.id2, l.block desc, l.lmode desc, l.CTIME desc
/

Key Features

• RAC-Aware Analysis: Monitors locks across all instances in RAC environment
• Blocking Relationship Mapping: Clearly identifies blocker and waiter relationships
• Session Context: Shows user, OS user, and application module for each session
• Lock Detail Analysis: Displays lock modes, types, and timing information
• SQL Correlation: Includes SQL_ID and current wait events
• Hierarchical Display: Groups results by lock resource with logical ordering

Usage

@gvlockb.sql

No parameters required - analyzes all current lock contention across instances.

Required Privileges

SELECT on GV$LOCK
SELECT on GV$SESSION
SELECT on GV$SESSION_WAIT

Sample Output

                                  Global Blocking/Waiting Locks

       ID1        ID2 BLOCKER INST  SID USERNAME  OSUSER   MODULE          LMODE REQUEST TY    CTIME  BLOCK SQL_ID        EVENT
---------- ---------- ------- ---- ---- --------- -------- --------------- ----- ------- -- -------- ------ ------------- ---------------
    393254      55842 Blocker    1  145 SALES_APP jsmith   MyApp.exe           6       0 TX     12456      1 8fz9x2q7mv8k2
                             2  289 SALES_APP rjones   MyApp.exe           0       6 TX      8934      0 3p7w9k5x2m1q4 enq: TX - row lock contention

    393255      55843 Blocker    1  167 FINANCE   mbrown   Finance.exe         3       0 TM      5678      1 7k2m9p4x8v3q1
                             1  234 FINANCE   kwhite   Finance.exe         0       3 TM      3456      0 9m3k7p2x5v8q4 enq: TM - contention
                             2  156 REPORTS   tblack   Reports.exe         0       3 TM      2345      0 4x8v9m2k7p3q1 enq: TM - contention

    393256      55844           1  178 BATCH_JOB oracle   sqlplus.exe         0       4 TX      1234      0 6v3q9m7k2p8x5 enq: TX - allocate ITL entry

Key Output Columns

• ID1/ID2: Lock resource identifiers (combination uniquely identifies the resource)
• BLOCKER: Indicates if session is blocking others (‘Blocker’ or blank)
• INST: RAC instance ID where the session resides
• SID: Session ID holding or waiting for the lock
• USERNAME: Database username
• OSUSER: Operating system user
• MODULE: Application module name
• LMODE: Lock mode held (0=None, 1=Null, 2=Row Share, 3=Row Exclusive, 4=Share, 5=Share Row Exclusive, 6=Exclusive)
• REQUEST: Lock mode requested (same scale as LMODE, 0=not waiting)
• TY: Lock type (TX=Transaction, TM=Table, etc.)
• CTIME: Time lock has been held (in centiseconds)
• BLOCK: Number of sessions this lock is blocking
• SQL_ID: Current SQL statement identifier
• EVENT: Current wait event for the session

Understanding Lock Modes

Lock Mode Reference

0 = None (not holding)
1 = Null (NULL)
2 = Row Share (RS)
3 = Row Exclusive (RX)
4 = Share (S)
5 = Share Row Exclusive (SRX)
6 = Exclusive (X)

Lock Type Reference

TX = Transaction Lock (row-level)
TM = Table Lock (DML operations)
UL = User Lock (application-defined)
CU = Cursor Lock
HW = High Water Mark Lock
ST = Space Transaction Lock

Lock Compatibility Matrix

     |  1  2  3  4  5  6
-----|------------------
  1  |  Y  Y  Y  Y  Y  Y
  2  |  Y  Y  Y  Y  Y  N
  3  |  Y  Y  Y  N  N  N
  4  |  Y  Y  N  Y  N  N
  5  |  Y  Y  N  N  N  N
  6  |  Y  N  N  N  N  N

Y = Compatible, N = Incompatible

Common Use Cases

1. Lock Contention Analysis

   • Identify blocking session chains
   • Analyze lock wait patterns
   • Determine root cause of contention
   • Plan contention resolution strategies

2. Deadlock Investigation

   • Investigate deadlock scenarios
   • Analyze circular wait conditions
   • Identify deadlock-prone applications
   • Plan deadlock prevention strategies

3. Performance Troubleshooting

   • Diagnose application slowdowns
   • Identify resource bottlenecks
   • Analyze wait event patterns
   • Support performance tuning efforts

4. RAC-Specific Issues

   • Cross-instance lock contention
   • Global lock coordinator analysis
   • Instance-specific blocking patterns
   • Cluster interconnect impact assessment

Advanced Analysis

Blocking Chain Analysis

-- Understanding blocking chains:
-- Session A blocks Session B
-- Session B blocks Session C
-- Session C blocks Session D
-- Kill Session A to resolve entire chain

Lock Duration Analysis

-- CTIME interpretation:
-- High CTIME values indicate:
-- Long-running transactions
-- Potential application issues
-- Inefficient transaction design
-- Need for transaction optimization

Cross-Instance Contention

-- RAC-specific patterns:
-- Instance 1 blocking Instance 2
-- Global lock coordination overhead
-- Interconnect communication delays
-- Consider workload partitioning

Troubleshooting Applications

Immediate Resolution

Kill Blocking Sessions

-- For immediate relief:
-- Identify the root blocker (session with BLOCKER='Blocker')
-- Consider impact of killing the session
-- Use ALTER SYSTEM KILL SESSION
-- Monitor for cascade effects

Transaction Analysis

-- Analyze blocking transactions:
-- Check transaction size and scope
-- Review SQL efficiency
-- Identify unnecessary long transactions
-- Plan transaction optimization

Long-Term Solutions

Application Design

-- Address application issues:
-- Minimize transaction duration
-- Avoid unnecessary exclusive locks
-- Implement proper error handling
-- Use appropriate isolation levels

Database Configuration

-- Database-level optimizations:
-- Adjust lock timeout parameters
-- Configure deadlock detection
-- Optimize buffer pool sizing
-- Consider partitioning strategies

Lock Type Analysis

Transaction Locks (TX)

Row Lock Contention

-- TX locks with REQUEST > 0:
-- Multiple sessions updating same rows
-- Long-running transactions holding locks
-- Insufficient commit frequency
-- Application serialization issues

ITL Shortage

-- TX locks for ITL allocation:
-- Table blocks need more ITL entries
-- Consider INITRANS and MAXTRANS
-- Analyze block utilization
-- Plan table reorganization

Table Locks (TM)

DML Lock Contention

-- TM locks indicate:
-- Conflicting DML operations
-- DDL operations blocking DML
-- Foreign key constraint issues
-- Partition lock contention

Schema Modification

-- TM locks during DDL:
-- ALTER TABLE operations
-- Index creation/rebuild
-- Constraint operations
-- Statistics gathering

Performance Impact Assessment

Wait Event Correlation

-- Common wait events with locks:
-- "enq: TX - row lock contention"
-- "enq: TM - contention"
-- "gc buffer busy acquire"
-- "gc current block busy"

Application Impact

-- Assess business impact:
-- Critical application modules affected
-- Peak usage time correlation
-- User experience degradation
-- SLA violation potential

Resource Utilization

-- System resource impact:
-- CPU usage patterns
-- Memory consumption
-- I/O wait times
-- Interconnect utilization

Prevention Strategies

Application Development

-- Best practices:
-- Minimize transaction scope
-- Access objects in consistent order
-- Implement retry logic for deadlocks
-- Use appropriate lock hints

Database Design

-- Design considerations:
-- Proper normalization
-- Effective partitioning
-- Appropriate indexing
-- Foreign key design

Monitoring Integration

-- Automated monitoring:
-- Alert on high lock waits
-- Track blocking session patterns
-- Monitor lock duration trends
-- Integrate with performance dashboards

RAC-Specific Considerations

Global Lock Manager

-- RAC lock coordination:
-- Global cache coordination
-- Cross-instance messaging
-- Lock mastering concepts
-- DRM (Dynamic Resource Mastery)

Instance Affinity

-- Workload distribution:
-- Partition instance affinity
-- Application-instance mapping
-- Service-based routing
-- Connection pooling strategies

Interconnect Impact

-- Network considerations:
-- Interconnect bandwidth
-- Latency effects on locks
-- Cluster wait events
-- Performance monitoring

Integration with Other Analysis

Session Analysis

-- Combine with session scripts:
-- gvsess.sql for detailed session info
-- gvwait.sql for wait event analysis
-- vsession.sql for single-instance focus
-- Track session behavior patterns

SQL Analysis

-- Correlate with SQL performance:
-- Identify problematic SQL statements
-- Analyze execution plans
-- Review bind variable usage
-- Plan SQL optimization

Historical Analysis

-- Trend analysis:
-- Track lock contention over time
-- Identify peak contention periods
-- Correlate with application changes
-- Plan capacity and optimization

Automation and Alerting

Automated Monitoring

-- Schedule regular execution:
-- Monitor for blocking chains
-- Alert on contention thresholds
-- Log results for analysis
-- Integrate with monitoring systems

Escalation Procedures

-- Define escalation criteria:
-- Blocking duration thresholds
-- Number of sessions affected
-- Critical application impact
-- Automated resolution actions

Related Scripts

• gvlock.sql - Comprehensive lock analysis
• vlockb2.sql - Single-instance blocking locks
• vlock.sql - Basic lock analysis
• gvsess.sql - Session analysis across RAC

Best Practices

Regular Monitoring

-- Establish monitoring routine:
-- Schedule during peak hours
-- Monitor for blocking patterns
-- Track resolution effectiveness
-- Document common scenarios

Documentation

-- Maintain incident documentation:
-- Record blocking scenarios
-- Document resolution steps
-- Track application patterns
-- Share knowledge with teams

Proactive Management

-- Prevent issues:
-- Review application design
-- Optimize transaction patterns
-- Implement proper indexing
-- Monitor trends and patterns

Summary

This script is essential for:

1. Lock Contention Analysis - Identifying and resolving blocking relationships
2. Deadlock Investigation - Understanding and preventing deadlock scenarios
3. RAC Performance - Analyzing cross-instance lock coordination
4. Application Tuning - Optimizing transaction and locking patterns
5. Incident Response - Quick identification and resolution of blocking issues