How to Find Long Running Queries in Oracle

 Introduction

Long running queries can slow down the database and affect user experience. As a DBA, identifying and tuning these queries is essential for maintaining performance.

Step 1: Find Currently Running SQL

SELECT s.sid, s.serial#, s.username, q.sql_id, q.sql_text

FROM v$session s

JOIN v$sql q ON s.sql_id = q.sql_id

WHERE s.status='ACTIVE'

AND s.username IS NOT NULL;

Step 2: Identify Long Running SQL by Time

SELECT s.sid, s.serial#, s.username, q.sql_id,

       ROUND(elapsed_time/1000000,2) AS elapsed_seconds,

       q.sql_text

FROM v$sql q

JOIN v$session s ON q.sql_id = s.sql_id

WHERE elapsed_time > 60000000 -- queries running more than 60 sec

AND s.username IS NOT NULL;

Step 3: Check Execution Plan

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

Replace &sql_id with the query’s SQL ID.

Step 4: Take Action

Tune the SQL (indexes, rewrite queries).

Kill session if query is blocking resources:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Conclusion

By monitoring long running queries, DBAs can detect performance bottlenecks early and take corrective action. Always analyze execution plans before deciding to terminate queries.