v$active_session_history视图

脚本摘自《教你如何成为oracle 10g OCP》 查找最近一分钟内，最消耗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))