Wait statistics
Job details
| Name: |
Wait statistics |
| Platform: |
Oracle |
| Category: |
Performance |
| Description: |
Collects waits statistics encountered by threads that executed. This task is based on the v$system_event dynamic performance view. |
| Long description: |
Collects waits statistics encountered by threads that executed. This task is based on the v$system_event dynamic performance view. |
| Version: |
2.8 |
| Default schedule: |
2,7,12,17,22,27,32,37,42,47,52,57 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (eleven_and_above=‘1′ |
version like ‘10.2%’) ] |
Parameters
| Name |
Default value |
Description |
| keep data for |
14 |
Number of days to provide statistics for. |
| keep aggregated data for |
60 |
Number of days to provide aggregated statistics for. |
| enable warnings and alarms |
NO |
If set to “NO” (default), the alert will only collect performance statistics without returning status warning or alarm. |
| alarm threshold (time) |
30 |
A period of time (in minutes) which must be passed (combined with average values of total waits) before an alarm is returned by the alert procedure. |
| warning threshold (time) |
10 |
A period of time (in minutes) which must be passed (combined with average values of total waits) before a warning is returned by the alert procedure. |
| waits threshold |
80 |
Total waits in percent relative to capacity threshold which is calculated from logical CPU count (cpu_count value from sys.dm_os_sys_info table) multiplied by 60 seconds. If this threshold is reached over a period of time (controlled by parameters “warning/alarm threshold (time)”), a warning/alarm is returned. |
| ignore wait types |
|
The list of wait types to be ignored (separated by comma). |
Job Summary
- Purpose: The purpose of this monitoring job is to collect and analyze wait statistics for Oracle databases. It focuses on assessing the waits encountered by threads due to database activities, which is crucial for performance tuning and troubleshooting.
- Why: This job is essential because understanding wait statistics helps in identifying performance bottlenecks within the database. By monitoring different wait types and their impacts, effective optimization strategies can be formulated to enhance database responsiveness and efficiency.
- Manual checking: You can check this manually in the database by issuing the following SQL commands:
SELECT event, total_waits, time_waited FROM v$system_event WHERE event NOT IN ('Idle');
Details and Mechanics
- The job utilizes dynamic views such as “v$system_event” to fetch real-time data about database wait events.
- It tracks and aggregates data about wait activities to aid in identifying trends and excessive wait times which might indicate performance issues.
- An implementation script is used to gather initial statistics, and continuous monitoring scripts update this data at regular intervals specified by the default schedule.
- It facilitates custom alerts based on thresholds defined for wait times and percentages relative to CPU utilization. These thresholds help in proactive monitoring and can trigger warnings or alarms when surpassed.
Configuration and Dependencies
- This job is specifically tailored for Oracle databases and relies on procedures that manage wait statistics’ historical data, such as inserting and cleaning up records.
- It includes dependencies on several database objects, such as tables and procedures named according to the database instance they pertain to, which store the current, historical, and aggregated wait statistics.
- It also ensures device compatibility by checking if the targeted instance fits the required Oracle version, thereby facilitating appropriate adjustments.
Implementations and Maintenance
- The job involves multiple SQL procedures and table creation scripts that manage the lifecycle of wait data, from collection and temporary storage to historical aggregation.
- The job’s implementation ensures it commences with a clean environment by potentially dropping existing tables and recreating them to hold new data on installation.
- Maintenance actions include periodic cleanup of historical data based on retention periods defined in the configuration, ensuring efficient use of database resources.
Reporting and Visualization
- The job provides visual presentations through charts and graphs that depict current and historical wait statistics, helping visually interpret the performance impacts over time.
- Reports include data on various wait types over different time frames like daily or in specific working hours, offering segmented views essential for detailed analysis.
- The visual tools integrate thresholds and parameters to compare actual wait times against calculated limits, providing a clear performance metric that is easy to read and interpret.
Conclusion
Understanding and monitoring Oracle wait statistics is vital for maintaining optimal database performance. This dbWatch Control Center job automates the gathering, processing, and reporting of these statistics, providing essential insights to database administrators for proactive performance management. Moreover, the adjustable thresholds and comprehensive reporting capabilities ensure that the system remains within operational parameters, thereby avoiding potential performance degradation.