利用ASH定位大量消耗数据库资源的SQL

爱心果

    由于AWR默认1小时产生快照，所以对于新近出现的性能问题，AWR中可能不能记录。这时可以借助ASH(active session history)对性能问题进行分析。ASH每秒采集v$session中的活动会话信息，通过v$active_session_history体现。我们可以通过该视图查询到出现性能瓶颈期间大量消耗资源的SQL。

 

1、查找最近1分钟内最耗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 sessi
group by sql_id
order by count(*) desc;

 

2、查找最近1分钟内最耗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 sessi
and ash.event_id = evt.event_id
and evt.wait_class = 'USER I/O'
group by sql_id
order by count(*) desc;

 

3、查找最近1分钟内最耗CPU的SESSION

select session_id,count(*)
from v$active_session_history
where sample_time > sysdate – 1/(24*60)
and sessi
group by session_id
order by count(*) desc;

 

4、查找最近1分钟内最耗资源的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)) "CPU"
from v$active_session_history ash, v$event_name en
where sample_time > sysdate - 200/(24*60) and sql_id is not NULL and en.event# = ash.event#
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1)) desc;

 

5、查找最近1分钟内最耗资源的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)) "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 sample_time > sysdate - 200/(24*60) and sql_id is not NULL and en.event# = ash.event#

group by session_id,user_id,session_serial#,program

order by sum(decode(session_state,'ON CPU',1,1)) desc;