CPU load
Job details
| Name: | CPU load |
| Platform: | Oracle |
| Category: | Performance |
| Description: | Checks CPU load using Oracle performance view v$osstat. |
| Long description: | Checks and collects CPU load statistics. The task also aggregates statistics for three different periods of the day: night hours (NW 00:00-08:00), working hours (WH 08:00-16:00) and evening hours (EH 16:00-00:00). |
| Version: | 3.8 |
| Default schedule: | 0,5,10,15,20,25,30,35,40,45,50,55 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & eleven_and_above=‘1′] |
Parameters
| Name | Default value | Description |
|---|---|---|
| Warning level in percent | 90 | The value of CPU load that will trigger warning. |
| Delete statistics older than (hours) | 336 | The value in hours of how old statistics we will keep in database. Older records is deleted as part of this check. |
| Alarm level in percent | 99 | The value of CPU load that will trigger alarm. |
| Turn off alert | NO | Change this to YES in order to not get alarms and warning on high CPU load. |
| Time threshold | 30 | A period of time (in minutes) which must be passed (combined with average values of CPU usage) before an alarm or a warning is returned by the procedure. |
Job Summary
The dbWatch monitoring job “oracle_cpu_load_check,” referred to as “CPU load”, is critical for database performance management, particularly on Oracle platforms. This function periodically assesses the CPU usage by collecting statistical data from Oracle’s performance view v$osstat.
- Purpose:
- The main objective of this job is to monitor CPU load statistics in an Oracle database environment. It helps in identifying the periods with high CPU load during different times of the day—namely night, working, and evening hours.
- Why:
- Monitoring CPU load is essential for maintaining optimal database performance and operational efficiency. High CPU usage can indicate underlying issues such as inefficient queries or inadequate hardware resources, which could potentially lead to slowdowns or system unavailability.
- Manual checking:
- Although dbWatch performs automated checks, the CPU load can also be examined manually through Oracle SQL queries. One such critical SQL command is utilized for fetching data from v$osstat:
select * from v$osstat where stat_name in ('NUM_CPUS', 'USER_TIME', 'NICE_TIME', 'SYS_TIME', 'IOWAIT_TIME', 'IDLE_TIME', 'BUSY_TIME', 'LOAD');Implementation Details
The job continuously collects CPU load data, segregating it based on time periods:
- Early morning (00:00-08:00) referred to as Night Hours (NH).
- Daytime (08:00-16:00) referred to as Working Hours (WH).
- Evening (16:00-00:00) referred to as Evening Hours (EH).
These statistics help in analyzing CPU load with respect to different work shifts and operational hours, providing a comprehensive view of the system performance.
Alert Management
- Alerts and Warnings:
- The system has configurable thresholds for generating warnings and alarms based on CPU usage percentages. Alerts notify database administrators about potential issues, allowing for proactive management and resolution.
- Reporting:
- dbWatch generates reports that reflect CPU usage over the last 48 hours, including detailed statistics for user, system, idle, and I/O wait times. Additionally, it curates aggregated data showing minimum, average, and maximum CPU usage during specified periods, supplemented by interactive charts for better visualization.
Dependencies and Maintenance
- Dependencies:
- The job depends on multiple Oracle system views and custom tables maintained within the dbWatch schema for historical and aggregated data storage. Ensuring these dependencies are active and updated is crucial for accurate monitoring.
- Data Retention:
- Data older than a configurable threshold (default is 336 hours) is automatically purged from the system to manage storage efficiently and ensure that the monitoring system remains performant.
Configuration and Customization
- The job allows customization in terms of alert thresholds and data retention policies. Administrators can adjust these parameters based on system performance and business requirements.
- System Load Analysis:
- For Unix-based systems, the job can also mimic the ‘sar’ command output, providing familiar analytics about CPU usage, which is particularly useful for users with a Unix/Linux background.
This meticulous design of monitoring and alerting ensures that the database environment under dbWatch surveillance maintains optimal performance and reliability, safeguarding against performance degradation that could affect business operations.