Oracle 11.2.0.4 — “sofar” increasing slowly in V$SESSION_LONGOPS

oracleoracle-11g-r2

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

http://i.stack.imgur.com/MPnFB.png

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

Best Answer

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;
Related Question