通過active_session_history我們能得到什麼

哎呀我的天吶發表於2015-05-13
原文網址 : http://blog.itpub.net/29990276/viewspace-1653514/
Session

通過active_session_history我們能得到什麼

 建立時間： 2015-4-8 15:21  修改時間： 2015-4-8 15:21 
--最近10分鐘最耗CPU的SQL語句
select a.sql_id,a.sess_count,a.cpu_load,b.sql_text
from (select sql_id,count(*) sess_count,
Round(count(*)/sum(count(*)) over(),2) cpu_load
from v$active_session_history
where sample_time >sysdate-1/(24*6)
and session_type<>'BACKGROUD'
AND SESSION_STATE='ON CPU'
GROUP BY SQL_ID
ORDER BY COUNT(*) DESC ) A,V$SQLAREA B
WHERE A.SQL_ID=B.SQL_ID

--最近10分鐘最耗I/O語句
select a.sql_id,a.sess_count,b.sql_text
from (select ASH.sql_id,count(*) sess_count
from v$active_session_history ash,v$event_name evt
where ash.sample_time>sysdate-1/(24*6)
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 ) A,V$SQLAREA B
WHERE A.SQL_ID=B.SQL_ID


--最近10分鐘最耗系統資源語句
select a.sql_id,a.sess_count,b.sql_text
from (select ASH.sql_id,count(*) sess_count
from v$active_session_history ash,v$event_name evt
where ash.sample_time>sysdate-1/(24*6)
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 ) A,V$SQLAREA B
WHERE A.SQL_ID=B.SQL_ID

--最近10分鐘最耗CPU的SQL語句
select a.sql_id,a.sess_count,a.cpu_load,b.sql_text
from (select sql_id,count(*) sess_count,
Round(count(*)/sum(count(*)) over(),2) cpu_load
from v$active_session_history
where sample_time >sysdate-1/(24*6)
and session_type<>'BACKGROUD'
AND SESSION_STATE='ON CPU'
GROUP BY SQL_ID
ORDER BY COUNT(*) DESC ) A,V$SQLAREA B
WHERE A.SQL_ID=B.SQL_ID

--最近10分鐘最耗I/O語句
select a.sql_id,a.sess_count,b.sql_text
from (select ASH.sql_id,count(*) sess_count
from v$active_session_history ash,v$event_name evt
where ash.sample_time>sysdate-1/(24*6)
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 ) A,V$SQLAREA B
WHERE A.SQL_ID=B.SQL_ID


--最近10分鐘最耗系統資源語句SQL語句
select a.sql_id,A.CPU,A.WAIT,A.IO,A.TOTAL,B.SQL_TEXT
FROM (
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*6)
       group by ash.sql_id
       order by sum(decode(ash.session_state,'ON CPU',1,1)) desc) a, v$sqlarea b
       where a.sql_id=b.sql_id
       
       
       
--最近10分鐘最耗系統資源的會話
select a.sid,a.serial#,b.username,a.user_id,a.program,a.cpu,a.waiting,a.io,a.total
FROM (
select ash.session_id sid,
ash.session_serial# 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*6)
       group by ash.session_id,ash.user_id,ash.session_serial#,ash.program
       order by sum(decode(ash.session_state,'ON CPU',1,1)) ) a, v$session b
       where a.sid=b.sid
       
       
       ----查詢目前使用者會話是在等待哪個會話
       
       select distinct a.session_id,a.session_serial#,a.session_id,a.session_state,a.session_type,
       a.blocking_session,a.blocking_session_serial#,a.blocking_session_status,
       a.event,s.sql_id
       from v$active_session_history a,v$session s
       where a.blocking_session is not null
       and a.blocking_session=s.sid
       and a.blocking_session_serial#=s.serial#
       and s.sql_id is not null

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1653514/，如需轉載，請註明出處，否則將追究法律責任。

相關文章