SQL statement statistics
Job details
| Name: | SQL statement statistics |
| Platform: | Oracle |
| Category: | Performance |
| Description: | Collects SQL statement statistics from v$sqlarea performance view (Oracle 12c only). |
| Long description: | Task collects SQL statement statistics from v$sqlarea performance view (Oracle 12c only). |
| Version: | 2.5 |
| Default schedule: | 30 6,11,18 * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘12%’)] |
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 primary purpose of this job is to monitor and collect statistics for SQL statements executed in an Oracle 12c environment.
- Why: Monitoring SQL statement statistics is crucial for performance tuning. It helps in identifying resource-intensive queries that could potentially be optimized to improve database performance. By keeping track of executions, buffer gets, disk reads, and other relevant metrics, it becomes possible to pinpoint bottlenecks.
- Manual checking: You can manually check SQL statement statistics by running the following commands within your Oracle database:
SELECT * FROM v$sqlarea WHERE (buffer_gets+disk_reads) > 0 AND executions > 0;Job Description
This job is designed for the Oracle 12c database and utilizes the dynamic performance view v$sqlarea to gather information. It specifically targets instances where the Oracle database engine is present, and the version begins with ‘12′, ensuring compatibility and relevance of the collected data.
Task Details
- “SQL statement statistics” is tasked with collecting and managing SQL execution statistics.
- It applies criteria such as:
- Buffer gets over a specified threshold.
- Disk reads over a specified threshold.
- It also filters SQL statements to exclude those containing specific keywords like “DECLARE“ or “BEGIN“.
- A history table is maintained to store these statistics, with a limitation on the maximum number of records retained.
Dependencies and Execution
- The task has dependencies on:
- Itself (recursive dependency for checks within the shared pool statistics).
- Base views for SQL area statistics.
- Constructs involved:
- Sequences for number generation.
- Creation of tables and views pertaining to historic data and performance views.
Implementation Strategy
This task involves:
- Creating necessary database objects like sequences, tables, and views if they do not exist.
- Procedures to collect data based on set thresholds and parameters are outlined. Data exceeding these thresholds are then logged into the history table.
Reporting and Output
- Output for this monitoring task is handled via structured reports that include:
- Rows processed
- Buffer gets
- Disk reads
- CPU usage
- These are categorized and formatted in several tables in the report to provide clear insights into the SQL performance.
Conclusion
Monitoring SQL statements in a database environment is an essential task for maintaining optimal performance and identifying possible issues. This task provides a comprehensive set of tools for administrators to track and analyze SQL operations specifically tailored to Oracle 12c databases.