System-Wide Wait Events Analysis (vsysevw.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 system-wide wait event analysis by:

• Displaying all wait events experienced since database startup
• Ordering results by total time waited to highlight biggest bottlenecks
• Calculating timeout percentages to identify resource contention
• Showing average wait times for each event type
• Computing totals for aggregate system analysis

Script

rem  vsysevw.sql
rem
ttitle 'System Events'
rem
col event format a30 heading 'EVENT'
col total_waits format 999999999 heading 'TOTAL|WAITS'
col total_timeouts format b99999999 heading 'TOTAL|TIMEOUTS'
col timeout_pct format b999.9 heading 'TIME|OUT|PCT'
col time_waited format b9999999999 heading 'TIME|WAITED'
col average_wait format b999999.9 heading 'AVERAGE|WAIT'
rem
break on report
compute sum of total_waits total_timeouts time_waited on report
rem
select event,
       total_waits,
       total_timeouts,
       100 * total_timeouts / total_waits timeout_pct,
       time_waited,
       average_wait
  from v$system_event
 order by time_waited desc, total_waits desc;

Usage

SQL> @vsysevw.sql

Required Privileges

• SELECT on V$SYSTEM_EVENT

Sample Output

System Events

EVENT                          TOTAL    TOTAL    TIME TIME        AVERAGE
                               WAITS    TIMEOUTS OUT  WAITED      WAIT
                                                 PCT
------------------------------ -------- -------- ---- ---------- --------
SQL*Net message from client   4,567,890        0  0.0 45,678,901     10.0
db file sequential read        2,345,678        0  0.0 12,345,678      5.3
log file sync                  1,234,567       12  0.0  5,678,901      4.6
db file scattered read           789,012        0  0.0  4,567,890      5.8
direct path read                 456,789        0  0.0  2,345,678      5.1
latch: shared pool               234,567      1,23  0.5  1,234,567      5.3
enq: TX - row lock contention    123,456      5,67  4.6    890,123      7.2
log file switch completion        45,678       234  0.5    567,890     12.4
buffer busy waits                 34,567        89  0.3    345,678     10.0

                               -------- -------- ---- ---------- --------
sum                           9,831,704    7,325  0.1 73,640,337      7.5

Key Output Columns

• EVENT: Name of the wait event
• TOTAL WAITS: Number of times this event was waited for
• TOTAL TIMEOUTS: Number of times the wait timed out
• TIME OUT PCT: Percentage of waits that resulted in timeouts
• TIME WAITED: Total time spent waiting (in centiseconds)
• AVERAGE WAIT: Average wait time per occurrence (in centiseconds)

Understanding Wait Events

Top Wait Event Categories

Application-Related Events

• SQL*Net message from client: User think time (not a database issue)
• SQL*Net more data to client: Large result sets
• SQL*Net message to client: Network or client processing

I/O-Related Events

• db file sequential read: Single block reads (index access)
• db file scattered read: Multi-block reads (full scans)
• direct path read: Direct path operations (parallel, temp)
• log file sync: Redo log write waits

Concurrency Events

• enq: TX - row lock contention: Row-level locking
• latch: shared pool: Shared pool contention
• buffer busy waits: Block contention

Performance Analysis

Wait Event Prioritization

Events are ordered by TIME WAITED because:

• High frequency, low impact events may appear first by count
• Total time waited shows actual performance impact
• Focus tuning efforts on highest time consumers

Key Performance Indicators

-- Enhanced analysis with percentages and rates
SELECT event,
       total_waits,
       ROUND(time_waited/100, 2) time_waited_secs,
       ROUND(average_wait, 2) avg_wait_cs,
       ROUND(time_waited * 100 / SUM(time_waited) OVER(), 2) pct_total_time,
       ROUND(total_waits * 100 / SUM(total_waits) OVER(), 2) pct_total_waits
FROM v$system_event
WHERE time_waited > 0
ORDER BY time_waited DESC;

Baseline Comparisons

-- Compare current vs startup statistics
SELECT event,
       total_waits,
       time_waited/100 time_waited_secs,
       ROUND((SYSDATE - startup_time) * 24 * 60 * 60) uptime_secs,
       ROUND(total_waits / ((SYSDATE - startup_time) * 24 * 60 * 60), 2) waits_per_sec,
       ROUND((time_waited/100) / ((SYSDATE - startup_time) * 24 * 60 * 60), 4) wait_secs_per_sec
FROM v$system_event, v$instance
WHERE time_waited > 1000
ORDER BY time_waited DESC;

Common Performance Issues

High I/O Wait Times

Events: db file sequential read, db file scattered read

• Causes: Slow storage, insufficient buffer cache, inefficient SQL
• Solutions: Storage upgrade, memory tuning, SQL optimization

Concurrency Issues

Events: enq: TX - row lock contention, latch waits

• Causes: Application design, long transactions, hot blocks
• Solutions: Application tuning, transaction optimization

Log File Issues

Events: log file sync, log file switch completion

• Causes: Slow redo storage, inadequate log sizing
• Solutions: Faster redo storage, larger redo logs

Advanced Analysis

Wait Event Trends

-- Historical wait event tracking (requires snapshots)
CREATE TABLE wait_event_history (
    snap_time DATE,
    event VARCHAR2(64),
    total_waits NUMBER,
    time_waited NUMBER,
    average_wait NUMBER
);

-- Insert current snapshot
INSERT INTO wait_event_history
SELECT SYSDATE, event, total_waits, time_waited, average_wait
FROM v$system_event
WHERE time_waited > 0;

Time-Based Analysis

-- Calculate wait event rates since startup
SELECT event,
       total_waits,
       ROUND(time_waited/100, 2) total_wait_secs,
       ROUND(total_waits /
             ((SYSDATE - startup_time) * 86400), 2) waits_per_second,
       ROUND((time_waited/100) /
             ((SYSDATE - startup_time) * 86400), 6) wait_time_per_second
FROM v$system_event se, v$instance i
WHERE se.time_waited > 1000
ORDER BY wait_time_per_second DESC;

Resource Contention Analysis

-- Focus on contention-related events
SELECT event, total_waits, total_timeouts,
       ROUND(total_timeouts * 100 / total_waits, 2) timeout_pct,
       ROUND(time_waited/100, 2) time_waited_secs,
       ROUND(average_wait, 2) avg_wait_cs
FROM v$system_event
WHERE (event LIKE '%enq:%' OR event LIKE '%latch%' OR event LIKE '%contention%')
AND total_waits > 0
ORDER BY time_waited DESC;

Wait Event Categories

I/O Events Analysis

-- I/O-related wait events summary
SELECT 'I/O Events' category,
       SUM(total_waits) total_waits,
       ROUND(SUM(time_waited)/100, 2) total_wait_secs,
       ROUND(AVG(average_wait), 2) avg_wait_cs
FROM v$system_event
WHERE event IN ('db file sequential read', 'db file scattered read',
                'direct path read', 'direct path write',
                'control file sequential read', 'control file parallel write');

Network Events Analysis

-- Network-related events
SELECT event, total_waits,
       ROUND(time_waited/100, 2) time_waited_secs,
       ROUND(average_wait, 2) avg_wait_cs
FROM v$system_event
WHERE event LIKE '%SQL*Net%'
ORDER BY time_waited DESC;

Performance Tuning Insights

Top Tuning Opportunities

1. Identify highest TIME_WAITED events
2. Check if average wait times are reasonable
3. Look for high timeout percentages
4. Focus on events that can be tuned

Timeout Analysis

High timeout percentages indicate:

• Resource contention: Multiple sessions competing
• Configuration issues: Inadequate resource allocation
• Application problems: Poor connection management

Wait Time Patterns

• Consistent high waits: Systemic performance issues
• Spiky patterns: Batch jobs or peak load problems
• Growing trends: Capacity or scaling issues

Monitoring and Alerting

Performance Baselines

-- Establish baseline wait patterns
SELECT event,
       ROUND(AVG(time_waited)/100, 2) baseline_wait_secs,
       ROUND(AVG(average_wait), 2) baseline_avg_wait_cs
FROM wait_event_history
WHERE snap_time BETWEEN SYSDATE-7 AND SYSDATE-1
GROUP BY event
HAVING COUNT(*) > 10;

Automated Monitoring

-- Alert on unusual wait patterns
SELECT event, current_avg_wait, baseline_avg_wait,
       ROUND(((current_avg_wait - baseline_avg_wait) / baseline_avg_wait) * 100, 2) pct_increase
FROM (
    SELECT event, average_wait current_avg_wait,
           (SELECT AVG(average_wait) FROM wait_event_history h
            WHERE h.event = se.event
            AND snap_time > SYSDATE - 7) baseline_avg_wait
    FROM v$system_event se
    WHERE time_waited > 1000
)
WHERE baseline_avg_wait > 0
AND ((current_avg_wait - baseline_avg_wait) / baseline_avg_wait) > 0.5;

Integration with Other Metrics

Database Load Analysis

-- Combine with database time metrics
SELECT 'Database Activity' metric,
       ROUND(value/100, 2) value_seconds
FROM v$sys_time_model
WHERE stat_name = 'DB time'

UNION ALL

SELECT 'Wait Time' metric,
       ROUND(SUM(time_waited)/100, 2) value_seconds
FROM v$system_event

UNION ALL

SELECT 'CPU Time' metric,
       ROUND(value/100, 2) value_seconds
FROM v$sys_time_model
WHERE stat_name = 'DB CPU';

Best Practices

1. Regular Monitoring

   • Check wait events during different workload periods
   • Compare patterns over time
   • Establish performance baselines

2. Focus Areas

   • Prioritize by total time waited
   • Address events with high timeout percentages
   • Consider tunable vs non-tunable events

3. Action Planning

   • Document baseline wait patterns
   • Set up automated monitoring for deviations
   • Plan tuning efforts based on business impact

Related Scripts

• Session Wait Events
• I/O Wait Analysis
• RAC Global Wait Events