I am running a long-running query, and it seems to be "stuck" on this operation here:


From v$session_longops, the value of "SOFAR" seems to be increasing at 2-3 blocks per minute on this sort operation. The "time remaining" is misleading as it has been stuck at 34 seconds. I don't see any other contention in the database that may be causing it.

I would appreciate some pointers as to where I could look next. I don't see any abnormal level of wait events. Thanks

  • i.stack.imgur.com/MPnFB.png – Eric Larson Oct 4 '16 at 1:50
  • 1
    Is the query running longer than expected ? In my experience the longops view can be a bit 'lumpy' in its progress. The worst was a panic by the DBA that according to longops a query would take 13 days. The query finished within the expected time of 3.5 hours. By all means check for resource contention but don't spend too much time based on what longops says. – BriteSponge Oct 4 '16 at 8:15

The TIME_REMAINING in V$SESSION_LONGOPS is much like the time remaining on downloads in Internet Explorer or file copies in Windows: It is a guess, massively subject to change, and often not a very good guess. I use SOFAR divided by TOTALWORK to measure progress. As long as SOFAR is moving forward at the expected speed, there is no problem. e.g.

select opname, sid, serial#, sofar, TOTALWORK,START_TIME,round(sofar*100/TOTALWORK,2) pct_complete, 
LAST_UPDATE_TIME , round(time_remaining/60) as "Minutes Remaining"
from v$session_longops 
where sofar <> totalwork and totalwork > 0 
order by START_TIME;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.