oraunix 发表于 2010-11-14 15:48:21

所有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))

spectre 发表于 2011-3-2 01:45:10

收藏了，慢慢研究~~

zhaojingyuo 发表于 2011-3-4 17:19:16

收藏！！！

Charlie 发表于 2011-3-4 19:28:07

太给力了~能听到相老师的课真是很受用~

网路孤狼 发表于 2011-3-5 10:32:50

这个先收藏了慢慢看看

tiantian840706 发表于 2011-3-7 09:47:15

:lol，谢谢老相，先收下了

闪现 发表于 2011-3-7 19:54:38

真的很霸道

ct_hg_cool 发表于 2011-3-8 10:18:20

看起来很爽

oraunix 发表于 2011-3-12 14:39:45

这个视图还是有点用的。

crazyfox 发表于 2011-3-12 15:44:13

必须收藏
页: [1] 2
查看完整版本: 所有dba都应该熟练使用的视图v$active_session_history