StatsPack System Wait Events Analysis (psysev.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.

StatsPack System Wait Events Analysis

This script provides detailed analysis of system wait events from StatsPack snapshots, showing wait event trends over time with comprehensive statistics including waits per second, timeout percentages, and average wait times.

Script: psysev.sql

rem psysev.sql
rem
set lines 132
rem
col SNAP_ID format 999999 heading 'SNAP ID'
col snap_time format a15 heading 'SNAP TIME'
col EVENT format a35 heading 'EVENT'
col total_waits format b999,999,990 heading 'TOTAL WAITS'
col waits_per_sec format b99,990 heading 'WAITS|PER|SEC'
col timeout_pct format b990.0 heading 'TIMEOUT|PCT'
col time_waited format b999,999,999,990 heading 'TIME WAITED'
col avg_wait format 999,990.0 heading 'AVG|WAIT|TIME'
rem
define start_dt='&start_date'
define end_dt='&end_date'
define nam='&name'
rem
ttitle 'System Wait Events (&nam)'
rem
clear break
clear compute
rem
select /*+ ordered use_nl(e x b se sb) */
       e.snap_id,
       to_char(e.snap_time, 'MM/DD HH24:MI:SS') snap_time,
       se.event,
       se.total_waits - sb.total_waits total_waits,
       (se.total_waits - sb.total_waits)
      / ((e.snap_time - b.snap_time) * 24 * 60 * 60) waits_per_sec,
       (se.total_timeouts - sb.total_timeouts)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) )*100 timeout_pct,
       (se.time_waited - sb.time_waited) time_waited,
       (se.time_waited - sb.time_waited)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) ) avg_wait
from stats$snapshot e,
     stats$snapshot b,
     stats$system_event se,
     stats$system_event sb
where b.snap_id =
         (select max(x.snap_id)
      from stats$snapshot x
     where x.snap_id < e.snap_id
       and x.dbid = e.dbid)
  and b.dbid = e.dbid
  and se.snap_id = e.snap_id
  and se.dbid = e.dbid
  and sb.snap_id = b.snap_id
  and sb.dbid = b.dbid
  and e.snap_time >= nvl(to_date('&start_dt'), trunc(sysdate))
  and e.snap_time <= nvl(to_date('&end_dt'), sysdate) + 1
  and sb.event = se.event
  and upper(se.event) like upper('&nam')
order by
      e.snap_id -- desc
      , se.event
/
rem
undefine start_dt
undefine end_dt
undefine nam

What This Script Does

This script provides comprehensive StatsPack wait event analysis by:

• Historical Analysis: Examines wait events across multiple StatsPack snapshots
• Delta Calculations: Shows incremental changes between consecutive snapshots
• Rate Analysis: Calculates waits per second for activity intensity
• Timeout Analysis: Shows percentage of waits that timed out
• Performance Metrics: Provides average wait times for each event
• Flexible Filtering: Allows filtering by date range and event name pattern
• Timeline View: Orders results chronologically for trend analysis

Usage

-- Run the script and provide parameters when prompted
@psysev.sql
Enter value for start_date: 2024-01-15
Enter value for end_date: 2024-01-16
Enter value for name: %latch%

-- Analyze all wait events for today
@psysev.sql
Enter value for start_date: [Enter for today]
Enter value for end_date: [Enter for tomorrow]
Enter value for name: %

-- Focus on specific event types
@psysev.sql
Enter value for start_date: 2024-01-15 08:00
Enter value for end_date: 2024-01-15 18:00
Enter value for name: db file%

Required Privileges

• SELECT privilege on StatsPack tables:
  • STATS$SNAPSHOT
  • STATS$SYSTEM_EVENT
• Typically requires DBA role or SELECT_CATALOG_ROLE
• StatsPack must be installed and collecting snapshots

Sample Output

                        System Wait Events (%latch%)

SNAP ID SNAP TIME       EVENT                           TOTAL WAITS WAITS  TIMEOUT TIME WAITED    AVG
                                                                   PER     PCT               WAIT
                                                                   SEC                       TIME
------- --------------- ------------------------------- ----------- ------ ------- ------------ -------
  12345 01/15 08:00:15   latch: cache buffers chains       1,234,567    145     2.3    45,678.90     0.04
  12345 01/15 08:00:15   latch: library cache               567,890     67     1.8    23,456.70     0.04
  12345 01/15 08:00:15   latch: shared pool                 234,567     28     0.5    12,345.60     0.05
  12346 01/15 08:15:20   latch: cache buffers chains       1,456,789    162     2.1    52,345.80     0.04
  12346 01/15 08:15:20   latch: library cache               645,321     72     1.9    26,789.40     0.04
  12346 01/15 08:15:20   latch: shared pool                 276,543     31     0.6    13,876.90     0.05
  12347 01/15 08:30:25   latch: cache buffers chains       1,678,901    178     2.0    58,912.60     0.04
  12347 01/15 08:30:25   latch: library cache               723,654     78     2.0    29,123.80     0.04
  12347 01/15 08:30:25   latch: shared pool                 318,765     34     0.7    15,234.50     0.05

Key Output Columns

Identification

Column	Description
SNAP ID	StatsPack snapshot identifier
SNAP TIME	Timestamp of the snapshot
EVENT	Name of the wait event

Activity Metrics

Column	Description
TOTAL WAITS	Number of waits for this event in the interval
WAITS PER SEC	Average waits per second during the interval
TIMEOUT PCT	Percentage of waits that timed out

Timing Metrics

Column	Description
TIME WAITED	Total time waited for this event (centiseconds)
AVG WAIT TIME	Average time per wait (centiseconds)

Understanding Wait Event Analysis

Activity Patterns

• High Waits Per Second: Indicates intense activity or contention
• Increasing Trends: May show growing performance issues
• Spike Patterns: Can indicate specific problematic periods

Timeout Analysis

• High Timeout %: May indicate severe contention or resource starvation
• Consistent Timeouts: Suggests ongoing configuration issues
• Zero Timeouts: Normal for most wait events

Performance Indicators

• Average Wait Times: Compare with expected values for each event type
• Time Waited Trends: Show impact on overall database performance
• Event Frequency: High-frequency events need optimization priority

Common Use Cases

1. Latch Contention Analysis

-- Focus on latch-related wait events
@psysev.sql
-- Parameters:
-- start_date: Yesterday
-- end_date: Today
-- name: %latch%

-- Analyze specific latch types
@psysev.sql
-- name: latch: cache buffers chains

2. I/O Performance Analysis

-- Analyze database file I/O waits
@psysev.sql
-- name: db file%

-- Focus on specific I/O types
@psysev.sql
-- name: db file sequential read

3. Lock and Enqueue Analysis

-- Examine locking issues
@psysev.sql
-- name: %enq%

-- Look at row-level locking
@psysev.sql
-- name: enq: TX%

4. Timeline Performance Analysis

-- Analyze business hours performance
@psysev.sql
-- start_date: 2024-01-15 08:00
-- end_date: 2024-01-15 18:00
-- name: %

StatsPack Configuration Requirements

Prerequisites

-- Verify StatsPack is installed
SELECT COUNT(*) FROM dba_tables WHERE table_name = 'STATS$SNAPSHOT';

-- Check snapshot frequency
SELECT
  MIN(snap_time) as earliest_snap,
  MAX(snap_time) as latest_snap,
  COUNT(*) as total_snapshots
FROM stats$snapshot;

-- Review snapshot intervals
SELECT
  snap_id,
  snap_time,
  ROUND((snap_time - LAG(snap_time) OVER (ORDER BY snap_id)) * 24 * 60, 1) as interval_minutes
FROM stats$snapshot
ORDER BY snap_id DESC;

Optimal Configuration

-- Recommended StatsPack settings
-- Snapshot every 15-30 minutes during business hours
-- Retain 7-30 days of history
-- Automatic snapshot collection enabled

Advanced Analysis Techniques

1. Wait Event Trending

-- Create trending report
WITH hourly_waits AS (
  SELECT
    TO_CHAR(e.snap_time, 'YYYY-MM-DD HH24') as hour,
    se.event,
    SUM(se.total_waits - sb.total_waits) as total_waits,
    AVG((se.time_waited - sb.time_waited) /
        DECODE((se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits))) as avg_wait
  FROM stats$snapshot e, stats$snapshot b, stats$system_event se, stats$system_event sb
  WHERE b.snap_id = (SELECT MAX(x.snap_id) FROM stats$snapshot x
                     WHERE x.snap_id < e.snap_id AND x.dbid = e.dbid)
    AND se.snap_id = e.snap_id AND sb.snap_id = b.snap_id
    AND se.event = sb.event
    AND e.snap_time >= SYSDATE - 7
  GROUP BY TO_CHAR(e.snap_time, 'YYYY-MM-DD HH24'), se.event
)
SELECT * FROM hourly_waits
WHERE event IN ('db file sequential read', 'db file scattered read', 'log file sync')
ORDER BY hour, event;

2. Top Wait Events by Period

-- Find top wait events for each time period
WITH ranked_events AS (
  SELECT
    e.snap_id,
    e.snap_time,
    se.event,
    se.total_waits - sb.total_waits as waits,
    se.time_waited - sb.time_waited as time_waited,
    ROW_NUMBER() OVER (PARTITION BY e.snap_id ORDER BY (se.time_waited - sb.time_waited) DESC) as rn
  FROM stats$snapshot e, stats$snapshot b, stats$system_event se, stats$system_event sb
  WHERE b.snap_id = (SELECT MAX(x.snap_id) FROM stats$snapshot x
                     WHERE x.snap_id < e.snap_id AND x.dbid = e.dbid)
    AND se.snap_id = e.snap_id AND sb.snap_id = b.snap_id
    AND se.event = sb.event
)
SELECT snap_id, snap_time, event, waits, time_waited
FROM ranked_events
WHERE rn <= 5
ORDER BY snap_id, rn;

3. Performance Baseline Comparison

-- Compare current performance to baseline
SELECT
  'Baseline' as period,
  se.event,
  AVG(se.total_waits - sb.total_waits) as avg_waits,
  AVG((se.time_waited - sb.time_waited) /
      DECODE((se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits))) as avg_wait_time
FROM stats$snapshot e, stats$snapshot b, stats$system_event se, stats$system_event sb
WHERE e.snap_time BETWEEN DATE '2024-01-01' AND DATE '2024-01-07'
  AND b.snap_id = (SELECT MAX(x.snap_id) FROM stats$snapshot x
                   WHERE x.snap_id < e.snap_id AND x.dbid = e.dbid)
  AND se.snap_id = e.snap_id AND sb.snap_id = b.snap_id
  AND se.event = sb.event
GROUP BY se.event
HAVING AVG(se.total_waits - sb.total_waits) > 100;

Integration with Other Tools

AWR Comparison

-- For databases with AWR, compare with StatsPack results
-- Use similar queries against DBA_HIST_SYSTEM_EVENT
SELECT
  event_name,
  SUM(total_waits_delta) as total_waits,
  ROUND(SUM(time_waited_micro_delta)/1000000, 2) as time_waited_sec
FROM dba_hist_system_event
WHERE snap_id BETWEEN &begin_snap AND &end_snap
GROUP BY event_name
ORDER BY time_waited_sec DESC;

Real-time Monitoring

-- Compare with current wait events
SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event NOT LIKE 'SQL*Net%'
ORDER BY time_waited DESC;

Best Practices

Analysis Guidelines

1. Regular Monitoring: Run weekly reports for trending analysis
2. Baseline Establishment: Establish performance baselines during normal operations
3. Event Categorization: Group related events for pattern analysis
4. Time Period Focus: Analyze business hours separately from maintenance windows
5. Threshold Setting: Define acceptable wait time thresholds for key events

Performance Optimization

1. Top Events First: Focus on events with highest time waited
2. Trend Analysis: Look for increasing wait patterns over time
3. Correlation Analysis: Correlate wait spikes with application events
4. Root Cause Analysis: Use wait events to guide deeper investigation
5. Change Impact: Monitor wait events before and after changes

Integration with Other Scripts

This script works well with:

• Active Session Analysis (gvsess.sql) - For current session analysis
• AWR Reports (awrrpt.sql) - For modern AWR analysis
• Wait Event Analysis (vsysev.sql) - For current wait events
• System Statistics (systime.sql) - For broader system analysis

This script is essential for DBAs managing Oracle databases with StatsPack and provides crucial historical perspective on database performance trends.