SQL statement statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and collect statistics on SQL statements executed in Oracle Database (18c and newer) that meet certain criteria associated with system performance and resource usage.
• Why: This job is essential because it helps in identifying resource-intensive SQL statements that can potentially impact database performance. By monitoring buffer gets, disk reads, and other execution metrics, database administrators can take proactive steps to optimize queries and maintain the system more effectively.
• Manual checking: You can check this manually by querying the Oracle dynamic performance views such as `V$SQLAREA`. Below is an example SQL command to manually verify SQL statement statistics:
  bc. SELECT executions, buffer_gets, disk_reads, rows_processed, cpu_time, elapsed_time, sql_text FROM v$sqlarea WHERE executions > 0 AND (buffer_gets + disk_reads > 0);

Detection and Criteria

The job is configured to trigger based on thresholds defined for ‘buffer gets’ and ‘disk reads’. It scrutinizes SQL queries whenever they exceed these thresholds, implying that such queries are consuming a high level of system resources which could degrade the performance.

Implementation Details

• SQL Statement Statistics are collected from the `v$sqlarea` performance view which provides active SQL statements in Oracle’s shared SQL area.
• The specific SQL statements that exceed predefined thresholds for performance metrics such as buffer gets or disk reads will be stored into a history table for further analysis.
• Excluding certain cursors: Cursors containing specified keywords like “DECLARE“ or “BEGIN“ are deliberately excluded from data collection to focus on SQL statements with significant impact.
• Loop tolerance: The implemented procedure will exit if the maximum loop time is exceeded during execution, ensuring that database performance isn’t compromised due to long running monitoring processes.

Dependencies

This monitoring job has several dependencies, both intra-dependent and on other database objects:

• A View (`dbv_sqlarea`) is created to simplify and optimize the data retrieval process by transforming data from the `v$sqlarea`.
• Stored History Data: Insertions into a custom history table `dbw_sqlhistr` and associated management through sequences to keep track of inserts.
• Recompilation and Cleanup: Post execution, it ensures that dependencies are recompiled, and cleanup operations are performed if they fail.

Reporting

This job is coupled with a comprehensive report which displays the collected SQL statement statistics, providing insights categorized and sorted by various performance impacting metrics like rows processed, buffer gets, disk reads, and more. The report supports visualization which helps in easy identification of potential performance bottlenecks.

Tables Used

This job, thus, provides critical oversight into database performance, specifically focusing on SQL execution statistics. It leverages the existing infrastructure of Oracle’s performance views and includes detailed setup to gather, persist, and report meaningful metrics to administrators, aiming to enhance operational insights and facilitate efficient database management.