All Session Waits with Parameters (vsesswa.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 queries Oracle views to provide database information via the vsesswa.sql script.

The Script

rem  vsesswa.sql
rem
set linesize 132
rem
ttitle 'Session Waits'
rem
col event format a29 heading 'EVENT'
col sid format 9999 heading 'SID'
col seq# format 99999 heading 'SEQ'
col wait_time format b999 heading 'WAIT|TIME'
col seconds_in_wait format b999999 heading 'SECONDS|IN WAIT'
col state format a17 heading 'STATE'
col parm1 format a20 heading 'PARM1'
col parm2 format a20 heading 'PARM2'
col parm3 format a20 heading 'PARM3'
col parameters format a70 heading 'PARAMETERS'
rem
break on event
rem
select event, sid, seq#,
       wait_time,
       seconds_in_wait,
/*
       state,
       p1text, p1, p1raw,
       p2text, p2, p2raw,
       p3text, p3, p3raw
       p1text || ' = ' || p1 parm1,
       p2text || ' = ' || p2 parm2,
       p3text || ' = ' || p3 parm3
*/
       decode( p1text, null, null,
               p1text || ' = ' || p1 ) ||
       decode( p2text, null, null,
               ', ' || p2text || ' = ' || p2 ) ||
       decode( p3text, null, null,
               ', ' || p3text || ' = ' || p3 )
       parameters
  from v$session_wait
 where event not in ( 'SQL*Net message from client' )
 order by event, p1, p2;
rem
set linesize 80

What This Script Does

This script provides a comprehensive view of all current session waits with minimal filtering, showing only those events that are not SQL*Net client waits. Unlike its more restrictive counterpart (vsessw.sql), this script includes background process waits and idle events, making it useful for complete system wait analysis and understanding all database activity.

Key Features

• Comprehensive Coverage: Shows nearly all wait events occurring in the system
• Minimal Filtering: Only excludes SQL*Net message from client events
• Detailed Parameters: Displays formatted wait event parameters (P1, P2, P3)
• Event Grouping: Groups sessions by wait event type for organized analysis
• Background Visibility: Includes background process waits for complete picture
• Extended Parameter Display: 70-character width for parameter information

Usage

@vsesswa.sql

No parameters required - shows all current session waits except client network waits.

Required Privileges

SELECT on V$SESSION_WAIT

Sample Output

                                            Session Waits

EVENT                         SID   SEQ WAIT SECONDS
                                       TIME IN WAIT PARAMETERS
----------------------------- ---- ----- ---- ------- ----------------------------------------------------------------------
ASM background timer            3  1001   -1       0
buffer busy waits             145  1234    0       5 file# = 4, block# = 123456, class# = 1
                              156  2345    0       3 file# = 4, block# = 123456, class# = 1

class slave wait               8  2001   -1       0

db file scattered read        234  4567   -1       0 file# = 8, first dba = 2097152, block cnt = 8
                              267  5678   -1       0 file# = 12, first dba = 3145728, block cnt = 16

db file sequential read       189  6789   -1       0 file# = 5, first dba = 1048576, block cnt = 1

enq: TX - row lock contention 345  8901    0      45 name|mode = 54585858, usn<<16 | slot = 65537, sequence = 12345

jobq slave wait                12  3001   -1       0

latch: cache buffers chains   412  1357    0       1 address = 123456789, number = 98, tries = 0

log file sync                 298  2468   -1       0 buffer# = 123456
                              301  3579   -1       0 buffer# = 123457

pmon timer                      2  4001   -1       0

rdbms ipc message              4  5001   -1       0 timeout = 300

smon timer                     5  6001   -1       0 sleep time = 300

Key Differences from vsessw.sql

Inclusion of Background Events

This script shows background and maintenance waits that vsessw.sql filters out:

Background Process Waits

• pmon timer: Process monitor cleanup intervals
• smon timer: System monitor maintenance intervals
• rdbms ipc message: Inter-process communication waits
• ASM background timer: ASM maintenance operations

Job and Queue Waits

• jobq slave wait: Job queue background processing
• class slave wait: Classification background processing
• Queue Monitor Wait: Advanced queuing operations

Network Events (Partial)

• SQL*Net message to client: Still included (only client → database filtered)

Use Cases Comparison

vsesswa.sql (This Script) - Best For:

• Complete system analysis: Understanding all database activity
• Background process monitoring: Checking background operations
• Idle session analysis: Understanding session states
• System baseline establishment: Documenting normal wait patterns

vsessw.sql - Best For:

• Active performance issues: Focusing on performance problems
• User session troubleshooting: Excluding background noise
• Real-time problem diagnosis: Immediate issue identification
• Performance tuning: Targeting user-impacting waits

Understanding All Wait Events

Background Process Analysis

Timer-Based Events

-- Normal background operations:
-- pmon timer: Usually 3-second intervals
-- smon timer: Usually 5-minute intervals
-- ASM background timer: ASM maintenance cycles

Communication Events

-- Inter-process waits:
-- rdbms ipc message: Normal communication between processes
-- virtual circuit status: Network connection management
-- pipe get: Named pipe communication

Idle vs. Active Waits

Identifying Active Problems

• seconds_in_wait > 0: Sessions actively waiting
• wait_time = 0: Recently completed waits
• wait_time = -1: Currently waiting

Background vs. User Sessions

-- Correlate SID with session info:
SELECT s.sid, s.username, s.program, s.status, s.machine
FROM v$session s
WHERE s.sid IN (SELECT DISTINCT sid FROM v$session_wait)
ORDER BY s.username NULLS FIRST;

Complete System Analysis

Wait Event Categories

High-Priority Performance Events

• db file sequential read: Single block I/O
• db file scattered read: Multi-block I/O
• log file sync: Commit processing
• enq: TX - row lock contention: Row locking
• latch: cache buffers chains: Buffer cache contention

Medium-Priority System Events

• control file parallel write: Control file updates
• log file parallel write: Redo log writing
• direct path read/write: Direct I/O operations

Low-Priority Background Events

• pmon timer, smon timer: Normal background cycles
• rdbms ipc message: Inter-process communication
• jobq slave wait: Job queue processing

Baseline Establishment

Normal Operations Documentation

-- Run during different periods:
-- 1. Peak business hours
-- 2. Batch processing windows
-- 3. Maintenance periods
-- 4. Quiet periods

-- Document typical patterns:
-- Background process cycles
-- User session wait patterns
-- System maintenance windows

Advanced Analysis Techniques

Session Correlation

-- Identify session details for significant waits:
SELECT sw.sid, sw.event, sw.seconds_in_wait,
       s.username, s.program, s.machine, s.osuser,
       sql.sql_text
FROM v$session_wait sw,
     v$session s,
     v$sql sql
WHERE sw.sid = s.sid
  AND s.sql_id = sql.sql_id(+)
  AND sw.seconds_in_wait > 5
ORDER BY sw.seconds_in_wait DESC;

Wait Event Trending

-- Compare current waits with historical data:
-- Identify unusual patterns
-- Check for new types of waits
-- Monitor wait duration increases

System Load Assessment

-- Count sessions by wait event:
SELECT event, COUNT(*) session_count,
       AVG(seconds_in_wait) avg_wait_time,
       MAX(seconds_in_wait) max_wait_time
FROM v$session_wait
WHERE event != 'SQL*Net message from client'
GROUP BY event
ORDER BY session_count DESC;

Performance Impact Assessment

Critical Wait Indicators

Immediate Attention Required

• Multiple sessions: Same wait event with high seconds_in_wait
• Lock contention: enq: TX waits with increasing duration
• I/O bottlenecks: High wait times for db file events
• Latch contention: Multiple sessions waiting for same latch

System Health Indicators

• Background processes: Normal timer intervals
• Network activity: Reasonable SQL*Net patterns
• Job processing: Normal job queue activity

Troubleshooting Workflow

Initial Assessment

1. Run vsesswa.sql for complete picture
2. Identify high wait times (> 5 seconds)
3. Group similar events to find patterns
4. Correlate with user complaints

Deep Dive Analysis

1. Session identification using SID
2. SQL statement analysis for user sessions
3. Resource contention investigation
4. Historical comparison with baseline

Resolution Planning

1. Prioritize user-impacting waits
2. Address systemic issues (I/O, locking)
3. Plan maintenance for background issues
4. Monitor resolution effectiveness

Common Use Cases

1. Complete System Assessment

   • Full wait event inventory
   • Background process health check
   • System baseline documentation
   • Comprehensive monitoring

2. Problem Investigation

   • Initial problem assessment
   • Wait pattern identification
   • System-wide impact analysis
   • Root cause investigation

3. Performance Monitoring

   • Regular health checks
   • Trend analysis over time
   • Capacity planning data
   • System behavior documentation

4. Maintenance Planning

   • Background process monitoring
   • System maintenance windows
   • Impact assessment planning
   • Change validation

Integration with Other Tools

Monitoring Scripts Combination

-- Use in sequence:
-- 1. vsesswa.sql - Complete picture
-- 2. vsessw.sql - Active issues only
-- 3. vsession.sql - Session details
-- 4. vsql*.sql scripts - SQL analysis

Historical Analysis

-- Correlate with:
-- AWR/Statspack reports
-- V$SYSTEM_EVENT statistics
-- Database performance metrics
-- Application monitoring data

Related Scripts

• vsessw.sql - Active session waits with extensive filtering
• gvsessw.sql - RAC-aware session waits
• vsession.sql - Complete session information
• vsysev.sql - System-wide event statistics

Best Practices

1. Regular Monitoring

   • Include in daily health checks
   • Document normal patterns
   • Track unusual events

2. Analysis Approach

   • Start with complete picture (this script)
   • Focus on user-impacting events
   • Investigate patterns and trends

3. Documentation

   • Record baseline wait patterns
   • Document resolution actions
   • Track performance improvements