ITPub博客

首页 > 数据库 > Oracle > 通過active_session_history我們能得到什麼

通過active_session_history我們能得到什麼

原创 Oracle 作者:diashad 时间:2015-04-08 15:24:02 1 删除 编辑

通過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/203348/viewspace-1548098/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-08

  • 博文量
    86
  • 访问量
    442702