Active Session Waits with Parameters (vsessw.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 vsessw.sql script.

The Script

rem  vsessw.sql
rem
set linesize 132
rem
ttitle 'Session Waits'
rem
col event format a30 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 a65 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 ( 'pmon timer',
                      'rdbms ipc message',
                      'wakeup time manager',
                      'smon timer',
                      'WMON goes to sleep',
                      'pipe get',
                      'Null event',
                      'SQL*Net message from client' ,
                      'SQL*Net message to client',
                      'virtual circuit status',
                      'Queue Monitor Wait',
                      'queue messages',
                      'jobq slave wait',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'ASM background timer',
'class slave wait')
 order by event, p1, p2;

What This Script Does

This script provides real-time monitoring of active Oracle sessions that are currently experiencing wait events, filtering out idle background events to focus on sessions with performance issues. It displays detailed wait parameters in a formatted way, making it an essential tool for troubleshooting active database performance problems.

Key Features

• Active Waits Only: Filters out idle background events and system maintenance waits
• Detailed Parameters: Shows formatted wait event parameters (P1, P2, P3)
• Real-Time Monitoring: Captures current session waits as they occur
• Grouped Display: Groups sessions by wait event type for easier analysis
• Timing Information: Shows wait time and seconds in wait for each session
• Comprehensive Filtering: Excludes over 15 types of idle/background events

Usage

@vsessw.sql

No parameters required - shows all current active session waits.

Required Privileges

SELECT on V$SESSION_WAIT

Sample Output

                                            Session Waits

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

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
                                203  7890   -1       0 file# = 6, first dba = 1572864, block cnt = 1

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

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
                                315  4680   -1       0 buffer# = 123458

Key Output Columns

• EVENT: The wait event name
• SID: Session ID experiencing the wait
• SEQ: Sequence number of the wait (increments with each new wait)
• WAIT TIME: Wait time in centiseconds (-1 = currently waiting, 0 = wait just completed)
• SECONDS IN WAIT: How long the session has been waiting
• PARAMETERS: Formatted display of P1, P2, P3 parameters with descriptions

Understanding Wait Events and Parameters

I/O Related Waits

db file sequential read

• file#: Database file number
• first dba: Data block address of the first block
• block cnt: Number of blocks (usually 1 for sequential reads)
• Indicates: Single block I/O operations (index lookups, ROWID access)

db file scattered read

• file#: Database file number
• first dba: Data block address of the first block
• block cnt: Number of blocks being read
• Indicates: Multi-block I/O operations (full table scans, index fast full scans)

Concurrency Waits

buffer busy waits

• file#: Database file number
• block#: Block number experiencing contention
• class#: Buffer class (1 = data block, 4 = segment header, etc.)
• Indicates: Multiple sessions competing for the same buffer

enq: TX - row lock contention

• name|mode: Lock name and mode in hexadecimal
• usn<<16 | slot: Undo segment number and slot
• sequence: Undo record sequence number
• Indicates: Sessions waiting for row-level locks

Redo and Commit Waits

log file sync

• buffer#: Redo buffer number
• Indicates: Sessions waiting for redo to be written to disk (commit processing)

log file parallel write

• files: Number of redo log files being written
• blocks: Number of redo blocks being written
• requests: Number of I/O requests
• Indicates: LGWR writing redo to log files

Latch Waits

latch: cache buffers chains

• address: Memory address of the latch
• number: Latch number
• tries: Number of attempts to get the latch
• Indicates: Contention for buffer cache access

Common Use Cases

1. Real-Time Performance Troubleshooting

   • Identify sessions experiencing performance issues
   • Determine the specific resources being waited for
   • Correlate wait events with user complaints

2. Bottleneck Identification

   • Find the most common wait events
   • Identify specific files or blocks causing contention
   • Locate locking issues between sessions

3. Application Diagnosis

   • Understand application wait patterns
   • Identify inefficient SQL operations
   • Correlate waits with application functionality

4. System Monitoring

   • Continuous monitoring during peak periods
   • Baseline establishment for normal operations
   • Early warning system for performance degradation

Filtered Events Explanation

The script filters out these idle events to focus on active performance issues:

Background Process Events

• pmon timer, smon timer: Process monitor idle waits
• rdbms ipc message: Inter-process communication waits
• wakeup time manager: Timer-based background waits

Network Idle Events

• SQL*Net message from client: Waiting for client to send next request
• SQL*Net message to client: Client processing time
• virtual circuit status: Network connection status checks

Queue and Job Events

• Queue Monitor Wait: Advanced queuing idle waits
• jobq slave wait: Job queue background process waits
• Streams AQ events: Streams advanced queuing idle waits

Analysis and Troubleshooting

High-Impact Wait Events

Buffer Busy Waits

-- Multiple sessions on same block indicates:
-- 1. Hot blocks (frequently accessed data)
-- 2. Insufficient freelists for tables
-- 3. Segment header contention
-- 4. Right-hand index contention

Row Lock Contention

-- Blocking session analysis needed:
-- 1. Identify holding session
-- 2. Check transaction length
-- 3. Consider application design
-- 4. Review commit frequency

I/O Wait Events

-- Storage subsystem analysis:
-- 1. Check disk performance metrics
-- 2. Review file placement
-- 3. Consider I/O balancing
-- 4. Evaluate buffer cache sizing

Parameter Interpretation

File and Block Numbers

-- Convert file# and block# to identify objects:
SELECT owner, object_name, object_type
FROM dba_objects
WHERE data_object_id = (
  SELECT data_object_id
  FROM dba_extents
  WHERE file_id = &file_number
    AND &block_number BETWEEN block_id AND block_id + blocks - 1
);

Lock Name Decoding

-- TX lock name|mode interpretation:
-- 54585858 = 'TX' in hexadecimal
-- Mode indicates lock type (exclusive, share, etc.)

Advanced Usage

Continuous Monitoring

-- Run in loop for ongoing monitoring:
-- Check every 30 seconds during problem periods
-- Document patterns and timing
-- Correlate with application activity

Wait Event Correlation

-- Combine with other views:
-- V$SESSION for session details
-- V$SQL for current SQL statements
-- V$PROCESS for OS process information

Historical Analysis

-- Compare with:
-- V$SYSTEM_EVENT for cumulative statistics
-- AWR/Statspack for historical trends
-- Alert log for related error messages

Performance Optimization

Immediate Actions

1. Identify blocking sessions for lock waits
2. Check storage performance for I/O waits
3. Review SQL efficiency for high resource consumption
4. Consider index optimization for buffer busy waits

Long-term Solutions

1. Application design review for locking patterns
2. Storage optimization for I/O performance
3. Memory tuning for buffer cache efficiency
4. Index strategy review for access patterns

Related Scripts

• gvsessw.sql - RAC-aware session waits across all instances
• vsession.sql - Complete session information with waits
• vsessev.sql - Session-level wait event statistics
• vsysev.sql - System-wide wait event statistics

Integration with Monitoring

Alert Setup

• Monitor for specific wait events exceeding thresholds
• Alert on sustained lock contention
• Notify on unusual wait patterns

Trending

• Track wait event frequency over time
• Monitor average wait times
• Identify performance regression patterns

Automation

• Include in regular health check scripts
• Integrate with monitoring tools
• Schedule during peak usage periods