Historical System Wait Events Analysis with Filtering (dhsysev_long.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 Oracle database administration functionality via the dhsysev-long.sql script.

The Script

rem psysev.sql
rem
set lines 132
rem
col SNAP_ID format 999999 heading 'SNAP ID'
col instance_number format 99 head 'INST'
col end_interval_time format a15 heading 'SNAP TIME'
col EVENT_NAME 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_micro format b999,999,990 heading 'TIME WAITED(ms)'
col avg_wait format 999,990.0 heading 'AVG|WAIT|TIME(ms)'
rem
define start_dt='&start_date'
define end_dt='&end_date'
define nam='&name'
define instno='&inst_no'
rem
ttitle 'System Wait Events (&nam)'
rem
clear break
clear compute
rem
select /*+ ordered use_hash(e x b se sb) */
       e.snap_id,
       to_char(e.end_interval_time, 'MM/DD HH24:MI') end_interval_time,
       e.instance_number,
       se.event_name,
       se.total_waits - sb.total_waits total_waits,
       -- (se.total_waits - sb.total_waits)
      -- / ((e.end_interval_time - b.end_interval_time) ) 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_micro - sb.time_waited_micro)/1000 time_waited_micro,
       (se.time_waited_micro - sb.time_waited_micro)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) )/1000 avg_wait
from dba_hist_snapshot e,
     dba_hist_snapshot b,
     dba_hist_system_event se,
     dba_hist_system_event sb
where b.snap_id =
         (select max(x.snap_id)
      from dba_hist_snapshot x
     where x.snap_id < e.snap_id
       and x.dbid = e.dbid
       and x.instance_number = e.instance_number)
  and b.dbid = e.dbid
  and b.instance_number = e.instance_number
  and se.snap_id = e.snap_id
  and se.dbid = e.dbid
  and se.instance_number = e.instance_number
  and sb.snap_id = b.snap_id
  and sb.dbid = b.dbid
  and sb.instance_number = b.instance_number
  and e.instance_number like nvl('&instno','%')
  and e.end_interval_time >= nvl(to_date('&start_dt'), trunc(sysdate))
  and e.end_interval_time <= nvl(to_date('&end_dt'), sysdate) + 1
  and sb.event_name = se.event_name
  and upper(se.event_name) like upper('db file sequential read%')
and  ( (se.time_waited_micro - sb.time_waited_micro)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) )/1000 ) > 8.2
order by
      end_interval_time
      , e.instance_number
      , se.event_name
/
rem
undefine start_dt
undefine end_dt
undefine nam
undefine instno

What This Script Does

This specialized performance analysis script focuses on historical “db file sequential read” wait events that exceed a critical 8.2 millisecond average wait time threshold. It analyzes AWR data to identify periods of poor single-block I/O performance, which often indicates storage latency issues or inefficient index access patterns.

Key Features

• Focused Analysis: Specifically targets db file sequential read events
• Performance Threshold: Filters for average wait times > 8.2ms (indicating potential issues)
• Historical Perspective: Uses AWR data for trend analysis over time
• Multi-Instance Support: Works across RAC instances
• Incremental Calculations: Shows wait event deltas between snapshots

Usage

Run the script and provide filter criteria when prompted:

@dhsysev_long.sql

Input Parameters:

• Start Date: Beginning of analysis period (YYYY-MM-DD format)
• End Date: End of analysis period (YYYY-MM-DD format)
• Name: Description for the report title
• Instance Number: Specific instance or % for all instances

Required Privileges

SELECT on DBA_HIST_SNAPSHOT
SELECT on DBA_HIST_SYSTEM_EVENT

Sample Output

                        System Wait Events (Production Database Analysis)

SNAP ID  INST SNAP TIME       EVENT                               TOTAL WAITS  TIMEOUT  TIME WAITED(ms)  AVG
                                                                               PCT                      WAIT
                                                                                                       TIME(ms)
------- ---- --------------- ----------------------------------- ------------ ------- --------------- --------
  12456    1 03/15 09:00     db file sequential read                   125,847     0.0         1,547,234     12.3
  12457    1 03/15 10:00     db file sequential read                   145,221     0.0         1,892,156     13.0
  12458    1 03/15 11:00     db file sequential read                   167,845     0.0         2,234,891     13.3
  12459    1 03/15 12:00     db file sequential read                   189,432     0.0         2,567,123     13.5
  12460    1 03/15 13:00     db file sequential read                   201,567     0.0         2,845,234     14.1

Key Output Columns

• SNAP ID: AWR snapshot identifier
• INST: RAC instance number
• SNAP TIME: Snapshot timestamp (MM/DD HH24:MI format)
• EVENT: Wait event name (filtered to db file sequential read)
• TOTAL WAITS: Number of wait events in the snapshot interval
• TIMEOUT PCT: Percentage of waits that timed out
• TIME WAITED(ms): Total time waited in milliseconds for the interval
• AVG WAIT TIME(ms): Average wait time per event in milliseconds

Understanding the 8.2ms Threshold

Why 8.2ms Matters

• Industry Benchmark: Generally accepted threshold for acceptable single-block I/O performance
• Storage Performance: Indicates potential storage subsystem issues
• Index Efficiency: High values suggest inefficient index access patterns
• Capacity Planning: Helps identify when storage upgrades are needed

Performance Baselines

• < 5ms: Excellent I/O performance (SSD/high-performance storage)
• 5-8ms: Good performance (acceptable for most workloads)
• 8-15ms: Warning zone (monitor for trends)
• > 15ms: Poor performance (requires immediate attention)

Performance Analysis

Trend Identification

-- Look for patterns in the output:
-- Increasing avg_wait over time: Performance degradation
-- Consistent high values: Chronic storage issues
-- Spikes during specific times: Load-related problems

Root Cause Analysis

High Average Wait Times May Indicate:

• Storage hardware issues or overload
• Inefficient SQL causing excessive index lookups
• Missing or inappropriate indexes
• Buffer cache too small for workload
• Network issues (for remote storage)

Correlation Analysis

• Compare with system load: High CPU + high I/O wait = resource contention
• Check concurrent processes: Backup/batch jobs affecting I/O
• Review SQL performance: Identify inefficient queries driving I/O

Common Use Cases

1. Storage Performance Monitoring

   • Establish baseline I/O performance
   • Monitor storage degradation over time
   • Validate storage upgrades

2. Performance Regression Analysis

   • Identify when I/O performance degraded
   • Correlate with application changes
   • Track performance after tuning efforts

3. Capacity Planning

   • Understand I/O patterns during peak periods
   • Plan for storage infrastructure upgrades
   • Validate SLA compliance

4. Troubleshooting

   • Isolate I/O performance issues
   • Identify specific time periods with problems
   • Support storage vendor analysis

Advanced Analysis

Extending the Analysis

-- Modify the script to include other I/O events:
and upper(se.event_name) like upper('db file%read%')

-- Adjust the threshold for different environments:
and avg_wait > 5.0  -- For high-performance SSD environments
and avg_wait > 12.0 -- For traditional spinning disk environments

Multi-Event Analysis

-- Remove the event filter to see all high-latency events:
-- and upper(se.event_name) like upper('db file sequential read%')

Troubleshooting

No Results Returned

• Check if db file sequential read events exist in the time period
• Verify the 8.2ms threshold isn’t too high for your environment
• Ensure AWR data exists for the specified date range

Very Few Results

• Good news - your I/O performance is likely acceptable
• Consider lowering the threshold to 5-6ms for more sensitivity

Too Many Results

• Indicates chronic I/O performance issues
• Focus on periods with highest average wait times
• Consider storage infrastructure evaluation

Related Scripts

• dhsysev.sql - General AWR system wait events
• gvio.sql - Real-time I/O statistics
• vfile.sql - File I/O statistics
• awrrpt.sql - Complete AWR reports