SQL waits
Job details
| Name: |
SQL waits |
| Platform: |
Oracle |
| Category: |
Performance |
| Description: |
Collects SQL wait statistics. |
| Long description: |
Task collects SQL wait statistics. |
| Version: |
2.8 |
| Default schedule: |
50 0,4,8,12,16,20 * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘10.2′ |
eleven_and_above = ‘1′)] |
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 collect and monitor SQL wait statistics for Oracle instances.
- Why: This job is crucial for performance tuning and identifying potential bottlenecks in SQL execution. By collecting detailed wait statistics, it helps in understanding where and why delays are occurring, assisting DBAs in optimizing query performance and overall database health.
- Manual checking: To manually check SQL wait statistics, you can use the following query:
select * from v$sql_wait;
Job Description
- Job Name: SQL waits
- Object: sql_waits
- Description: Collects SQL wait statistics.
- Version: 2.8
- Company: dbwatch.com
- Default Schedule: Every 4 hours at 50 minutes past the hour (0,4,8,12,16,20)
- Category: Performance
Tables and Indexes Created
| Table Name |
Description |
| dbw_sql_area_hist |
Historical record of SQL execution areas including wait times and resources used. |
| dbw_sql_area_rank |
Ranking of SQL statements based on wait times. |
| dbw_sql_daily_waits |
Daily statistics of waits for SQL statements. |
| Index Name |
Table |
Description |
| IDX_SQL_AREA_HIST_1 |
dbw_sql_area_hist |
Index on history date column. |
| IDX_SQL_AREA_HIST_2 |
dbw_sql_area_hist |
Index on SQL ID column. |
| INX_DBW_SQL_DAILY_WAITS_1 |
dbw_sql_daily_waits |
Index on SQL ID column. |
| INX_DBW_SQL_DAILY_WAITS_2 |
dbw_sql_daily_waits |
Index on history date column. |
| INX_DBW_SQL_DAILY_WAITS_3 |
dbw_sql_daily_waits |
Index on total waits column. |
Procedural Implementations and Data Flow
- “dbw_sql_area_hist_gather” procedure: This gathers and inserts data into the dbw_sql_area_hist and dbw_sql_daily_waits tables followed by ranking inserts into dbw_sql_area_rank.
- “sql_waits” procedure: This procedure manages the cleanup of historical data beyond the retention period and invokes dbw_sql_area_hist_gather to update daily statistics.
Dependencies
- Dependencies on itself (sql_waits) and other components like dbw_sql_area_hist, dbw_sql_area_hist_gather, dbw_sql_area_rank, and dbw_sql_daily_wa…
- Cleanup-on-fail is enabled to ensure data integrity upon job failures.
Report Template Configuration
Version: 2
- Title: SQL waits statistics
- Description: Template report based on SQL waits statistics task.
- Default Schedule: Every hour on the hour.
- Chapters:
- Title: SQL Waits
Additional Observations
- The job includes a robust set of SQL and PL/SQL scripts aimed at checking and gathering extensive wait-related data which is crucial for optimizing query performance in Oracle databases.
- The reporting templates provide visual insights and rankings that can be directly used for identifying issues and communicating findings to technical and management teams.