Disk read statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect disk read statistics from specific Oracle database events to assess performance issues related to disk reads.
• Why: This job is important to monitor the disk read patterns, such as sequential and scattered reads, which can impact database performance significantly. Regular monitoring allows for timely optimization actions and proactive handling of potential slowdowns.
• Manual checking: You can check this manually in the Oracle database by issuing these SQL commands:

SELECT event, total_waits, total_timeouts, time_waited_micro FROM v$system_event WHERE event IN ('db file scattered read', 'db file sequential read');

Job Details

• Name: Disk read statistics
• Description: Collects statistics from v$system_event for db file sequential and dbfile scattered read events.
• Why it’s important: Monitoring these specific events helps in understanding the frequency and impact of single and multi-block read operations, which are crucial for performance tuning.

Technical Implementation

• Tables Created:
  • dbw_disk_read_stat – stores most recent data fetched from v$system_event.
  • dbw_disk_read_histr – maintains historical changes in wait values over time.
• Procedure: read_disk_proc
  • Purpose: Gathers new data entries from v$system_event, compares with the last recorded values, computes differences, and updates the historical data table.
  • Operations Include:
    • Data fetch and insertion into real-time and historical tables.
    • Period-specific accumulation and differential data calculation.
    • Historical data cleanup based on a configurable retention period.

Dependencies and Cleanup

• Dependencies include:
  • read_disk_proc procedure which is the main executable body.
  • dbw_disk_read_stat for storing temporary statistical data.
  • dbw_disk_read_histr for maintaining historical data.
• Cleanup operations:
  • On failure, cleanup actions are triggered to ensure no residual data affects subsequent runs.

Report Generation

• Report Title: Disk waits statistics
• Description: Showcases data based on the disk read statistics collected through the monitoring task.
• Frequency: Hourly updates to ensure the report contains the latest data.
• Contents:
  • Tables displaying disk wait events with periodic statistics.
  • Graphs categorizing waits over time by event type for visual analysis.

Technical Notes

• SQL Procedures: The core logic for data collection and processing is embedded in SQL procedures that handle data extraction from dynamic performance views, data insertion, and historical tracking with exception handling.
• Error Management: In cases of exceptions, error details are logged into DBW_ERRORS table and task-specific values are updated to reflect the issue in the control center’s monitoring dashboard.

This comprehensive setup ensures that database administrators have a clear insight into disk read operations, which are critical for maintaining optimal performance in Oracle databases.