Oracle ASH视图详解

ASH视图基础

v$active_session_history、dba_hist_active_sess_history

数据来源：

MMON进程收集数据库实例性能及会话活动的大量统计数据到SGA，并定期存储到SM/AWR组件中的数据字典

用途：

显示采样的会话活动信息，动态视图中每秒一行记录，历史视图中每10秒一行记录

结构：

通过用户手册了解视图的结构信息。与v$session视图的信息近似，但不包含inactive状态的会话信息

利用ASH视图监控数据库性能

会话监控语句

SELECT inst_id
      ,sid
      ,username
      ,machine
      ,program
      ,module
      ,action
      ,sql_id
      ,event
      ,blocking_session
      ,logon_time
      ,prev_exec_start
      ,client_info
  FROM gv$session
 WHERE status = 'ACTIVE'
   AND TYPE <> 'BACKGROUND'
 ORDER BY inst_id
         ,sid;

历史活动会话检查

-- 查看最近10分钟，按等待事件的数量降序排序
SELECT event
      ,COUNT(*)
  FROM gv$active_session_history a
 WHERE a.sample_time > SYSDATE - 1 / 144
 GROUP BY event
 ORDER BY COUNT(*) DESC;


-- 查询每分钟的采样次数
SELECT trunc(sample_time,'mi')
      ,COUNT(1)
  FROM gv$active_session_history
 GROUP BY trunc(sample_time,'mi')
 ORDER BY 1;


-- 查看最近10分钟，按等待事件和sql_id分组
SELECT event
      ,sql_id
      ,COUNT(1)
  FROM gv$active_session_history
 WHERE sample_time > SYSDATE - 1 / 144
 GROUP BY event
         ,sql_id
 ORDER BY COUNT(*) DESC;


-- 查询指定采样时间段内，按照每分钟和等待事件分组，统计数量
SELECT trunc(sample_time,'mi')
      ,event
      ,COUNT(1)
  FROM gv$active_session_history
 WHERE sample_time >= to_date('20231010 17:51:00','yyyymmdd hh24:mi:ss')
   AND sample_time <= to_date('20231010 17:54:00','yyyymmdd hh24:mi:ss')
 GROUP BY trunc(sample_time,'mi'), event
 ORDER BY 1;

利用ASH视图分析数据库阻塞问题

时间、事件、源头三段分析法

分析会话阻塞出现的时间段

-- 查看指定时间段内，每分钟等待事件不为空的会话数
SELECT trunc(sample_time,'mi')
      ,event
      ,COUNT(1)
  FROM dba_hist_active_sess_history
 WHERE sample_time > to_date('20231010 17:51:00','yyyymmdd hh24:mi:ss')
   AND sample_time < to_date('20231010 17:54:00','yyyymmdd hh24:mi:ss')
   AND event IS NOT NULL
 GROUP BY trunc(sample_time,'mi'), event
HAVING COUNT(*) > 2
 ORDER BY 1;

分析会话阻塞的等待事件

-- 查看指定时间段内，按照每分钟和等待事件分组，统计数量
SELECT trunc(sample_time,'mi')
      ,event
      ,COUNT(*)
  FROM dba_hist_active_sess_history
 WHERE sample_time > to_date('20231213 17:51:00','yyyymmdd hh24:mi:ss')
   AND sample_time < to_date('20231215 17:54:00','yyyymmdd hh24:mi:ss')
   AND event IS NOT NULL
 GROUP BY trunc(sample_time,'mi'),event
HAVING COUNT(*) > 2
 ORDER BY 1,3;

定位会话阻塞的源头

WITH ash AS
 (SELECT instance_number
        ,session_id
        ,event
        ,blocking_session
        ,program
        ,to_char(sample_time,'YYYYMMDD HH24MISS') sample_time
        ,sample_id
        ,blocking_inst_id
    FROM dba_hist_active_sess_history
   WHERE sample_time > to_date('20231213 17:51:00','yyyymmdd hh24:mi:ss')
     AND sample_time < to_date('20231215 17:54:00','yyyymmdd hh24:mi:ss'))
SELECT *
  FROM (SELECT sample_time
              ,blocking_session final_block
              ,sys_connect_by_path(session_id,',') sid_chain
              ,sys_connect_by_path(event,',') event_chain
          FROM ash
         START WITH session_id IS NOT NULL
        CONNECT BY PRIOR blocking_session = session_id
               AND PRIOR instance_number = blocking_inst_id
               AND sample_id = PRIOR sample_id) a
 WHERE instr(sid_chain ,final_block) = 0
   AND NOT EXISTS (SELECT 1
          FROM ash b
         WHERE a.final_block = b.session_id
           AND b.blocking_session IS NOT NULL)
 ORDER BY sample_time;

实战案例

create or replace procedure p_test_update
as 
 l_cnt number;
begin
  update TEST_ANALYZE set var3='abcd' where id1=920;
  commit;
end; 

update TEST_ANALYZE set var3=‘abcd’ where id1=920;

begin
  p_test_update;
end; 

alter procedure p_test_update compile;

alter procedure p_test_update compile;

按照上面的思路1、2、3逐步排查，找到问题

阻塞问题相对固定的分析思路

时间 > 事件 > 阻塞源 > 源会话分析 > 阻塞关系

利用ASH视图分析SQL运行过程

分析SQL当前所处的阶段

-- gv$active_session_history
IN_PARSE, IN_HARD_PARSE, IN_SQL_EXECUTION, IN_PLSQL_EXECUTION, IN_PLSQL_RPC, IN_PLSQL_COMPILATION, IN_JAVA_EXECUTION, IN_BIND, IN_CURSOR_CLOSE

分析SQL的所属关系

-- gv$active_session_history
SQL_ID, TOP_LEVEL_SQL_ID

分析SQL当前执行的步骤

-- gv$active_session_history
SQL_PLAN_OPERATION, SQL_PLAN_OPTIONS

分析SQL执行计划的变化情况

-- gv$active_session_history
SQL_PLAN_HASH_VALUE

分析SQL当前操作的对象或数据

-- gv$active_session_history
CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, CURRENT_ROW#

实战案例

select * from ANA_TRUSS; 
create index idx_ana_truss_var2 on ana_truss(var2,var1);
insert into ANA_TRUSS select * from ANA_TRUSS;

-- 查询出object_id （CURRENT_OBJ#字段）
SELECT sample_time
      ,sql_id
      ,sql_opname
      ,event
      ,current_obj#
  FROM gv$active_session_history
 WHERE event = 'db file sequential read'
 ORDER BY sample_time DESC;

-- 根据object_id查询具体是哪个对象
select * from dba_objects where object_id=xxx;

ASH视图的其他用途

• 分析数据库运行性能基线、运行趋势
• 对比各个不同时间段的活动会话差异
• 分析跨实例的并行守护进程
• 分析会话资源消耗情况

参考资料

Oracle活动会话历史(ASH)及报告解读

https://blog.51cto.com/u_14286115/5193824

Oracle性能优化_ASH视图.pptx