Undo statistics
Job details
| Name: |
Undo statistics |
| Platform: |
Oracle |
| Category: |
Performance |
| Description: |
Collects rollback segment statistics. |
| Long description: |
Task collects rollback segment statistics. |
| Version: |
2.1 |
| Default schedule: |
8,18,28,38,48,58 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’] |
Parameters
| Name |
Default value |
Description |
| keep data for |
14 |
The amount of days the statistics are kept in the history table. |
Monitoring Job Summary for dbWatch Control Center: Undo Statistics
Purpose
- Collects and reports rollback/undo segment statistics in Oracle databases.
- Maintains performance and efficiency of database transactions by monitoring undo segments.
Why This Job is Important
- Ensures optimal sizing and performance by tracking metrics like wraps, extends, shrinks, etc., which indicate how well the undo segments are performing.
- Helps in diagnosing issues related to database performance that could stem from inefficiencies in undo segments management.
- Allows for proactive adjustments to system parameters like UNDO_RETENTION to avoid potential transaction failures or performance bottlenecks.
Manual Checking
You can manually check these statistics in the database by using the following SQL command:
SELECT * FROM dbw_undo_name;
Implementation Details
- The implementation involves creating tables, views, and a procedure that collectively help in capturing and retaining the required statistics for configured intervals.
- Periodic execution configured to run at a default schedule, ensuring continuous monitoring.
Dependencies
- This job is dependent on several tables and views that store the collected statistics and metadata.
Tables
| Table Name |
Description |
| dbw_undostat |
Stores current undo statistics |
| dbw_undostat_histr |
Stores historical undo statistics |
| dbw_undo_name |
Maintains names and identifiers of undo segments |
Views
| View Name |
Description |
| dbv_undo |
Provides a readable format of the current undo statistics |
| dbv_rbs_tot_stat_histr |
Shows total rollback segment statistics in a historical view |
| dbv_rbs_stat_histr |
Detailed historical statistics for each rollback segment |
Stored Procedure
| Procedure Name |
Description |
| proc_undo_stat |
Captures the latest statistics from dynamic performance views and updates tables |
Reporting and Output
- A comprehensive report is generated which includes tables and dual-axis charts reflecting the undo statistics over time.
- Metrics such as segment size, total writes, total gets, and changes in waits, shrinks, wraps, and extends are visualized for easy understanding of trends.
Automatic Maintenance
- The job includes cleanup mechanisms to keep the database from being overloaded with historical data that is no longer relevant, adhering to specified data retention policies.
Structured data maintenance, effective reporting, and dependencies ensure the operation of this job is both smooth and beneficial, providing critical insights necessary for maintaining Oracle database performance.