Thursday, February 2, 2012

Long running queries - progress

In long running queries I am always interested in how long has it been so far and how much longer is left. 

Here sofar/totalwork gives the approximate percentage complete and elapsed_seconds gives for how long it has been running.

select
   l.sid,
   l.sofar,
   l.totalwork,
   l.start_time,
   l.last_update_time,
   l.elapsed_seconds,
   l.elapsed_seconds/60 minutes,
   s.sql_text
from
   v$session_longops l     
left outer join
    v$sql s
on
   s.hash_value = l.sql_hash_value
and
   s.address = l.sql_address
and
   s.child_number = 0;


 

credit: http://www.praetoriate.com/oracle_tips_longrunning_ddl.htm

No comments:

Post a Comment