Here we are going to learn how to find the long running queries in oracle, sometimes the query takes time to complete. Oracle has a view v$session_longops which is used to tracke these kinds of queries. The view shows the details about the work has donned in percentage, the pending percentage also and sofar completed, etc.
How to find the long running queries in oracle
Here is a command which shows you the information about the long running queries in oracle.
SQL>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;
Or you can use also below query for more clearity.
set lines 300
col TARGET for a40
col SQL_ID for a20
SQL>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;
With the reference of above output you can find out the sql_text, sql_id and running wait events.
Using below queries check long running queries
1. This query will find sql_id using above sid:
SQL> select sql_id from v$session where sid='&SID';
2. Find out the sql text on behalf of above sql_id:
SQL> select sql_fulltext from V$sql where sql_id='SQL_ID';
3. Check wait event for the query:
SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';
You must read:
2 thoughts on “How to find the long running queries in oracle”