Skip to content
Oracle DBA Scripts & Database Utilities

Session-Level Wait Event Statistics (vsessev.sql)

What This Script Does

Section titled “What This Script Does”

This script queries Oracle views to provide database information via the vsessev.sql script.

The Script

Section titled “The Script”
rem  vsessev.sql
rem
ttitle 'Session Events'
rem
col sid format 9999 heading 'SID'
col event format a35 heading 'EVENT'
col total_waits format 9999999 heading 'TOTAL|WAITS'
col total_timeouts format b999999 heading 'TOTAL|TIMEOUTS'
col timeout_pct format b999.9 heading 'TIME|OUT|PCT'
col time_waited format b999999999 heading 'TIME|WAITED'
col average_wait format b999999.9 heading 'AVERAGE|WAIT'
rem
break on report on event
compute sum of total_waits total_timeouts time_waited on report
rem
select event,
       sid,
       total_waits,
       total_timeouts,
       100 * total_timeouts / total_waits timeout_pct,
       time_waited,
       average_wait
  from v$session_event
 where sid like nvl('&sid','%')
   and event like nvl('&event','%')
 order by event, sid;

What This Script Does

Section titled “What This Script Does”

This script provides detailed wait event statistics at the session level, showing comprehensive information about what each session has been waiting for since it started. It calculates timeout percentages and average wait times, making it essential for session-specific performance analysis and troubleshooting.

Key Features

Section titled “Key Features”
  • Session-Specific Analysis: Focus on individual sessions or session patterns
  • Complete Wait Statistics: Shows all wait events for selected sessions
  • Timeout Analysis: Calculates timeout percentages for each event
  • Performance Metrics: Includes total waits, time waited, and average wait times
  • Flexible Filtering: Filter by session ID and/or event type
  • Aggregated Reporting: Provides totals and summaries

Usage

Section titled “Usage”

Run the script and provide filter criteria when prompted:

@vsessev.sql

Input Parameters:

  • SID: Session ID (specific session or % for all sessions)
  • Event: Event name pattern (specific event or % for all events)

Required Privileges

Section titled “Required Privileges”
SELECT on V$SESSION_EVENT

Sample Output

Section titled “Sample Output”
                                           Session Events


EVENT                               SID TOTAL  TOTAL TIME  TIME    AVERAGE
                                        WAITS TIMEOUTS OUT WAITED    WAIT
                                                     PCT
----------------------------------- ---- ------ ------- ---- -------- --------
SQL*Net message from client         156    125       0  0.0   875432   7003.5
SQL*Net message to client           156    125       0  0.0      234      1.9
buffer busy waits                    156      8       0  0.0     1250    156.3
                                     234     12       0  0.0     2100    175.0
                                         ------  ------- -------- --------
sum                                          20       0     3350


db file scattered read              156     45       0  0.0    15678    348.4
                                    234     67       0  0.0    23456    350.1
                                         ------  ------- -------- --------
sum                                         112       0    39134


db file sequential read             156    234       0  0.0    12345     52.7
                                    234    456       0  0.0    28901     63.4
                                         ------  ------- -------- --------
sum                                         690       0    41246


enq: TX - row lock contention       156      3       1 33.3     8750   2916.7
                                         ------  ------- -------- --------
sum                                           3       1     8750


log file sync                       156     89       0  0.0     2345     26.3
                                    234    156       0  0.0     4567     29.3
                                         ------  ------- -------- --------
sum                                         245       0     6912


                                         ====== ======= ======== ========
sum                                        1070       1    99342

Key Output Columns

Section titled “Key Output Columns”
  • EVENT: Name of the wait event
  • SID: Session identifier
  • TOTAL WAITS: Number of times the session waited for this event
  • TOTAL TIMEOUTS: Number of waits that timed out
  • TIME OUT PCT: Percentage of waits that resulted in timeouts
  • TIME WAITED: Total time waited for this event (centiseconds)
  • AVERAGE WAIT: Average wait time per occurrence (centiseconds)

Understanding Session Wait Patterns

Section titled “Understanding Session Wait Patterns”

Common Wait Event Categories

Section titled “Common Wait Event Categories”
Section titled “Network Related”
  • SQL*Net message from client: Waiting for client to send next request
  • SQL*Net message to client: Time spent sending data to client
  • High values: Often indicate network latency or client processing delays

I/O Operations

Section titled “I/O Operations”
  • db file sequential read: Single-block reads (typically index access)
  • db file scattered read: Multi-block reads (typically table scans)
  • High values: May indicate storage performance issues or inefficient SQL

Concurrency and Locking

Section titled “Concurrency and Locking”
  • enq: TX - row lock contention: Waiting for row locks
  • buffer busy waits: Contention for specific database blocks
  • High values: Indicate blocking or hot block issues

System Operations

Section titled “System Operations”
  • log file sync: Waiting for commit operations to complete
  • latch: cache buffers chains: Memory structure contention
  • High values: May indicate system resource constraints

Performance Analysis

Section titled “Performance Analysis”

Identifying Performance Issues

Section titled “Identifying Performance Issues”

High Average Wait Times

Section titled “High Average Wait Times”
-- Look for events with high AVERAGE WAIT values:
-- > 10ms for db file sequential read: Storage issues
-- > 50ms for db file scattered read: Storage or SQL issues
-- > 5ms for log file sync: Redo log performance issues

Timeout Analysis

Section titled “Timeout Analysis”
-- TIME OUT PCT > 0 indicates:
-- Resource contention or deadlocks
-- System overload
-- Configuration issues

Session Comparison

Section titled “Session Comparison”
-- Compare sessions experiencing similar workloads:
-- Significant differences may indicate:
-- - Different execution plans
-- - Resource contention for specific sessions
-- - Application-specific issues

Common Use Cases

Section titled “Common Use Cases”

  1. Session Performance Troubleshooting

    • Identify what a specific session is waiting for
    • Understand session-level performance bottlenecks
    • Compare performance across similar sessions

  2. Application Performance Analysis

    • Analyze wait patterns for specific applications
    • Identify application-specific performance issues
    • Optimize application database interaction

  3. Blocking Investigation

    • Find sessions experiencing lock waits
    • Identify timeout patterns indicating blocking
    • Correlate with system-wide locking issues

  4. I/O Performance Analysis

    • Understand session-level I/O patterns
    • Identify sessions with excessive I/O waits
    • Correlate with storage performance metrics

Advanced Analysis

Section titled “Advanced Analysis”

Session Comparison

Section titled “Session Comparison”
-- Run for multiple sessions and compare:
-- Session A: Heavy I/O waits
-- Session B: Heavy lock waits
-- Session C: Network waits
-- Identify patterns and root causes

Historical Analysis

Section titled “Historical Analysis”
-- Combine with session statistics:
-- Run before and after specific operations
-- Compare cumulative statistics over time
-- Identify performance degradation patterns

Wait Event Correlation

Section titled “Wait Event Correlation”
-- Correlate high wait events with:
-- SQL statement patterns (V$SQL)
-- System resource usage (V$SYSSTAT)
-- Storage performance metrics

Filter Examples

Section titled “Filter Examples”

Analyze Specific Session

Section titled “Analyze Specific Session”
Enter value for sid: 156
Enter value for event: %

Focus on I/O Events

Section titled “Focus on I/O Events”
Enter value for sid: %
Enter value for event: db file%

Check Locking Issues

Section titled “Check Locking Issues”
Enter value for sid: %
Enter value for event: enq:%

Network Performance

Section titled “Network Performance”
Enter value for sid: %
Enter value for event: SQL*Net%

Troubleshooting Session Issues

Section titled “Troubleshooting Session Issues”

High I/O Waits

Section titled “High I/O Waits”
  • Check SQL execution plans: Look for full table scans
  • Review indexing strategy: Missing or inefficient indexes
  • Storage performance: Validate storage subsystem performance

Lock Contention

Section titled “Lock Contention”
  • Identify blocking sessions: Use V$LOCK and V$SESSION
  • Review application logic: Check for long transactions
  • Optimize commit frequency: Reduce lock duration

Network Issues

Section titled “Network Issues”
  • Client performance: Check client-side processing
  • Network infrastructure: Validate network configuration
  • Result set size: Consider data volume being transferred

Integration with Other Scripts

Section titled “Integration with Other Scripts”

Finding Session Details

Section titled “Finding Session Details”
-- After identifying problematic sessions:
SELECT username, program, module, sql_id
FROM v$session
WHERE sid = 156;

SQL Analysis

Section titled “SQL Analysis”
-- Analyze SQL for sessions with high waits:
SELECT sql_text, executions, buffer_gets
FROM v$sql
WHERE sql_id IN (SELECT sql_id FROM v$session WHERE sid = 156);
Section titled “Related Scripts”