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
标签:视图,event,sample,active,session,time,Oracle,ASH,id From: https://blog.csdn.net/weixin_tank88921/article/details/140447059