Global System Events Analysis (gvsysev.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 analysis of system wait events across all RAC instances, showing cumulative wait statistics since instance startup. It displays wait counts, timeout percentages, total time waited, and average wait times for each event type by instance. This is essential for RAC performance tuning, identifying cluster-wide bottlenecks, and understanding system resource contention patterns.

The Script

rem  gvsysev.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,
       inst_id,
       total_waits,
       total_timeouts,
       100 * total_timeouts / total_waits timeout_pct,
       time_waited,
       average_wait
  from gv$system_event
 order by event,
          inst_id;

Key Features

• RAC-Aware Analysis: Shows events across all instances in the cluster
• Comprehensive Metrics: Wait counts, timeouts, timing, and averages
• Timeout Analysis: Calculates timeout percentages for each event
• Instance Comparison: Compare event patterns across cluster nodes
• Statistical Summaries: Provides report totals for key metrics
• Performance Trending: Historical view since instance startup
• Bottleneck Identification: Highlights high-impact wait events

Usage

@gvsysev.sql

No parameters required - analyzes all system events across all instances.

Required Privileges

SELECT on GV$SYSTEM_EVENT

Sample Output

                                              System Events

EVENT                          INST_ID      TOTAL      TOTAL TIME    TIME        AVERAGE
                                            WAITS   TIMEOUTS  OUT WAITED        WAIT
                                                              PCT
------------------------------ ------- --------- ---------- ---- ---------- ---------
ASM file metadata operation          1     1,234          0  0.0      45.67      0.04
                                     2     1,456          0  0.0      52.34      0.04

SQL*Net break/reset to client       1    12,345          0  0.0       0.23      0.00
                                     2    15,678          0  0.0       0.34      0.00

SQL*Net message from client          1 1,234,567      5,432  0.4  45,678.90     37.01
                                     2 1,456,789      6,234  0.4  52,345.67     35.93

SQL*Net message to client            1 1,234,890          0  0.0      12.34      0.00
                                     2 1,457,123          0  0.0      15.67      0.00

db file scattered read               1   456,789          0  0.0   8,901.23     19.49
                                     2   523,456          0  0.0  10,234.56     19.55

db file sequential read              1 2,345,678          0  0.0   3,456.78      1.47
                                     2 2,678,901          0  0.0   3,901.23      1.46

enq: TX - row lock contention        1    12,345         45  0.4   2,345.67    190.05
                                     2     8,901         23  0.3   1,789.12    201.02

log file sync                        1   234,567          0  0.0     456.78      1.95
                                     2   267,890          0  0.0     523.45      1.95
                                                   ---------- ---------- ----------
sum                                           12,567,432     11,734 134,567.89

Key Output Columns

• EVENT: Wait event name
• INST_ID: RAC instance ID
• TOTAL WAITS: Total number of waits for this event since startup
• 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 System Wait Events

Event Categories

Application Events

-- Application-related events:
-- SQL*Net message from client: User think time
-- SQL*Net message to client: Network send time
-- SQL*Net break/reset: Connection management
-- enq: TX - row lock contention: Lock conflicts

I/O Events

-- I/O-related events:
-- db file sequential read: Single block reads
-- db file scattered read: Multi-block reads
-- direct path read: Direct I/O operations
-- log file sync: Redo log synchronization

RAC Events

-- RAC-specific events:
-- gc current block busy: Global cache contention
-- gc buffer busy acquire: Cluster coordination
-- gc cr block busy: Consistent read conflicts
-- gcs remote message: Inter-instance messaging

System Events

-- System-level events:
-- latch: free: Latch contention
-- library cache lock: Parse contention
-- buffer busy waits: Buffer access conflicts
-- free buffer waits: Buffer shortage

Performance Interpretation

High-Impact Events

-- Focus on events with:
-- High total wait time
-- High average wait times
-- Significant timeout percentages
-- Unusual patterns across instances

Instance Comparison

-- Compare instances for:
-- Uneven wait distribution
-- Instance-specific bottlenecks
-- Load balancing effectiveness
-- Configuration differences

Common Use Cases

1. RAC Performance Analysis

   • Compare performance across cluster instances
   • Identify cluster-wide bottlenecks
   • Analyze load distribution patterns
   • Support cluster optimization

2. Wait Event Analysis

   • Identify top wait events by time
   • Analyze timeout patterns
   • Understand system contention
   • Plan performance improvements

3. Capacity Planning

   • Monitor resource utilization trends
   • Assess cluster scalability
   • Plan infrastructure upgrades
   • Support growth planning

4. Troubleshooting

   • Diagnose performance problems
   • Investigate cluster issues
   • Analyze system behavior
   • Support incident resolution

Advanced Analysis

Timeout Analysis

High Timeout Rates

-- High timeout percentages indicate:
-- Resource contention
-- System overload
-- Configuration issues
-- Network problems (for cluster events)

Timeout Pattern Analysis

-- Analyze timeout patterns:
-- Events with consistent timeouts
-- Instance-specific timeout issues
-- Correlation with system load
-- Impact on application performance

Instance Load Distribution

Load Balancing Assessment

-- Evaluate load distribution:
-- Compare wait counts across instances
-- Identify uneven workload distribution
-- Assess service routing effectiveness
-- Plan workload optimization

Instance-Specific Issues

-- Identify instance problems:
-- Disproportionate wait times
-- Instance-specific events
-- Hardware or configuration differences
-- Resource availability variations

Performance Trending

Historical Analysis

-- Since startup trending:
-- Wait event growth patterns
-- Performance degradation indicators
-- System capacity trends
-- Peak usage identification

Baseline Establishment

-- Establish performance baselines:
-- Normal wait event patterns
-- Expected timeout rates
-- Typical instance distribution
-- Performance benchmarks

RAC-Specific Analysis

Cluster Coordination Events

Global Cache Events

-- Analyze cluster cache events:
-- gc current block busy
-- gc buffer busy acquire
-- gc cr block busy
-- gcs remote message

Interconnect Performance

-- Assess interconnect efficiency:
-- High latency indicators
-- Bandwidth utilization
-- Message passing overhead
-- Network configuration impact

Instance Affinity

Workload Distribution

-- Analyze workload patterns:
-- Application instance affinity
-- Service-based routing
-- Connection distribution
-- Resource utilization balance

Performance Optimization

-- Optimization opportunities:
-- Instance-specific tuning
-- Service configuration
-- Connection pooling
-- Workload balancing

Performance Tuning Applications

Top Wait Events

I/O Optimization

-- For high I/O waits:
-- Optimize storage configuration
-- Improve SQL efficiency
-- Consider indexing strategies
-- Plan storage upgrades

Lock Contention

-- For lock-related waits:
-- Analyze application design
-- Optimize transaction scope
-- Consider partitioning
-- Review lock timeout settings

Network Issues

-- For network-related waits:
-- Optimize interconnect configuration
-- Review network hardware
-- Analyze bandwidth utilization
-- Consider network upgrades

Cluster Optimization

Service Management

-- Optimize service configuration:
-- Instance preference settings
-- Load balancing methods
-- Connection pooling
-- Failover configuration

Resource Allocation

-- Balance resource allocation:
-- CPU and memory distribution
-- I/O load balancing
-- Network bandwidth allocation
-- Storage access patterns

Integration with Monitoring

Automated Monitoring

Alert Configuration

-- Set up automated alerts for:
-- High timeout percentages
-- Unusual wait time increases
-- Instance-specific issues
-- Performance degradation

Dashboard Integration

-- Dashboard components:
-- Top wait events by time
-- Instance comparison charts
-- Timeout trend analysis
-- Performance health indicators

Historical Tracking

Trend Analysis

-- Track performance trends:
-- Wait event evolution
-- Instance performance patterns
-- Capacity utilization growth
-- Seasonal variations

Capacity Planning

-- Support capacity planning:
-- Resource requirement forecasting
-- Scalability assessment
-- Infrastructure planning
-- Performance projections

Best Practices

Regular Monitoring

Monitoring Schedule

-- Establish monitoring routine:
-- Daily wait event review
-- Weekly trend analysis
-- Monthly capacity assessment
-- Quarterly optimization review

Performance Baselines

-- Maintain performance baselines:
-- Normal operation patterns
-- Peak usage characteristics
-- Seasonal variations
-- Growth trends

Analysis Methodology

Systematic Approach

-- Analysis framework:
-- Focus on high-impact events
-- Compare across instances
-- Analyze timeout patterns
-- Correlate with system metrics

Root Cause Analysis

-- Investigation process:
-- Identify performance symptoms
-- Analyze wait event patterns
-- Correlate with system changes
-- Develop optimization plans

Related Scripts

• vsysevw.sql - Single-instance system events
• gvsess.sql - Global session analysis
• dhsysev.sql - Historical system events from AWR
• wait_group.sql - Wait events grouped by category

Troubleshooting Tips

High Wait Times

Investigation Steps

-- For high wait times:
-- Identify contributing factors
-- Analyze system resource usage
-- Review configuration settings
-- Check for recent changes

Resolution Strategies

-- Optimization approaches:
-- Tune underlying resources
-- Optimize application code
-- Adjust system parameters
-- Implement hardware improvements

Timeout Issues

Common Causes

-- Timeout root causes:
-- Resource exhaustion
-- System overload
-- Configuration problems
-- Hardware limitations

Resolution Methods

-- Address timeout issues:
-- Increase resource allocation
-- Optimize resource usage
-- Adjust timeout parameters
-- Implement load balancing

Summary

This script is essential for:

1. RAC Performance Analysis - Understanding cluster-wide performance characteristics
2. Wait Event Analysis - Identifying and analyzing system bottlenecks
3. Capacity Planning - Monitoring resource utilization and planning growth
4. Troubleshooting - Diagnosing performance issues and system problems
5. Optimization Planning - Supporting systematic performance improvement efforts