Redo statistics
Job details
| Name: |
Redo statistics |
| Platform: |
Oracle |
| Category: |
Performance |
| Description: |
Collects redo log switch and transaction statistics per hour and per day using Oracle dynamic performance views. |
| Long description: |
This job collects detailed statistics on redo log switches and transaction counts in Oracle databases. It aggregates data per hour and per day to help identify activity patterns, performance anomalies, or unusual load. The information is retrieved from dynamic performance views to ensure better insight into the database workload profile and helps detect periods of high transactional activity. |
| Version: |
3.61 |
| Default schedule: |
6,16,26,36,46,56 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & nine_and_above=‘1′] |
Parameters
| Name |
Default value |
Description |
| keep data for |
60 |
Specifies the number of days hourly statistics and transaction frequency data are retained in the history table. This setting does not affect the daily switch count, which is stored separately and retained for up to one year. |
Job Summary
- Purpose: The primary objective of this task is to monitor and collect statistics related to the redo log files in Oracle databases.
- Why: Redo log files are crucial for database recovery processes such as instance recovery and media recovery. Efficient management and monitoring of redo logs can prevent data loss, enhance data integrity, and optimize performance during recovery operations. Monitoring these statistics helps in managing the size, switches, and performance of redo logs which are significant for ensuring database resilience and efficiency.
- Manual checking: You can manually check the statistics and information related to redo logs in an Oracle database by executing the following queries:
SELECT THREAD# as "Thread", GROUP# as "Group", MEMBER as "Member", BYTES as "Size", STATUS as "Status" FROM [schema_name].dbw_redo_stat_[instance_id] WHERE rep_type!='OLD' ORDER BY GROUP#
Details and Dependencies
- Task Name: Redo statistics
- Version: 3.5
- Description: Gets redo log files statistics based on Oracle dictionary and performance views.
- Default Schedule: Every 10 minutes (at minutes 6, 16, 26, 36, 46, 56 of each hour)
Dependencies:
| Dependency Object |
Object Type |
Description |
| redo_stat_[instance_id] |
2 (Self-reference) |
Task has a dependency on itself, likely for iterative checks or updates. |
| dbw_redo_stat_[instance_id] |
0 (Table) |
Primary table holding redo log statistics. |
| dbw_trans_count_[instance_id] |
0 (Table) |
Stores transaction count data. |
| dbw_switch_count_hour_[instance_id] |
0 (Table) |
Holds data for redo log switches per hour. |
| dbw_switch_count_day_[instance_id] |
0 (Table) |
Contains redo log switches per day data. |
| dbw_switch_count_last_run_[instance_id] |
0 (Table) |
Used to track the last run details for day and hour redo statistics. |
| dbw_trans_count_histr_[instance_id] |
0 (Table) |
Historical transaction data table. |
Implementation
- Code: Various tables are created and procedures are defined to populate these tables with the logs’ statistics such as switch number and frequency, and transaction counts. These procedures help in generating reports on redo log file sizes, status, and activities.
Reporting Template
- Report Title: Redo Statistics
- Description: The report provides detailed analysis based on the collected data from redo log statistics, including redo log configurations and switch frequency.
- Default Schedule: Hourly
Report Contents
- Chapter: Redolog Analysis
- Details:
- Discusses the importance of redolog files in recovery processes.
- Presentations: Includes tables and charts that display:
- Redolog file statistics grouped by thread and size.
By efficiently monitoring and analyzing redo statistics using the dbWatch job, database administrators can ensure the stability and performance of their Oracle databases, especially in recovery scenarios.