v$active_session_history与dba_hist_active_sess_history差异

最新推荐文章于 2022-05-31 17:40:37 发布

xiaoxiao树

最新推荐文章于 2022-05-31 17:40:37 发布

阅读量4k

点赞数 1

分类专栏： oracle

oracle 专栏收录该内容

73 篇文章

订阅专栏

本文详细介绍了Oracle数据库中活动会话历史(v$active_session_history)与自动工作负载资料档案(dba_hist_active_sess_history)的使用方法。通过具体SQL查询示例，展示了如何分析最近一分钟内最消耗CPU、I/O的SQL语句及会话，为数据库性能调优提供指导。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- 每1秒将v$session中记录保存到 v$active_session_history 视图中，只会加载非idle会话。存在内存，重启就会消失。用做ash报表
select sample_id,
       sample_time,
       IS_AWR_SAMPLE,
       cast(sample_time as date),
       to_char(sample_time,'yyyymmdd hh24:mi:ss'),
       session_id,
       session_serial#,
       user_id,
       sql_id,
       SQL_EXEC_START,
       sql_child_number,
       sql_opname,
       force_matching_signature,
       top_level_sql_id,
       sql_plan_hash_value,
       event,
       p1,
       p1text,
       p2,
       p2text,
       p3,
       p3text,
       wait_class,
       wait_time,
       session_state,
       blocking_session_status,
       blocking_session,
       blocking_session_serial#,
       blocking_inst_id,
       program,
       module,
       machine,
       CURRENT_OBJ#,
       CURRENT_FILE#,
       CURRENT_BLOCK#,
       CURRENT_ROW#
from v$active_session_history where sample_id >= 117252 and sample_id <= 117752
-- where cast(sample_time as date) between to_date('2019-02-21 16:52:00','yyyy-mm-dd hh24:mi:ss') and to_date('2019-02-21 16:53:00','yyyy-mm-dd hh24:mi:ss')
-- order by 1
--    where sample_time between sysdate - 12/24 and sysdate - 1/24;

-- dba_hist_active_sess_history 中数据通过快照生成。快照中记录从v$active_session_history中抽取，抽取条件是间隔10秒且相同的stample_time记录，并且持久化保存,用做awr报表。
-- 持久化数据不全
select snap_id,
       sample_id,
       sample_time,
       DBID,
       INSTANCE_NUMBER,
       cast(sample_time as date),
       to_char(sample_time,'yyyymmdd hh24:mi:ss'),
       session_id,
       session_serial#,
       sql_id,
       SQL_EXEC_START,
       sql_child_number,
       sql_opname,
       force_matching_signature,
       top_level_sql_id,
       sql_plan_hash_value,
       event,
       p1,
       p1text,
       p2,
       p2text,
       p3,
       p3text,
       wait_class,
       wait_time,
       session_state,
       blocking_session_status,
       blocking_session,
       blocking_session_serial#,
       blocking_inst_id,
       program,
       module,
       machine,
       CURRENT_OBJ#,
       CURRENT_FILE#,
       CURRENT_BLOCK#,
       CURRENT_ROW#
from dba_hist_active_sess_history
   where sample_id >= 117252

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