两个大事务查询的SQL语句

来源：未知责任编辑：责任编辑发表时间:2014-02-02 17:50　点击:次

--大事务

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

作者 lxsgoodluck