v$active_session_history

86 阅读 0 评论 57 点赞

我是靠谱客的博主无聊学姐，最近开发中收集的这篇文章主要介绍v$active_session_history，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

v$active_session_history 相关等待语句：

select ash.sql_id,
ash.event,
trunc(sum(TM_DELTA_TIME) / 1000000,2) TM_DELTA_TIME,--执行时间
trunc(sum(TM_DELTA_CPU_TIME) / 1000000,2) TM_DELTA_CPU_TIME,--cpu时间
trunc(sum(TM_DELTA_DB_TIME) / 1000000,2) TM_DELTA_DB_TIME,--DB时间
SUM(DELTA_READ_IO_REQUESTS) DELTA_READ_IO_REQUESTS,
SUM(DELTA_WRITE_IO_REQUESTS),
trunc(SUM(DELTA_READ_IO_BYTES)/1024/1024,2) DELTA_READ_IO_M,
trunc(SUM(DELTA_WRITE_IO_BYTES)/1024/1024,2) DELTA_WRITE_IO_M,
trunc(SUM(DELTA_INTERCONNECT_IO_BYTES)/1024/1024,2) DELTA_INTERCONNECT_IO_M,--网络流量

sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",--cpu
trunc(sum(decode(ash.session_state,'WAITING',1,0)) -
trunc(sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0))),2) "WAITING",--wait
trunc(sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)),2) "IO",--io
trunc(sum(decode(ash.session_state,'ON CPU',1,1)),2) "TOTAL"--o
from v$active_session_history ash,V$EVENT_NAME EN
where ash.sample_id > 190681
and ash.sample_id < (190681 + 2527)
AND ash.event#=en.event#(+)
group by ash.sql_id, ash.event
order by 2 desc;

/*1.时间模型统计
TM_DELTA_TIME 一次统计间隔
TM_DELTA_CPU_TIME 在这个间隔内，CPU时间
TM_DELTA_DB_TIME 在这个间隔内，DB时间

因为ASH采样的粒度是1秒，但是进程并不是在1s内都ACTIVE的。该统计的粒度是微秒(百万分之一秒)
TM_DELTA_TIME - TM_DELTA_DB_TIME = INACTIVE TIME
TM_DELTA_DB_ TIME - TM_DELTA_CPU_TIME = WAIT TIME*/

--查找最近一分钟内，最消耗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));