System-Wide Event Statistics (vsysev.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 vsysev.sql script.

The Script

rem  vsysev.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'
col average_wait_ms format b999999.9 heading 'AVERAGE|WAIT|(ms)'
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,
       (time_waited_micro / total_waits)/ 1000 average_wait_ms
  from v$system_event
 order by event;

What This Script Does

This script provides a comprehensive system-wide view of all wait events that have occurred since database startup. It displays cumulative statistics including total waits, timeouts, time waited, and average wait times, making it essential for understanding overall database performance patterns and establishing performance baselines.

Key Features

• Complete Event Coverage: Shows all system wait events alphabetically
• Comprehensive Metrics: Includes waits, timeouts, and timing information
• Timeout Analysis: Calculates timeout percentages for each event
• Multiple Time Formats: Shows average wait time in both centiseconds and milliseconds
• Summary Totals: Provides overall system wait statistics

Usage

@vsysev.sql

No parameters required - shows all system events since database startup.

Required Privileges

SELECT on V$SYSTEM_EVENT

Sample Output

                                              System Events

EVENT                          TOTAL     TOTAL   TIME TIME        AVERAGE    AVERAGE
                               WAITS  TIMEOUTS    OUT WAITED       WAIT    WAIT
                                                  PCT                      (ms)
------------------------------ ------ --------- ----- ---------- -------- --------
SQL*Net break/reset to client      45         0   0.0        123     2.73     27.3
SQL*Net message from client   1,234,567        45   0.0  8,765,432     7.10     71.0
SQL*Net message to client     1,234,522         0   0.0     12,345     0.01      0.1
buffer busy waits                  567        12   2.1      1,234     2.18     21.8
control file parallel write       234         0   0.0        456     1.95     19.5
db file parallel write           1,234         0   0.0      2,345     1.90     19.0
db file scattered read          45,678         0   0.0    123,456     2.70     27.0
db file sequential read         89,123         0   0.0    234,567     2.63     26.3
direct path read                 5,678         0   0.0     12,345     2.17     21.7
enq: TX - row lock contention      234        45  19.2     56,789   242.68  2,426.8
latch: cache buffers chains        123         0   0.0        456     3.71     37.1
log file parallel write         8,765         0   0.0     17,530     2.00     20.0
log file sync                   23,456         0   0.0     46,912     2.00     20.0

                               ======= ========= ========== ========
sum                          2,643,826        102 9,272,895

Key Output Columns

• EVENT: Name of the wait event
• TOTAL WAITS: Cumulative number of waits for this event since startup
• TOTAL TIMEOUTS: Number of waits that resulted in timeouts
• TIME OUT PCT: Percentage of waits that timed out
• TIME WAITED: Total time waited for this event (centiseconds)
• AVERAGE WAIT: Average wait time per occurrence (centiseconds)
• AVERAGE WAIT (ms): Average wait time per occurrence (milliseconds)

Understanding System Wait Events

I/O Related Events

Single Block I/O

• db file sequential read: Index lookups, table access by rowid
• Good performance: < 10ms average
• Concerning: > 20ms average

Multi-Block I/O

• db file scattered read: Full table scans, index fast full scans
• Good performance: < 50ms average
• Concerning: > 100ms average

Control and Log File I/O

• log file sync: Commit processing
• log file parallel write: Redo log writing
• control file parallel write: Control file updates

Network Events

Client Communication

• SQL*Net message from client: Waiting for client to send request
• SQL*Net message to client: Sending results to client
• High values: Often indicates application or network issues

Concurrency Events

Locking

• enq: TX - row lock contention: Row-level locking
• High timeout %: Indicates blocking or deadlock issues

Latching

• latch: cache buffers chains: Buffer cache contention
• latch: shared pool: Shared pool contention

Performance Analysis

Baseline Establishment

-- Run vsysev.sql to establish baseline:
-- 1. During normal operations
-- 2. During peak load
-- 3. During batch processing
-- Compare results to identify patterns

Problem Identification

High Wait Times

-- Events to investigate if average wait > thresholds:
-- db file sequential read > 10ms: Storage issues
-- db file scattered read > 50ms: Storage or SQL issues
-- log file sync > 10ms: Redo performance issues

High Timeout Percentages

-- Events with timeout % > 5%:
-- Usually indicates resource contention
-- May suggest deadlock or blocking issues
-- Could indicate insufficient resources

System Health Indicators

Good Performance Signs

• Low average wait times for I/O events
• Minimal timeout percentages
• Reasonable total wait counts

Performance Concerns

• High average wait times
• Significant timeout percentages
• Rapidly growing wait counts

Common Use Cases

1. Performance Baseline

   • Establish normal system performance characteristics
   • Create baseline for comparison after changes
   • Document expected wait patterns

2. Problem Diagnosis

   • Identify system-wide performance issues
   • Find events contributing to poor performance
   • Correlate with user complaints

3. Capacity Planning

   • Understand system resource utilization
   • Plan for growth and scaling
   • Identify bottlenecks before they become critical

4. System Monitoring

   • Regular health checks
   • Trend analysis over time
   • Performance regression detection

Advanced Analysis

Wait Event Prioritization

-- Focus on events with:
-- 1. High total waits AND high average wait time
-- 2. High timeout percentages (> 5%)
-- 3. Events consuming significant total time waited

Trending Analysis

-- Run periodically and compare:
-- 1. Growth in total waits
-- 2. Changes in average wait times
-- 3. New events appearing
-- 4. Timeout percentage trends

Correlation Analysis

-- Correlate wait events with:
-- 1. Application workload patterns
-- 2. System resource utilization
-- 3. Storage performance metrics
-- 4. Network latency measurements

Performance Optimization

I/O Optimization

-- For high I/O wait events:
-- 1. Check storage subsystem performance
-- 2. Review SQL execution plans
-- 3. Consider index optimization
-- 4. Evaluate buffer cache sizing

Concurrency Optimization

-- For locking/latching events:
-- 1. Identify blocking sessions
-- 2. Review application transaction design
-- 3. Consider partitioning strategies
-- 4. Optimize commit frequency

System Tuning

-- Based on wait event patterns:
-- 1. Adjust memory allocations
-- 2. Optimize I/O subsystem
-- 3. Tune network configuration
-- 4. Review application design

Troubleshooting Common Patterns

High I/O Waits

• Check storage performance: IOPS, latency, throughput
• Review SQL efficiency: Execution plans, indexes
• Validate buffer cache: Hit ratios, sizing

Network Issues

• High SQL*Net waits: Check network latency, client performance
• Application delays: Review client-side processing
• Connection issues: Validate network infrastructure

Concurrency Problems

• High lock waits: Identify blocking sessions
• Latch contention: Review memory configuration
• Buffer busy waits: Check for hot blocks

Integration with Other Monitoring

AWR/Statspack Correlation

-- Compare with historical data:
-- AWR reports show wait event trends
-- Statspack provides historical baselines
-- Identify performance regression points

Real-Time Monitoring

-- Combine with current session analysis:
-- V$SESSION_WAIT for current activity
-- V$ACTIVE_SESSION_HISTORY for recent trends

Related Scripts

• dhsysev.sql - AWR system wait events analysis
• dhsysev-long.sql - Historical wait events with filtering
• vsessev.sql - Session-level wait events
• gvsessw.sql - Current session waits

Best Practices

1. Regular Monitoring

   • Run weekly during different load periods
   • Compare results over time
   • Document baseline performance

2. Focus Areas

   • Prioritize events with high time waited
   • Investigate events with increasing trends
   • Address high timeout percentages first

3. Integration

   • Use with other performance scripts
   • Correlate with application metrics
   • Include in regular health checks