Long running queries

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and identify long-running queries in an Oracle database environment. It is designed to detect queries that have been running for extended periods and flag them with warnings or alarms based on predefined thresholds.
• Why: Monitoring long-running queries is crucial for maintaining the performance and stability of database systems. Excessive query durations can indicate inefficient query design, indexing issues, or hardware limitations, potentially leading to slower response times and increased resource consumption. If these thresholds are exceeded, it could result in performance bottlenecks that affect the entire application or system relying on the database.
• Manual checking: You can manually check long-running queries in the database by issuing the following SQL commands:

SELECT s.sid, s.machine, s.schemaname, s.logon_time, sysdate - (s.last_call_et / (60 * 60 * 24)) AS ActivityStarted, s.last_call_et AS ActiveSeconds, (SELECT listagg(sql_text) WITHIN GROUP (ORDER BY piece) FROM v$sqltext t WHERE t.address = s.sql_address AND t.hash_value = s.sql_hash_value AND t.piece BETWEEN 0 AND 61) AS SQLQUERY FROM sys.v_$session s, sys.v_$process p WHERE s.status = 'ACTIVE' AND s.type = 'USER' AND s.sql_hash_value > 0 AND p.background IS NULL AND p.addr (+) = s.paddr ORDER BY 2,3,1;

Implementation Details

• The job operates by creating a procedure that is repeatedly executed according to a default schedule. It involves several key components:
  • A historical table (dbw_long_running_history_#instance~id#) to store details of long-running queries.
  • A monitoring procedure (dbw_long_running_#instance~id#) to identify active queries exceeding the warning and alarm time thresholds, while excluding specified schemas.
• Table Implementation Code:

CREATE TABLE dbw_long_running_history_#instance~id# (histr_date DATE, status VARCHAR2(10), sid NUMBER, machine VARCHAR2(100), schema VARCHAR2(40), logon_time DATE, activity_started DATE, active_sec NUMBER, sql VARCHAR2(2000));

Dependencies and Execution

• The job’s execution depends on Oracle system views such as v$sqltext, v$session, and v$process to fetch real-time query activity.
• It uses the DBWatch Control Center’s internal parameter mechanism to manage configurable thresholds like “Warning time in seconds” and “Alarm time in seconds,” as well as to determine which schemas to exclude from the monitoring.

Reports and Alerting

• A report template is included to visualize and report on the data collected. This report is structured to display:

** The report facilitates effective auditing and analysis of long-running queries, providing insights into Query durations, times, and other relevant details.

Upgrade and Version Handling

• The monitoring job includes scripts to handle upgrades from previous versions by potentially altering procedures or schemas to match new requirements.
• The integrity and consistency of the job’s operation across various Oracle versions and setups are maintained by tailored condition checks and installation criteria.

This comprehensive setup ensures the DBA team can proactively manage database performance and handle any issues related to long query execution times efficiently.