Redo statistics
Job details
| Name: | Redo statistics |
| Platform: | Oracle |
| Category: | Performance |
| Description: | Gets redolog files statistics. Based on Oracle dictionary and performance views. |
| Long description: | Task gets redolog files statistics. Based on Oracle dictionary and performance views. |
| Version: | 1.7 |
| Default schedule: | 6,16,26,36,46,56 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘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 monitor and gather statistics related to redolog files in Oracle database instances, particularly focusing on version 8.
- Why: This job is important to ensure efficient database operations by analyzing the usage and performance of redolog files.
- Manual checking: You can check this manually in the database by issuing the SQL commands provided in various parts of the job’s implementation code.
Job Description and Dependencies
- The job is named “Redo statistics” and targets Oracle database instances specifically with database versions starting with ‘8′.
- It collects data related to redolog files based on Oracle dictionary and performance views.
- The ‘redo_stat’ object is the primary focus, gathering data about group numbers, thread numbers, status, and more.
Task Execution Details
- Default Schedule: The task is scheduled to execute every ten minutes, which allows for frequent updates and ensures that performance data is consistently refreshed.
- Dependencies: Includes several dependencies like ‘dbw_redo_stat’, ‘dbw_switch_count_day’, ‘dbw_switch_count_hour’, etc., which are essential for the successful execution of this task.
- Self-clean on Failure: If an operational failure occurs, the task is designed to clean up its dependencies.
Implementation
- Various SQL scripts and procedures are used to create tables and store data compulsory for analyzing redo activities in the Oracle instance.
- These scripts manage the creation and upkeep of tables like ‘dbw_redo_stat’, ‘dbw_trans_count’, and others used to store transaction counts, switch counts per day/hour, and redo logs statistics.
Reporting and Analysis
- This job supports a report template named “Redo statistics” which includes chapters such as “Redolog analysis,” highlighting configurations and efficiency metrics of redolog files.
- Several SQL select commands are used to fetch data for presentations in the report, enabling detailed insights into redo log configurations and their performance impacts.
Presentations include:
| Presentation Title | Details |
|---|---|
| Redolog files | Lists and details of existing redolog files including size and status. |
| Redolog switch frequency per day | Shows the frequency of redolog switches per day, providing insights into transaction rates and database load distributions. |
| Redolog switch frequency per hour | Provides data on how evenly redolog switches are distributed across hours of a day, which impacts performance. |
| Transaction frequency | Displays information about database transaction frequency to assess the load and performance.
Special Considerations
- Version Control: Clearly stated dependency on Oracle version starting with ‘8′, making it version-specific.
- Performance Charts: Detailed charts are included in the report which visualizes the logged statistics in an easy-to-understand format.
- Recovery and Protection: Emphasizes the critical nature of redolog files in data recovery scenarios by documenting configuration states and activities.
This job forms a comprehensive tool for database administrators by offering deep insights into redolog utilization, thus facilitating optimized database recovery configurations and operational efficiencies.