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
- 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.
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.