Library Lock Analysis (vliblock.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 library cache lock contention by identifying sessions currently waiting on library cache events and showing the specific database objects causing the contention. It provides detailed information about lock modes held and requested, helping diagnose parse-related performance issues, DDL blocking, and library cache contention problems.

The Script

--need to be sysdba
select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, 'MATERIALIZED VIEW',
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      62, 'EVALUATION CONTEXT',
                     'UNDEFINED') object_type,
       lob.KGLNAOBJ object_name,
       pn.KGLPNMOD lock_mode_held,
       pn.KGLPNREQ lock_mode_requested,
       ses.sid,
       ses.serial#,
       ses.username
  FROM
       x$kglpn pn,
       v$session ses,
       x$kglob lob,
       v$session_wait vsw
  WHERE
   pn.KGLPNUSE = ses.saddr and
   pn.KGLPNHDL = lob.KGLHDADR
   and lob.kglhdadr = vsw.p1raw
   and vsw.event like 'library%'
order by lock_mode_held desc
/

Key Features

• Real-Time Lock Analysis: Shows current library cache lock contention
• Object Identification: Identifies specific objects causing lock waits
• Lock Mode Analysis: Shows held vs. requested lock modes
• Session Information: Displays affected sessions and users
• Comprehensive Object Types: Covers all database object types
• DDL Blocking Detection: Identifies DDL operations causing blocks
• Parse Contention Analysis: Helps diagnose parse-related issues

Usage

@vliblock.sql

Important: This script requires SYSDBA privileges to access X$ tables.

Required Privileges

-- Must be connected as SYSDBA
CONNECT / AS SYSDBA

-- or have specific privileges:
SELECT on X$KGLPN
SELECT on X$KGLOB
SELECT on V$SESSION
SELECT on V$SESSION_WAIT

Sample Output

OBJECT_TYPE      OBJECT_NAME                    LOCK_MODE_HELD LOCK_MODE_REQUESTED     SID    SERIAL# USERNAME
---------------- ------------------------------ -------------- --------------- ------- ------- ----------
PACKAGE BODY     DBMS_STATS                                  3               0     234    45691 HR
PACKAGE BODY     DBMS_STATS                                  0               3     456    23847 SALES_APP

PROCEDURE        UPDATE_EMPLOYEE_SALARY                      3               0     123    78934 DBA_USER
PROCEDURE        UPDATE_EMPLOYEE_SALARY                      0               3     567    12456 HR_BATCH

TABLE            EMPLOYEES                                   2               0     345    56789 HR
TABLE            EMPLOYEES                                   0               5     678    89012 DDL_USER

PACKAGE          CUSTOM_UTILS                                3               0     789    34567 APP_USER
PACKAGE          CUSTOM_UTILS                                0               3     890    67890 APP_USER2

Key Output Columns

• OBJECT_TYPE: Type of database object (TABLE, PACKAGE, PROCEDURE, etc.)
• OBJECT_NAME: Name of the object being locked
• LOCK_MODE_HELD: Lock mode currently held by the session
• LOCK_MODE_REQUESTED: Lock mode being requested by waiting session
• SID: Session ID of the session
• SERIAL#: Serial number of the session
• USERNAME: Database username of the session

Understanding Library Cache Lock Modes

Lock Mode Values

Held Lock Modes

-- Lock modes held (KGLPNMOD):
-- 0: No lock held
-- 1: NULL lock
-- 2: SHARE lock (S)
-- 3: SHARE ROW EXCLUSIVE lock (SRX)
-- 4: EXCLUSIVE lock (X)
-- 5: EXCLUSIVE lock with immediate invalidation

Requested Lock Modes

-- Lock modes requested (KGLPNREQ):
-- 0: No lock requested
-- 1: NULL lock requested
-- 2: SHARE lock requested
-- 3: SHARE ROW EXCLUSIVE lock requested
-- 4: EXCLUSIVE lock requested
-- 5: EXCLUSIVE lock with invalidation requested

Lock Compatibility

Compatible Operations

-- Multiple sessions can hold:
-- NULL locks simultaneously
-- SHARE locks simultaneously
-- Mixed NULL and SHARE locks

Incompatible Operations

-- Blocking scenarios:
-- EXCLUSIVE lock blocks all other lock requests
-- DDL operations require EXCLUSIVE locks
-- Compilation requires SHARE ROW EXCLUSIVE locks
-- Multiple sessions compiling same object

Common Use Cases

1. DDL Blocking Analysis

   • Identify DDL operations waiting for locks
   • Find sessions blocking DDL commands
   • Analyze ALTER TABLE or DROP operations
   • Support maintenance window planning

2. Parse Contention Diagnosis

   • Identify parsing bottlenecks
   • Analyze shared pool contention
   • Find objects causing parse waits
   • Support SQL tuning efforts

3. PL/SQL Compilation Issues

   • Diagnose package compilation waits
   • Identify dependency chain problems
   • Analyze recompilation cascades
   • Support development troubleshooting

4. Application Performance Issues

   • Find library cache related slowdowns
   • Identify locking patterns
   • Analyze session blocking chains
   • Support performance optimization

Advanced Analysis

DDL Operation Analysis

DDL Blocking Patterns

-- DDL operations typically:
-- Require EXCLUSIVE locks (mode 4 or 5)
-- Block all other operations on the object
-- Can cause widespread application waits
-- May indicate maintenance window issues

DDL Impact Assessment

-- Assess DDL impact:
-- Number of sessions affected
-- Types of operations blocked
-- Duration of blocking
-- Application impact scope

Parse Contention Analysis

Parse Lock Contention

-- Parse contention indicators:
-- Multiple sessions requesting SRX locks (mode 3)
-- Contention on packages or procedures
-- Shared pool pressure symptoms
-- Cursor sharing problems

Resolution Strategies

-- Parse contention solutions:
-- Optimize cursor sharing
-- Reduce hard parsing
-- Improve shared pool sizing
-- Implement connection pooling

Object Type Analysis

High-Contention Object Types

-- Objects prone to contention:
-- PACKAGE BODY: Compilation and execution
-- PROCEDURE/FUNCTION: Compilation conflicts
-- TABLE: DDL operations
-- VIEW: Definition changes

Contention Patterns

-- Common contention scenarios:
-- Package recompilation cascades
-- Table structure modifications
-- Procedure/function updates
-- Development environment conflicts

Troubleshooting Applications

Immediate Response

Session Identification

-- Identify problem sessions:
-- Sessions holding EXCLUSIVE locks
-- Long-running DDL operations
-- Sessions with high lock mode values
-- Users performing maintenance

Quick Resolution

-- Immediate actions:
-- Contact session owners
-- Consider session termination
-- Reschedule maintenance operations
-- Implement workarounds

Root Cause Analysis

Lock Chain Analysis

-- Analyze blocking chains:
-- Identify root blocking session
-- Map dependent sessions
-- Assess cascade effects
-- Plan resolution approach

Pattern Recognition

-- Identify recurring issues:
-- Frequent compilation conflicts
-- Regular DDL blocking
-- Application design problems
-- Maintenance scheduling issues

Prevention Strategies

Development Practices

-- Prevent library cache contention:
-- Coordinate development activities
-- Use separate schemas for development
-- Implement code review processes
-- Schedule maintenance windows

Application Design

-- Design considerations:
-- Minimize dynamic DDL
-- Use static SQL where possible
-- Implement proper cursor sharing
-- Consider connection pooling

Integration with Performance Monitoring

Real-Time Monitoring

Alert Configuration

-- Set up alerts for:
-- Library cache wait events
-- Long-running DDL operations
-- Parse contention thresholds
-- Application response degradation

Dashboard Integration

-- Dashboard components:
-- Current library cache waits
-- Top contended objects
-- Session blocking relationships
-- Lock mode distributions

Historical Analysis

Trend Monitoring

-- Track historical patterns:
-- Peak contention periods
-- Recurring problem objects
-- User activity correlations
-- Maintenance impact analysis

Capacity Planning

-- Support planning with:
-- Parse workload trends
-- Object usage patterns
-- Resource requirement forecasts
-- Maintenance window sizing

Performance Impact Assessment

Application Impact

Response Time Analysis

-- Assess performance impact:
-- Application response degradation
-- User experience effects
-- Transaction throughput reduction
-- Service level violations

Business Impact

-- Business considerations:
-- Critical application availability
-- Peak business hour effects
-- Customer service impact
-- Revenue implications

System Impact

Resource Utilization

-- System resource effects:
-- CPU utilization patterns
-- Memory allocation changes
-- I/O operation impacts
-- Network activity variations

Stability Considerations

-- System stability factors:
-- Cascade failure potential
-- Recovery time requirements
-- Backup operation impacts
-- Maintenance window constraints

Best Practices

Monitoring Strategy

Regular Monitoring

-- Monitoring schedule:
-- Real-time during peak hours
-- Scheduled checks during maintenance
-- Post-deployment validation
-- Incident response procedures

Proactive Detection

-- Early warning systems:
-- Automated contention detection
-- Performance threshold monitoring
-- Trend analysis alerts
-- Capacity planning reviews

Resolution Procedures

Incident Response

-- Response procedures:
-- Rapid session identification
-- Impact assessment protocols
-- Escalation procedures
-- Communication plans

Prevention Measures

-- Preventive actions:
-- Development coordination
-- Maintenance scheduling
-- Application design reviews
-- Performance testing

Related Scripts

• vlibrary.sql - Library cache statistics
• vlibcache.sql - Library cache analysis
• vsession.sql - Session analysis
• vlock.sql - General lock analysis

Security Considerations

Access Control

-- SYSDBA requirement considerations:
-- Limit access to authorized personnel
-- Implement proper authentication
-- Monitor privileged access
-- Maintain audit trails

Data Sensitivity

-- Information exposure:
-- Object names may be sensitive
-- User information disclosure
-- Application logic exposure
-- Security policy considerations

Automation and Scripting

Automated Detection

-- Automation opportunities:
-- Scheduled contention checks
-- Alert-driven analysis
-- Performance dashboard integration
-- Incident response automation

Custom Extensions

-- Script enhancements:
-- Add timing information
-- Include wait event details
-- Correlate with other metrics
-- Generate resolution suggestions

Summary

This script is essential for:

1. Library Cache Analysis - Real-time identification of library cache lock contention
2. DDL Blocking Detection - Finding and resolving DDL operation conflicts
3. Parse Contention Diagnosis - Identifying and resolving parse-related performance issues
4. Session Troubleshooting - Understanding session blocking relationships and impacts
5. Performance Optimization - Supporting systematic library cache performance improvement