Disk read statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect disk read statistics specifically from Oracle 8i databases, targeting events related to both sequential and scattered disk reads.
• Why: This job is important because it aids in monitoring the performance of disk reads in Oracle databases, which can be crucial for diagnosing system bottlenecks and improving database performance. Proper monitoring of these metrics can help in preemptive tuning and troubleshooting efforts.
• Manual checking: You can check this manually in the database by issuing the following SQL commands:

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

Job Details

The job is configured to execute at ten-minute intervals, continually updating tables designed to keep track of both ‘scattered’ and ‘sequential’ reads from the database. It heavily involves working with Oracle performance views, specifically “v$system_event”.

Major Components and Processes

• Dependencies: The storage procedure utilized in this job (`read_disk_proc`) relies on the existence of specific tables and views in the database. The procedure collects statistics data, inserts records into the historical and current statistics tables, commits changes, and deletes outdated records as per data retention configuration.

Implementation Breakdown

A detailed look into the creation of the necessary tables:

CREATE TABLE dbw_disk_read_histr(...);
CREATE TABLE dbw_disk_read_stat(...);
CREATE OR REPLACE PROCEDURE read_disk_proc IS ...

The procedure (`read_disk_proc`) plays a crucial role by:
* Fetching current statistics from Oracle’s "v$system_event" performance view.
* Comparing the latest data with previously stored data to calculate differences.
* Storing the computed statistics in designated tables for both current and historical record-keeping.

The procedure also handles scenarios where the database restarts (e.g., by comparing event timestamps with system start times).

Error Handling

Error handling is integrated within the procedure to update task values with error messages, ensuring any failures during execution do not go unnoticed.

Reporting Mechanism

The dbWatch Report, named “Disk waits statistics,” visually represents the collected data through both tabular and chart formats. The reports allow database administrators to easily comprehend the trends and anomalies in disk read patterns over time.

Presentation of Data

This report presents statistical data regarding disk waits, taking historical data into account. It computes ‘waits per second’ by using the differential waits and the time period of data collection, ensuring precise monitoring output.

Additionally, a categorized chart enhances the visual interpretation, plotting ‘waits per second’ across the collected historical dates for different types of disk reads.

Summary

The dbWatch Control Center job “oracle_disk_read_statistics_8i” is an essential tool for database performance monitoring, with a specific focus on detailed disk access patterns. Its automated nature ensures continuous tracking and the capability to provide timely insights into performance trends, crucial for maintaining optimal database operations.