Too many entries for one single session and serila# in V$Session_longops

oracle-11g-r2performancequery-performancesession

I have a concern with the entries in v$session_longops.
I used a query to identify query running from past 25 minutes for sid=62 which is given below:

SELECT s.sid,
       s.serial#,
       s.machine,
       s.program,           
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       totalwork,
       sofar
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = 62
AND    s.serial# = sl.serial#
AND    sl.elapsed_seconds > 25*60
and sofar<>totalwork;

it returns no rows.

But when I check with v$session_longops and v$session I have many entries for the same sid.
I am using OEM 12c. Even there in sql monitoring I am able to see many queries running for more than 3 hours. I use oracle 11.2.0.3

Can anyone help me understand y is it happening? I am confused..

Best Answer

you should review your query, missing s.sid = sl.sid. Besides, wher yout lokk at v$session_longops and find many rows, they all meet the other conditions - sl.elapsed_seconds > 25*60 and sofar<>totalwork ?

Related Question