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:
479 of them is not related to any current session (the remaining one I created artificially just to see a difference).
On a 2-node cluster:
View other columns as well, for example
opname
andlast_update_time
as suggested. Also, join togv$session
to find which entries are current.