Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1728467
  • 博文数量: 163
  • 博客积分: 10591
  • 博客等级: 上将
  • 技术积分: 1980
  • 用 户 组: 普通用户
  • 注册时间: 2006-08-08 18:17
文章分类

全部博文(163)

文章存档

2018年(1)

2012年(1)

2011年(47)

2010年(58)

2009年(21)

2008年(35)

分类: Oracle

2011-10-08 13:39:21

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)

阅读(1483) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~