Oracle – Understanding V$SESSION_LONGOPS Output

oracle

I am curious about something, I was trying to find any long running queries currently running on my Oracle database.

The Query:

SELECT username,sid, serial#,
     TO_CHAR(CURRENT_TIMESTAMP,'HH24:MI:SS') AS curr,
     TO_CHAR(start_time,'HH24:MI:SS') AS logon,
     (sysdate - start_time)*24*60 AS mins
FROM V$SESSION_LONGOPS
WHERE    username is not NULL
AND (SYSDATE - start_time)*24*60 > 1 ;

The output:

USERNAME SID   SERIAL#  CURR         LOGON       MINS
user     1999   23457   15:53:59    13:58:10    4435.81666666667
user     581    7549    15:53:59    15:57:10    4316.81666666667
user    1999    31343   15:53:59    16:58:10    4255.81666666667

Based on the info above is that telling me I have several queries running for days?

I am new to Oracle, mostly MySQL but i know in MySQL this is not a good thing.

If someone could tell me what I am seeing that would be great.

Best Answer

Long running queries may show up in V$SESSION_LONGOPS, but it is far from giving a complete overview. Only a few specific built-in operations are logged here, for example full table scan, index fast full scan, hash join, sorts and some other.

V$SESSION_LONGOPS shows not only the current long operations, but a history as well. The number of entries retained is 500, according to V$Session_longops Have Limited Records Of 500 (Doc ID 783721.1) This is per instance by the way.

For example, I have 480 entries, the first one is almost a week old:

SQL> select sysdate, min(last_update_time), count(*) from gv$session_longops;

SYSDATE    MIN(LAST_U   COUNT(*)
---------- ---------- ----------
2016-12-04 2016-11-28        480

479 of them is not related to any current session (the remaining one I created artificially just to see a difference).

SQL> select count(*) from gv$session_longops 
     where (inst_id, sid, serial#) not in (select inst_id, sid, serial# from gv$session);

  COUNT(*)
----------
       479

On a 2-node cluster:

SQL> select inst_id, sysdate, min(last_update_time), count(*)
     from gv$session_longops group by inst_id;

   INST_ID SYSDATE   MIN(LAST_   COUNT(*)
---------- --------- --------- ----------
         1 04-DEC-16 27-NOV-16        453
         2 04-DEC-16 02-DEC-16        473

View other columns as well, for example opname and last_update_time as suggested. Also, join to gv$session to find which entries are current.

Related Question