When working with Oracle databases, tracking executing queries can help identify performance issues quickly. In this guide, we will explore a script to find running queries in oracle database, allowing you to monitor SQL execution time, CPU usage, and I/O statistics.
Table of Contents
SQL Script to Find Executing Queries in SQL Monitor:
SET lines 200
SET pages 1000
SET SQLBL ON
STATUS format a12
col SQL_EXEC_START format a22
col username for a7
col sid for 999
SELECT *
FROM
(SELECT status,
sid,session_serial#,username,
sql_id,
sql_exec_id,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed(s)",
ROUND(cpu_time/1000000) AS "CPU(s)",
buffer_gets,
ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads(MB)",
ROUND(physical_write_bytes/(1024*1024)) AS "Phys writes(MB)"
FROM v$sql_monitor
where status not like '%DONE%'
ORDER BY sql_exec_start desc
)
WHERE rownum<=20;
This script fetches the top 20 running sessions in oracle from the v$sql_monitor view, which is essential for monitoring long running queries in oracle that have not completed.
Sample Output:
SQL Execution Start: Timestamp when the query execution started.
Elapsed(s): Total time the query has been running in seconds.
CPU(s): Time spent by the CPU executing the query in seconds.
Buffer Gets: Number of logical read operations (buffers) performed by the query.
Phys reads (MB): The amount of physical disk reads (in megabytes) that the query has performed. This happens when data must be read from disk rather than memory.
Phys writes (MB): The amount of data written to disk (in megabytes) by this query. This can occur during DML operations (like inserts, updates, or deletes).
To learn more about SQL monitoring in Oracle, you can refer to the official Oracle SQL Monitoring Guide.
If you want to find the top 10 expensive SQL queries, then you can follow my other article, Expensive SQL Queries.
