所有dba都应该熟练使用的视图v$active_session_history  

下面的sql不见得都那么准确,大家可以做一些修改,然后使用。但是这个视图是非常有用的。

查找最近一分钟内,最消耗CPU的sql语句
select sql_id,count(*),
round(count(*)/sum(count(*)) over (),2) pctload
from v$active_session_history
where sample_time > sysdate -1/(24*60)
and session_type <> 'BACKGROUND'
and session_state= 'ON CPU'
group by sql_id
order by count(*) desc;

查找最近一分钟内,最消耗I/O的sql语句
select ash.sql_id,count(*)
from v$active_session_history ash,v$event_name evt
where ash.sample_time > sysdate -1/(24*60)
and ash.session_state = 'WAITING'
and ash.event_id = evt.event_id
and evt.wait_class = 'USER I/O'
group by ash.sql_id
order by count(*) desc;

查找最近一分钟内,最消耗CPU的session
select session_id,count(*)
from v$active_session_history
where session_state = 'ON CPU'
and sample_time > sysdate -1/(24*60)
group by session_id
order by count(*) desc;

查找最近一分钟内,最消耗资源的sql语句
select ash.sql_id,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAIT",
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0))
"IO",
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate -1/(24*60)
group by ash.sql_id
order by sum(decode(ash.session_state,'ON CPU',1,1)) desc;

查找最近一分钟内,最消耗资源的session
select ash.session_id,ash.session_serial#,ash.user_id,ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAITING",
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0))
"IO",
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where en.event# = ash.event# and ash.sample_time > sysdate -1/(24*60)
group by ash.session_id,ash.user_id,ash.session_serial#,ash.program
order by sum(decode(ash.session_state,'ON CPU',1,1))  

标签: 暂无标签
oraunix

写了 199 篇文章,拥有财富 1026,被 339 人关注

转播转播 分享分享1 分享淘帖
回复

使用道具

P4 | 发表于 2014-6-20 16:42:08
我查询了,都是空值,正常吧
回复

使用道具

P4 | 发表于 2014-6-13 23:01:37
好东西,谢谢风向,学习了
回复

使用道具

P4 | 发表于 2011-3-21 10:41:31
学习了 向老师致敬
回复

使用道具

P4 | 发表于 2011-3-12 15:44:13
必须收藏
回复

使用道具

P6 | 发表于 2011-3-12 14:39:45
这个视图还是有点用的。
回复

使用道具

P3 | 发表于 2011-3-8 10:18:20
看起来很爽
回复

使用道具

游客 | 发表于 2011-3-7 19:54:38
真的很霸道
回复

使用道具

P3 | 发表于 2011-3-7 09:47:15
,谢谢老相,先收下了
回复

使用道具

游客 | 发表于 2011-3-5 10:32:50
这个先收藏了  慢慢看看
回复

使用道具

12下一页
您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈