SQL statement statistics
Job details
| Name: | SQL statement statistics |
| Platform: | Oracle |
| Category: | Performance |
| Description: | Collects SQL statement statistics from v$sqlarea performance view (Oracle 11g only). |
| Long description: | Task collects SQL statement statistics from v$sqlarea performance view (Oracle 11g only). |
| Version: | 2.8 |
| Default schedule: | 30 6,11,18 * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘11%’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| max loop time | 60 | The maximum time (in seconds) the task will |
| max sql records | 100 | The maximum numbers of records |
| buffer gets threshold | 10000000 | The threshold for when the statistics will be collected. |
| disk reads threshold | 500000 | The threshold for when the statistics will be collected. |
| excl. cursor text | DECLARE,BEGIN | Cursors which includes strings defined by this parameter, are excluded from the tasks check. |
| logging threshold | 100 | … |
Job Summary
- Purpose: The purpose of this job is to collect and analyze SQL statement statistics specifically for Oracle 11g instances, using the v$sqlarea performance view.
- Why: Monitoring SQL statement statistics is crucial for performance tuning in database systems. This job helps identify heavy SQL queries that may need optimization due to high buffer gets, disk reads, or execution counts. By targeting Oracle 11g, it addresses the nuances and performance metrics specific to this version.
- Manual checking: You can manually check SQL statistics in the database by querying the v$sqlarea performance view:
SELECT * FROM v$sqlarea WHERE buffer_gets + disk_reads > 0 AND executions > 0 ORDER BY buffer_gets DESC;Report Structure
This job structures its output into multiple presentations to analyze different aspects of SQL performance. Below are the report sections generated:
Report Presentations
- “SQL statements analysis”: Lists comprehensive statistics from the V$SQLAREA performance view.
- “Statistics for cursors with the highest value of records processed”: Focuses on SQL stats with maximum processing.
- “Statistics for cursors with the highest value of buffer gets”: Details SQL stats where buffer gets are elevated.
- “Statistics for cursors with the highest value of disk reads”: Showcases SQL statistics with high disk read counts.
- “Statistics of most executed cursors”: Lists SQL stats for the most frequently executed queries.
- “History of resource demanding cursors”: Provides historical data on SQL statements that have been demanding in terms of system resources.
Each section presents data in tabulated forms focusing on different metrics like executions, buffer gets ratio, CPU time per execution, etc.
Implementation and Technical Details
- Dependencies: This task is dependent on pre-existing objects like ‘dbw_sqlhistr_seq_#instance~id#’, which is a sequence for tracking alert runs, and various views such as ‘dbv_sqlarea_#instance~id#’ for capturing real-time SQL execution statistics.
- The Procedure ‘sql_statements_stat_#instance~id#’: The core procedure that implements the logic for capturing and logging SQL statement stats. It includes:
- Detection and logging of high buffer gets and disk reads.
Automation and Scheduling
- Default Scheduling: The job is set up to run automatically in the morning at 6, 11, and 18 hours every day, ensuring regular and consistent data capturing without manual intervention.
- Express installation: Enabled for this job, suggesting that it can be set up with minimal configurations and promptly made operational.
These details encapsulate how the dbWatch job handles the monitoring of SQL statement statistics for Oracle 11g databases, providing insights that are crucial for maintaining optimal database performance.