Daily AWR System Event Summary (dhsysevd.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 daily AWR system event analysis by:

• Aggregating wait events from AWR history by day
• Calculating delta values between snapshots for accurate metrics
• Filtering events by name pattern and date range
• Supporting RAC environments with instance filtering
• Showing timeout percentages and average wait times
• Focusing on significant events (>1000 waits)

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 b9,999,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) */
trunc(e.end_interval_time),
       se.event_name,
       sum(se.total_waits) - sum(sb.total_waits) total_waits,
       (sum(se.total_timeouts) - sum(sb.total_timeouts))
          / decode( (sum(se.total_waits) - sum(sb.total_waits)), 0, 1, (sum(se.total_waits) - sum(sb.total_waits)) )*100 timeout_pct,
       (sum(se.time_waited_micro) - sum(sb.time_waited_micro))/1000 time_waited_micro,
       (sum(se.time_waited_micro) - sum(sb.time_waited_micro))
          / decode( (sum(se.total_waits) - sum(sb.total_waits)), 0, 1, (sum(se.total_waits) - sum(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.total_waits > 1000
  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('&nam')
group by
trunc(e.end_interval_time),
       se.event_name
order by
trunc(e.end_interval_time)
      , se.event_name
/
rem
undefine start_dt
undefine end_dt
undefine nam
undefine instno

Usage

SQL> @dhsysevd.sql
Enter value for start_date: 01-JAN-2025
Enter value for end_date: 31-JAN-2025
Enter value for name: %file%
Enter value for inst_no: 1

Parameters

• start_date: Beginning date for analysis (DD-MON-YYYY format, defaults to today)
• end_date: End date for analysis (DD-MON-YYYY format, defaults to today)
• name: Event name pattern (% for all, specific pattern like ‘%file%’ for I/O events)
• inst_no: Instance number for RAC (% for all instances)

Required Privileges

• SELECT on DBA_HIST_SNAPSHOT
• SELECT on DBA_HIST_SYSTEM_EVENT
• Oracle Diagnostics Pack license required

Sample Output

System Wait Events (%file%)

TRUNC(E.END_IN EVENT                              TOTAL WAITS TIMEOUT TIME WAITED(ms)    AVG
               PCT                                                    WAIT
                                                                      TIME(ms)
-------------- ---------------------------------- ----------- ------- --------------- --------
01-JAN-25      db file parallel read                   12,456     0.0          34,567     2.8
               db file scattered read                 456,789     0.0       1,234,567     2.7
               db file sequential read              2,345,678     0.0       5,678,901     2.4
               log file switch completion                234    12.5          45,678   195.2

02-JAN-25      db file parallel read                   15,678     0.0          45,678     2.9
               db file scattered read                 512,345     0.0       1,456,789     2.8
               db file sequential read              2,567,890     0.0       6,234,567     2.4
               log file switch completion                345    15.3          67,890   196.8

Key Output Columns

• TRUNC(E.END_IN): Date of the measurements
• EVENT: Wait event name
• TOTAL WAITS: Number of waits during the day
• TIMEOUT PCT: Percentage of waits that timed out
• TIME WAITED(ms): Total time waited in milliseconds
• AVG WAIT TIME(ms): Average wait time per event

Understanding the Metrics

Wait Event Patterns

• Increasing waits: May indicate growing workload or degrading performance
• High timeout percentages: Suggest resource contention
• Rising average wait times: Point to infrastructure issues

Common Event Categories

• db file%: I/O related waits
• log file%: Redo log related waits
• enq:%: Lock/enqueue contention
• latch%: Memory structure contention

Daily Trend Analysis

Workload Patterns

-- Analyze weekly patterns for specific events
SELECT TO_CHAR(end_interval_time, 'DAY') day_of_week,
       event_name,
       AVG(total_waits) avg_daily_waits,
       AVG(avg_wait_ms) avg_wait_time
FROM (
    -- Use the main query here
)
GROUP BY TO_CHAR(end_interval_time, 'DAY'), event_name
ORDER BY
    CASE TO_CHAR(end_interval_time, 'DAY')
        WHEN 'MONDAY' THEN 1
        WHEN 'TUESDAY' THEN 2
        -- etc.
    END;

Peak Period Identification

-- Find days with highest wait times
SELECT * FROM (
    -- Use the main query here
)
WHERE ROWNUM <= 10
ORDER BY time_waited_ms DESC;

Common Use Cases

1. Capacity Planning

   • Track wait event growth over time
   • Identify resource constraints
   • Plan infrastructure upgrades

2. Performance Trending

   • Monitor long-term performance patterns
   • Detect gradual degradation
   • Validate tuning efforts

3. Problem Diagnosis

   • Correlate wait events with business cycles
   • Identify recurring issues
   • Compare current vs historical performance

Filtering Strategies

I/O Wait Analysis

-- Focus on I/O related waits
Enter value for name: %file%

Lock Contention Analysis

-- Monitor enqueue waits
Enter value for name: enq:%

Memory Contention

-- Check latch waits
Enter value for name: latch%

RAC-Specific Analysis

Instance Comparison

Run the script multiple times with different instance numbers:

-- Instance 1
Enter value for inst_no: 1

-- Instance 2
Enter value for inst_no: 2

-- All instances
Enter value for inst_no: %

Cross-Instance Events

-- Global cache events
Enter value for name: gc%

Performance Considerations

1. Date Range Selection

   • Smaller ranges execute faster
   • Consider AWR retention period
   • Balance detail vs performance

2. Event Filtering

   • Use specific patterns when possible
   • Avoid % for all events on large systems
   • Focus on problem areas

3. AWR Snapshot Frequency

   • More frequent snapshots = more granular data
   • Standard interval is 1 hour
   • Adjust based on requirements

Integration with AWR Reports

Complementary Analysis

1. Use this script for trend identification
2. Generate AWR reports for specific problem days
3. Cross-reference wait events with AWR findings

Baseline Comparison

-- Compare current month to baseline
-- Run for baseline period
Enter value for start_date: 01-DEC-2024
Enter value for end_date: 31-DEC-2024

-- Run for current period
Enter value for start_date: 01-JAN-2025
Enter value for end_date: 31-JAN-2025

Best Practices

1. Regular Monitoring

   • Schedule weekly/monthly trend analysis
   • Track key event categories
   • Document baseline metrics

2. Proactive Analysis

   • Monitor for increasing wait times
   • Watch timeout percentage trends
   • Alert on threshold breaches

3. Correlation with Business

   • Map wait events to business processes
   • Understand workload patterns
   • Plan maintenance windows

Troubleshooting

No Data Returned

• Verify AWR retention period covers date range
• Check if AWR snapshots exist for period
• Ensure event name pattern matches

Performance Issues

• Reduce date range
• Use specific event patterns
• Consider summary tables for large datasets

Missing Snapshots

• Check AWR snapshot schedule
• Verify automatic snapshots enabled
• Look for gaps in snapshot history

Related Scripts

• System-Wide Wait Events Analysis
• AWR Report Generation
• ASH Report Generation
• Historical System Events