帖两个大事务查询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
