Thursday, March 6, 2014

How to use v$session_longops to check long running processes

I am setting NLS_DATE_FORMAT so that the START_TIME and LAST_UPDATE_TIME will be more accurate.

SET TERMOUT OFF
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
SET TERMOUT ON
SET LINES 300
COL "CURRENTLY EXECUTING" FORMAT A35
COL OSUSER FORMAT A10
COL MACHINE FORMAT A20
COL USERNAME FORMAT A20
COL PROGRAM FORMAT A40
COL UNITS FORMAT A20
COL OPNAME FORMAT A10
COL START_TIME FORMAT A20
COL LAST_UPDATE_TIME FORMAT A20
COL SQL_ID FORMAT A15
SET VERIFY OFF
SET FEEDBACK OFF

SELECT
    S.SID,
    S.SERIAL#,
    S.OSUSER,
    S.MACHINE,
    S.USERNAME,
    S.PROGRAM,
    S.SQL_ID,
    SUBSTR(Q.SQL_TEXT,1,30) || '...' "CURRENTLY EXECUTING",
    L.OPNAME,
    L.SOFAR,
    L.TOTALWORK,
    L.UNITS,
    L.START_TIME,
    L.LAST_UPDATE_TIME,
    L.TIME_REMAINING "SECONDS LEFT",
    TO_TIMESTAMP(L.LAST_UPDATE_TIME,'DD.MM.YYYY HH24:MI:SS')-TO_TIMESTAMP(L.START_TIME,'DD.MM.YYYY HH24:MI:SS') "RUNNING FOR"
FROM V$SESSION_LONGOPS L JOIN V$SESSION S ON L.SID = S.SID
                         JOIN V$SQL Q ON S.SQL_ID = Q.SQL_ID
AND   S.SID = &SID
AND   S.SERIAL#  = L.SERIAL#
ORDER BY L.LAST_UPDATE_TIME DESC;

EXIT

Result:
SID SERIAL# OSUSER MACHINE USERNAME PROGRAM SQL_ID CURRENTLY EXECUTING OPNAME SOFAR TOTALWORK UNITS START_TIME LAST_UPDATE_TIME SECONDS LEFT RUNNING FOR
408 2624 oracle testserver1 SH sqlplus@testserver1 (TNS V1-V3) 3w11rcbvd5a32 UPDATE SALES_Q1_DI_MASTERS SET ... Table Scan 82432 82636 Blocks 06.03.2014 07:35:26 06.03.2014 09:44:22 19 +00 02:08:56.000000

No comments:

Post a Comment