SET LINES 165 PAGES 100
COL SID FOR 9999
COL SERIAL# FOR 99999
COL CHD FOR A2
COL EVENT FOR A18
COL SQL_TEXT FOR A61
COL USERN FOR A6
COL SEC FOR 9999.999
COL C_SEC FOR 9999.999
COL EXEC FOR 999999
COL DISKS FOR 99999.99
COL SPID FOR A5
COL LCE FOR 99999
SELECT d.spid spid,
--a.sid,a.SERIAL#,
a.last_call_et lce,substr(a.UserName,1,6) usern
,substr(c.sql_text,1,60) sql_text
--,c.sql_text sql_text
,(case when c.EXECUTIONS=0 then round(c.CPU_TIME/1000000,3)
when c.EXECUTIONS<>0 then round(c.CPU_TIME/(c.EXECUTIONS*1000000),3) end) c_sec
,(case when c.EXECUTIONS=0 then round(c.ELAPSED_TIME/1000000,3)
when c.EXECUTIONS<>0 then round(c.ELAPSED_TIME/(c.EXECUTIONS*1000000),3) end) sec
,(case when c.EXECUTIONS=0 then round(c.DISK_READS,2)
when c.EXECUTIONS<>0 then round(c.DISK_READS/c.EXECUTIONS,2) end) disks
,EXECUTIONS exec
,substr(b.EVENT,0,18) event ,c.hash_value
--,c.sql_id
,to_char(c.child_number) chd
FROM v$session a, v$session_wait b, v$sql c, v$process d
WHERE a.sid=b.sid AND d.addr=a.paddr
AND a.SQL_HASH_VALUE=c.HASH_VALUE
AND a.STATUS='ACTIVE'
AND c.last_load_time IN (SELECT max(w.last_load_time) FROM v$sql w WHERE w.hash_value=c.hash_value)
阅读(1496) | 评论(0) | 转发(0) |