How to find long running queries in Oracle

SQL queries takes long time to complete. You can check long running sessions using v$session_longops script which will show you, % completed, remaining time, sofar completed and much more detailed information.

Monitor long running sessions in Oracle using below queries:

 QUERY 1: 

SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, 
TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM 
V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0
 AND SOFAR <> TOTALWORK;
 

 QUERY 2: 

set lines 300

col TARGET for a40

col SQL_ID for a20

select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 
Mins_Remaining,ELAPSED_SECONDS,SQL_ID from v$session_longops where 
TIME_REMAINING>0 order by TIME_REMAINING;
 

From the above output you can further check the sql_id, sql_text and the wait event for which query is waiting for.

 

TO find out sql_id for the above sid:
SQL> select sql_id from v$session where sid='&SID';

To find sql text for the above sql_id:
SQL> select sql_fulltext from V$sql where sql_id='bgf07y9xn8grx';

To find wait event of the query for which it is waiting for:
SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';

To kill session in Oracle:
https://fastsqlplsql.blogspot.com/2020/01/4-best-ways-to-find-blocking-sessions.html