Blog

During DB performance or slowness issues, it is very important to check long running query in Oracle database as a DBA. Today we will learn how to check this with the proper steps.

Query 1:

set lines 300
col TARGET for a40
col SQL_ID for a20
select SID,SERIAL#,TARGET,TOTALWORK,SOFAR,TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING
/

From the above output you will find the sid and sql_id, to find the sql_text use below query :

select sql_text from v$sql where sql_id='xxxxxx';

If this is a select query and running from Application user, we are good to kill once we check with the Application Team,
but if any DML statements are running from the Application end, please ask the Application Team
for further interference.

Kill the Query with Below Command if needed :

alter system kill session 'sid,serial#' immediate;

Also, you can use the below query to check long running query in Oracle. It will give the query details that are running more than 3600 sec.

Query 2:

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,t.sql_text from v$session s
join v$sqltext_with_newlines t
on s.sql_address = t.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 3600
order by sid,serial#,t.piece;

So here are the detailed steps while troubleshooting to check long running query in Oracle. Also, you can checkout the Oracle Doc ID 2775463.1 for further reference. Hope this helps!!