Disk read statistics


Job details

Name: Disk read statistics
Platform: Oracle
Category: Performance
Description: Collects disk read statistics from v$system_event performance view for db file sequential and dbfile scattered read event.
Long description: Task collects disk read statistics from v$system_event performance view for db file sequential read event (a single-block read, for example index fetch by ROWID) and a db file scattered read event (a multiblock read, for example a full-table scan).
Version: 2.5
Default schedule: 4,14,24,34,44,54 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & maj_version=‘8′]

Parameters

Name Default value Description
keep data for 14 The amount of days the statistics are kept in the history table.

Job Summary

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

Dependency Type Description
Main Procedure Executes core data collection logic
Dependency Table Stores information related adjustments
History Table Maintains historical data for reporting purposes

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.

Event Type Waits per second History date
db file sequential read Calculated value Date of record
db file scattered read Calculated value Date of record

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.