本文介绍了如何使用Oracle的active_session_history视图来诊断和优化数据库性能。通过查询此视图可以找出最近10分钟内最耗CPU、I/O及系统资源的SQL语句，并进一步定位到具体的会话，为数据库性能问题提供了解决方案。
通過active_session_history我們能得到什麼
--最近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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/203348/viewspace-1548098/
扫一扫
1564
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
