1. Oracle ASH技术深度解析会话与SQL执行状态监控实战作为一名Oracle DBA我经常需要处理数据库性能问题。其中识别长时间运行的会话和SQL语句是最基础也最重要的技能之一。Oracle提供的ASHActive Session History功能就像数据库的黑匣子记录了所有活跃会话的详细执行信息。今天我将分享如何利用ASH和相关视图来精准定位问题会话和SQL。提示ASH数据默认保留1小时对于历史分析建议结合AWR报告使用。1.1 ASH核心原理与数据采集机制ASH的工作原理可以类比为医院的心电图监测——它每秒采集一次数据库中的活跃会话快照v$active_session_history并将这些数据压缩存储。具体来说采样频率实时数据v$active_session_history视图每秒采样一次历史数据dba_hist_active_sess_history视图每10秒采样一次来自AWR快照关键字段解析sql_exec_startSQL开始执行的时间戳sample_time采样时间点session_idsession_serial#唯一标识一个会话sql_idSQL语句的唯一标识符数据保留策略内存中保留最近1小时数据v$active_session_history磁盘上通过AWR快照长期保存dba_hist_active_sess_history2. 实时会话监控实战技巧2.1 实时会话查询方案解析下面这个查询是我日常使用频率最高的监控脚本之一它能立即显示当前所有活跃会话及其执行的SQLselect t1.*,t2.sql_text,t2.sql_fulltext,(SYSTIMESTAMP-t1.sql_exec_start) as run_time from (select inst_id,sql_exec_start,sid,serial#,schemaname,machine,program,sql_id,event,blocking_instance,blocking_session from gv$session where statusACTIVE and sql_exec_start is not null) t1, (select inst_id,sql_text,sql_fulltext,sql_id from gv$sqlarea) t2 where t1.inst_idt2.inst_id and t1.sql_idt2.sql_id order by run_time desc;关键字段说明run_time计算SQL已执行时间SYSTIMESTAMP - sql_exec_startevent显示会话当前等待的事件如I/O、锁等blocking_session标识阻塞当前会话的其他会话注意事项在生产环境执行此查询时建议使用SQL*Plus或TOAD等工具避免在PL/SQL Developer中直接运行因为GUI工具可能会对结果进行分页导致内存问题。2.2 高级监控技巧过滤特定程序 在where条件中添加and program like %你的应用名称%可以只监控特定应用程序的会话。识别锁等待 添加and wait_class ! Idle可以过滤掉空闲等待事件专注真正的性能问题。资源消耗排序 可以关联v$sesstat视图按CPU或内存使用排序select /* ordered */ s.sid, s.serial#, s.sql_id, st.value as cpu_used from v$session s, v$sesstat st, v$statname sn where st.sid s.sid and sn.statistic# st.statistic# and sn.name CPU used by this session and s.status ACTIVE order by st.value desc;3. 历史ASH数据分析方法论3.1 一小时内的历史数据分析对于已经发生的问题我们可以查询ASH历史数据。以下两个查询分别从不同粒度分析历史性能问题-- 每秒采样的ASH数据精确度高 select t1.*,t2.sql_text,t2.sql_fulltext from (select inst_id,session_id,session_serial#,session_type,user_id,sql_id,sql_exec_start,blocking_session,blocking_session_serial#,blocking_inst_id,blocking_hangchain_info,program,module,machine,port,max(sample_time-sql_exec_start) as run_time from gv$active_session_history where sample_time SYSTIMESTAMP - INTERVAL 1 HOUR and sql_exec_start is not null group by inst_id,session_id,session_serial#,session_type,user_id,sql_id,sql_exec_start,blocking_session,blocking_session_serial#,blocking_inst_id,blocking_hangchain_info,program,module,machine,port order by run_time desc) t1, (select inst_id,sql_text,sql_fulltext,sql_id from gv$sqlarea) t2 where t1.inst_idt2.inst_id and t1.sql_idt2.sql_id order by t1.run_time desc; -- 每10秒采样的AWR历史数据保留时间长 select t1.*,t2.sql_text,t2.sql_fulltext from (select instance_number,session_id,session_serial#,session_type,user_id,sql_id,sql_exec_start,max(sample_time-sql_exec_start) as run_time,blocking_session,blocking_session_serial#,blocking_inst_id,blocking_hangchain_info,program,module,machine,port from dba_hist_active_sess_history where sample_time SYSTIMESTAMP - INTERVAL 1 HOUR and sql_exec_start is not null group by instance_number,session_id,session_serial#,session_type,user_id,sql_id,sql_exec_start,blocking_session,blocking_session_serial#,blocking_inst_id,blocking_hangchain_info,program,module,machine,port order by run_time desc) t1, (select inst_id,sql_text,sql_fulltext,sql_id from gv$sqlarea) t2 where t1.instance_numbert2.inst_id and t1.sql_idt2.sql_id order by t1.run_time desc;3.2 历史分析实战技巧时间范围调整 修改INTERVAL 1 HOUR可以调整查询的时间范围如INTERVAL 30 MINUTE或INTERVAL 24 HOUR。特定SQL定位 添加and t2.sql_text like %关键字%可以搜索包含特定关键字的SQL。用户过滤 添加and t1.user_id (select user_id from dba_users where username用户名)可以只查看特定用户的活动。阻塞链分析blocking_hangchain_info字段特别有用它能显示完整的阻塞链帮助识别死锁或长时间锁等待的根本原因。4. 性能问题诊断与优化实战4.1 常见性能问题模式识别通过ASH数据分析我总结了几种常见的性能问题模式问题类型典型特征ASH中的表现解决方案CPU瓶颈高CPU使用率大量CPU_TIME等待优化SQL、增加CPU资源I/O问题高物理读db file sequential/scattered read等待优化索引、增加缓冲区锁竞争阻塞链长enq: TX - row lock contention等待优化事务设计网络延迟SQL*Net消息SQL*Net more data from client等待优化网络、批处理4.2 SQL优化实战案例最近处理的一个真实案例某报表SQL执行时间突然从5分钟增加到2小时。使用ASH分析后发现首先定位问题SQLselect sql_id, count(*) as samples from dba_hist_active_sess_history where sample_time between to_timestamp(2023-06-01 14:00,YYYY-MM-DD HH24:MI) and to_timestamp(2023-06-01 16:00,YYYY-MM-DD HH24:MI) group by sql_id order by samples desc;获取SQL执行计划变化select * from table(dbms_xplan.display_awr(8mkxm2b4uq8ux));发现执行计划从索引扫描变成了全表扫描原因是统计信息过时。解决方法是exec dbms_stats.gather_table_stats(SCHEMA,TABLE);4.3 高级诊断技巧ASH时间线分析select to_char(sample_time,YYYY-MM-DD HH24:MI) as minute, count(*) as active_sessions, sum(case when session_stateON CPU then 1 else 0 end) as on_cpu from dba_hist_active_sess_history where sample_time sysdate - 1/24 group by to_char(sample_time,YYYY-MM-DD HH24:MI) order by minute;等待事件关联分析select event, count(*) as wait_count, round(avg(time_waited)/1000) as avg_ms from dba_hist_active_sess_history where sample_time sysdate - 1/24 and session_stateWAITING group by event order by wait_count desc;TOP SQL识别select sql_id, count(*) as ash_samples, round(count(*)/sum(count(*)) over()*100,2) as pct from dba_hist_active_sess_history where sample_time sysdate - 1 group by sql_id order by ash_samples desc fetch first 10 rows only;5. ASH扩展应用与自动化监控5.1 创建自定义ASH报表我们可以将常用查询封装成视图或存储过程方便日常使用create or replace view long_running_sessions as select h.session_id, h.session_serial#, s.osuser, s.machine, h.sql_id, h.sql_exec_start, max(h.sample_time - h.sql_exec_start) as duration, s.program, s.module from v$active_session_history h join v$session s on h.session_id s.sid and h.session_serial# s.serial# where h.sql_exec_start is not null group by h.session_id, h.session_serial#, s.osuser, s.machine, h.sql_id, h.sql_exec_start, s.program, s.module having max(h.sample_time - h.sql_exec_start) interval 5 minute order by duration desc;5.2 自动化监控脚本以下是一个简单的Shell脚本可以定期检查长时间运行会话并发送警报#!/bin/bash # 设置Oracle环境变量 export ORACLE_HOME/u01/app/oracle/product/19.0.0/dbhome_1 export PATH$ORACLE_HOME/bin:$PATH # 运行SQL查询 sqlplus -s / as sysdba EOF set pagesize 0 set feedback off set heading off spool /tmp/long_sessions.txt select WARNING: Long running session - SID:||sid||, Serial#:||serial#|| , Duration:||(SYSTIMESTAMP-sql_exec_start)||, SQL_ID:||sql_id from v\$session where statusACTIVE and sql_exec_start is not null and (SYSTIMESTAMP-sql_exec_start) interval 30 minute; spool off EOF # 检查是否有输出并发送邮件 if [ -s /tmp/long_sessions.txt ]; then mailx -s Oracle Long Running Sessions Alert dba_teamcompany.com /tmp/long_sessions.txt fi5.3 ASH与AWR集成分析对于更全面的性能分析建议结合ASH和AWR报告生成特定时间段的AWR报告select * from table(dbms_workload_repository.awr_report_text( l_dbid (select dbid from v$database), l_inst_num (select instance_number from v$instance), l_bid (select min(snap_id) from dba_hist_snapshot where begin_interval_time sysdate - 1/24), l_eid (select max(snap_id) from dba_hist_snapshot) ));关键指标对比分析select metric_name, average, max_val from dba_hist_sysmetric_summary where begin_time sysdate - 1 order by begin_time, metric_name;6. 常见问题排查指南6.1 ASH数据查询无结果可能原因及解决方案时间范围错误检查时区设置确保SYSTIMESTAMP正确权限不足确保用户有SELECT_CATALOG_ROLE或直接查询权限ASH缓冲区已清空对于历史数据确认AWR快照已生成6.2 识别不可见会话有时会话在v$session中不可见但仍消耗资源可以检查select program, status, count(*) from v$session group by program, status order by count(*) desc;6.3 处理阻塞链当发现阻塞会话时可以使用以下脚本生成完整的阻塞链with block_tree as ( select level as lvl, connect_by_root sid as root_sid, sid, serial#, sql_id, blocking_session, seconds_in_wait, event from v$session start with blocking_session is null connect by prior sid blocking_session ) select lpad( , 2*(lvl-1)) || sid as tree, serial#, sql_id, event, seconds_in_wait from block_tree order by root_sid, lvl;6.4 ASH采样数据不准确ASH采样基于时间点可能错过短暂但高负载的活动。解决方法增加采样频率需调整隐含参数不建议生产环境使用结合Statspack报告分析使用Oracle Real-Time SQL Monitoring7. 性能监控最佳实践根据多年DBA经验我总结出以下ASH使用最佳实践定期基线收集begin dbms_workload_repository.create_baseline( start_snap_id (select min(snap_id) from dba_hist_snapshot where begin_interval_time sysdate - 7), end_snap_id (select max(snap_id) from dba_hist_snapshot), baseline_name Weekly_Baseline); end;关键指标监控活动会话数TOP SQL执行时间变化等待事件分布阻塞会话数量自动化警报设置会话运行超过30分钟相同SQL有多个会话被阻塞关键等待事件突增容量规划select to_char(sample_time,YYYY-MM-DD HH24) as hour, avg(count(*)) over (order by to_char(sample_time,YYYY-MM-DD HH24) rows between 3 preceding and current row) as moving_avg from dba_hist_active_sess_history where sample_time sysdate - 30 group by to_char(sample_time,YYYY-MM-DD HH24) order by hour;在实际工作中我发现ASH数据最有价值的不是单个时间点的快照而是趋势分析。通过对比不同时间段的ASH数据可以识别出性能退化模式在问题变得严重前采取预防措施。例如某关键SQL的执行时间每周增长10%这种趋势比突然的性能下降更容易被忽视但通过ASH历史数据分析可以清晰发现。
