帖两个大事务查询SQL

--大事务
select sid,serial#,start_time,last_update_time,sofar/totalwork*100,elapsed_seconds,time_remaining,
       round(elapsed_seconds/60) elapsed_minutes,
       round(elapsed_seconds/(sofar/totalwork)/60) total_minutes,
       round(elapsed_seconds/(sofar/totalwork)/60) - round(elapsed_seconds/60) need_minutes
       ,username,opname,target,target_desc,message
from v$session_longops
where totalwork <> 0 and sofar!=totalwork --sofar/totalwork < 1
order by sofar/totalwork*100

--大事务（查询执行的SQL语句）
select sid,serial#,start_time,last_update_time,sofar/totalwork*100,elapsed_seconds,time_remaining,
       round(elapsed_seconds/60) elapsed_minutes,
       round(elapsed_seconds/(sofar/totalwork)/60) total_minutes,
       round(elapsed_seconds/(sofar/totalwork)/60) - round(elapsed_seconds/60) need_minutes
       ,username,opname,target,target_desc,message
     ,s.SQL_TEXT,s.SQL_FULLTEXT,s.MODULE,s.SERVICE,s.PARSING_SCHEMA_NAME
     ,s.OPTIMIZER_MODE,s.OPTIMIZER_COST,s.ROWS_PROCESSED
     ,s.PLSQL_EXEC_TIME,s.USER_IO_WAIT_TIME,s.BUFFER_GETS,s.DISK_READS
     ,s.LAST_LOAD_TIME,s.LAST_ACTIVE_TIME,s.FIRST_LOAD_TIME,s.CPU_TIME
from v$session_longops l,v$sql s
where l.SQL_ADDRESS = s.ADDRESS and l.SQL_ID = s.SQL_ID
  and totalwork <> 0 and sofar!=totalwork --sofar/totalwork < 1
order by sofar/totalwork*100,l.ELAPSED_SECONDS desc,l.last_update_time desc

 

 

 

www.cects.cn