Introduction:
Sessions which are running longer in oracle database can be viewed from v$session_longops view provided by Oracle. For RAC, make sure to use gv$session_longops instead of v$.
In this blog, will see how to find long running sessions or long operations in Oracle database.
Description:
Any SQL statement which executes for more than 6 absolute seconds (long running threshold) can be viewed from v$session_longops view. To monitor query execution progress, the pre-requisite is set TIMED_STATISTICS to true.
This view can capture information on backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.
Key definition of each column in v$session_longops
-
- TARGET (table or view on which the operation is carried out)
- SOFAR (units of work done so far)
- TOTALWORK (total units of work)
- ELAPSED_SECONDS (number of elapsed seconds from the start of the operation)
- TIME_REMAINING (estimated remaining time in seconds)
Some SQL statements can spawn multiple consecutive operations, such as a “Table Scan” operation followed by a “Sort Output” operation, which will not be visible until the first operation is completed.
Following are some queries to find the long operations in Oracle database.
Queries to find long running sessions in the database:
To monitor the long running sessions:
Query1:
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;
SID SERIAL# OPNAME CONTEXT SOFAR TOTALWORK %_COMPLETE
———- ———— —————- ———- ———- ———- ——————————————–
2752 58501 Hash Join 0 5250 164565 3.19
4814 54482 Hash Join 0 196912 253487 77.68
Query2:
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;
SID TARGET TOTALWORK SOFAR MINS_REMAINING ELAPSED_SECONDS SQL_ID
———- —————————————- ———- ———- ————– ————— ———–
4814 Hash Join 253487 197067 238.716667 50029 268s54qfvwh1u
2752 Hash Join 164565 5475 1264 2610 bud5jwga8tty8
To find out sql_id for the above sid:
SQL> select sql_id from v$session where sid=’&SID’;
To find sql text for a sql_id:
SQL> select sql_fulltext from V$sql where sql_id=’&sql_id’;
To find wait event of the query for sql_id:
SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id=’&sql_id’;
To monitor the SQL’s:
Query1:
set lines 1000 pages 9999
column sid format 9999
column serial for 999999
column status format a15
column username format a10
column sql_text format a80
column module format a30
col program for a30
col SQL_EXEC_START for a20
SELECT * FROM
(SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE,
MODULE,program,
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)”,
substr(sql_text,1,30) sql_text
FROM gv$sql_monitor where status=’EXECUTING’ and module not like ‘%emagent%’
ORDER BY sql_exec_start desc
);
Query2:
set lines 1000 pages 9999
col MESSAGE for a35
select SID, MESSAGE, ELAPSED_SECONDS, TIME_REMAINING , (SOFAR/nvl(nullif(TOTALWORK,0),1))*100 pct,to_char(START_TIME,’DD-MON-YY HH24:MI’) Start_tim,to_char(SQL_EXEC_START,’DD-MON-YY HH24:MI’) EXEC_TIM,
to_char(sysdate+(TIME_REMAINING/(24*60*60)),’HH24:MI’) tim,
to_char(sysdate,’HH24:MI’) sysdt , sql_id from v$session_longops order by ELAPSED_SECONDS desc;
Bottom line:
Be careful when you attempt to monitor DML operations (UPDATE or DELETE) on the table undergoing a FullTableScan, it will not be accurately reflected in V$SESSION_LONGOPS.
