46-Oracle ASH-报告生成

小伙伴们，Oracle ASH（Active Session History）日常排查性能问题的多不多。当业务突然卡顿、告警频发时，ASH快速定位问题（每秒采样活动会话的等待事件、SQL_ID和阻塞关系，直接透视数据库的实时状态），这个用起来还是很顺手的。ASH是DBA的灭火器，尤其在秒级故障定位中无可替代。不过由于数据保存时间有时效数据容易被覆盖哦。

一、Oracle ASH 性能监控来历和价值

• ​传统监控局限​
  • 10g前依赖V$SESSION，V$SESSION_WAIT，会话断开后数据丢失，无法回溯瞬态性能问题（如秒级锁争用、SQL执行计划突变）
• ​ASH的核心价值​
  • ​实时性​：每秒采样活动会话（非空闲状态），捕获SQL执行路径、等待事件、阻塞关系，成为诊断瞬态问题的“高速摄像机”
  • ​持久化​：会话断开后数据仍保留，支持历史性能回溯

二、技术原理：秒级采样的设计​

​1. 采样机制​

• ​精准定点​：仅采集非空闲会话​（等待事件非Idle类）
• ​动态权重​：长等待事件采样概率更高（100ms事件采样率是10ms的10倍）
• ​执行主体​：由后台进程 ​MMNL​ 完成采样

2. 存储架构​ 

​存储层	​位置​	​保留策略​	​进程​
​内存缓冲区​	SGA共享池	循环覆盖，默认保留≤1小时	MMNL
​持久化仓库​	AWR表（WRH$_ACTIVE_SESSION_HIST）	每60分钟或缓冲区满时写入，保留8天	MMON

• 缓冲区大小：
  • 10g默认1MB，11g默认4MB，可通过隐含参数_ash_size调整修改（不过需要重启实例）

3. 数据高效性​

• ​写入优化​：仅10%的ASH数据持久化到AWR，使用Direct-Path Insert减少日志生成
• ​数据关联​：通过SQL_ID、对象ID、等待事件参数进行关联分析系统瓶颈

三、版本操作指南与验证脚本​

​1. Oracle 10g：基础采样​

• ​生成ASH报告​：

--# 需要交互式输入时间范围
sqlplus / as sysdba
@?/rdbms/admin/ashrpt.sql
---
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
输入 report_type 的值:

• 验证缓冲区参数​： 

-- 检查内存分配
SELECT * FROM v$sgastat WHERE name LIKE '%ASH buffers%';
--11g
SYS@orcl> SELECT * FROM v$sgastat WHERE name LIKE '%ASH buffers%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  ASH buffers                  67108864
--19c
SYS@test19> SELECT * FROM v$sgastat WHERE name LIKE '%ASH buffers%';

shared pool    ASH buffers                  67108864          1
--23ai
SYS@CDB$ROOT> SELECT * FROM v$sgastat WHERE name LIKE '%ASH buffers%';

POOL           NAME                BYTES    CON_ID
______________ ______________ __________ _________
shared pool    ASH buffers       4194304         1

2. Oracle 11g/12c增强功能​

• ​RAC支持​：

--# 多实例报告生成，登录SCAN-IP地址，
@?/rdbms/admin/ashrpti.sql
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 18 14:56:58 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SYS@jshis1> @?/rdbms/admin/ashrpti.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

•  分析脚本查询异常​：

-- 定位行锁争用源头
-- 最近5分钟
SELECT sample_time, session_id, sql_id, blocking_session
FROM v$active_session_history
WHERE event='enq: TX - row lock contention'
  AND sample_time > SYSDATE - 5/1440;
  --23ai
  SYS@CDB$ROOT> SELECT sample_time, session_id, sql_id, blocking_session
  2  FROM v$active_session_history
  3  WHERE event='enq: TX - row lock contention'
  4*   AND sample_time > SYSDATE - 5/1440;

3. Oracle 19c,23ai

• ​生成报告​：

--验证数据库版本和支持情况
SELECT * FROM v$version;
SYS@CDB$ROOT> show parameter ash;
NAME                          TYPE        VALUE
----------------------------- ----------- ------------------
db_flash_cache_file           string
db_flash_cache_size           big integer 0
db_flashback_log_dest         string      /opt/oracle/FB_FRA
db_flashback_log_dest_size    big integer 100G
db_flashback_retention_target integer     1440
hash_area_size                integer     131072

--如果包出现不可用可以尝试编译修复
SYS@CDB$ROOT> ALTER PACKAGE DBMS_WORKLOAD_REPOSITORY COMPILE;
Package DBMS_WORKLOAD_REPOSITORY altered.
-- 检查内存中最旧数据时间
SYS@CDB$ROOT> SELECT MIN(sample_time) FROM v$active_session_history;

MIN(SAMPLE_TIME)
__________________________________
15-JUN-25 09.38.45.848000000 PM

  -- 开始时间（30分钟前）
  -- 输出HTML格式报告
-- 如果需要就设置SQL*Plus环境
SET LINESIZE 200
SET PAGESIZE 0
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET HEADING OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON

-- 指定输出文件
SPOOL /u01/app/oracle/ash_report.html

-- 生成报告
SELECT * 
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
  l_dbid     => (SELECT dbid FROM v$database),
  l_inst_num => (SELECT instance_number FROM v$instance),
  l_btime    => SYSDATE - 30/1440,
  l_etime    => SYSDATE
));

-- 结束输出
SPOOL OFF

 四、ASH报告保存路径​

使用绝对路径+时间戳命名​（如ash_$(date +%Y%m%d).html），避免文件覆盖；

同时注意保存的路径oracle用户有读写权限

​操作方式​	​Linux实现​	​Windows实现​
交互式生成	输入/u01/app/oraclereport.html	输入D:\report.html
脚本硬编码路径	Shell中写/u01/app/oracle/report.html	BAT中写D:\report.html
SPOOL重定向	SPOOL /u01/app/oracle/report.html	同Linux（需SQL*Plus环境）
​默认位置​	$ORACLE_HOME/bin或~	%ORACLE_HOME%\RDBMS\ADMIN或用户目录

Linux下生成报告

• 1. 手动生成报告时指定路径​

执行ashrpt.sql脚本时，在最后一步输入带绝对路径的文件名​：

sqlplus / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql
# 按提示操作：
Enter value for report_type: html
Enter value for begin_time: -30  --例如30分钟前
Enter value for duration: 30     -- 持续30分钟
Enter value for report_name: /home/oracle/reports/ash_20250618.html  --自定义路径

• 2. 使用SPOOL命令重定向​

在SQL*Plus中直接生成并保存到指定路径：

SET PAGESIZE 0 LONG 1000000 TRIMSPOOL ON
SPOOL /u01/app/oracle/ash_report.html  -- 指定输出路径
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(...));
SPOOL OFF

• 3. 自动化脚本中硬编码路径​

在Shell脚本中直接写入路径（示例run_ash.sh）

#!/bin/bash
sqlplus -s / as sysdba <<EOF
@$ORACLE_HOME/rdbms/admin/ashrpt.sql <<INPUT
html
-30
30
/home/oracle/ash_reports/ash_$(date +%Y%m%d).html  # 固定输出目录
INPUT
EOF

Windows系统设置方法​

• ​1. 交互式生成时指定路径​

  sqlplus / as sysdba
  SQL> @C:\app\oracle\product\19.0.0\dbhome_1\RDBMS\ADMIN\ashrpt.sql
  # 输入报告参数后：
  Enter value for report_name: D:\oracle_reports\ash_report.html  # 自定义路径

  • 2. 批处理脚本固定路径​
  创建BAT脚本（如create_ash.bat）：

  @echo off
  sqlplus -s / as sysdba @C:\scripts\create_ash.sql > D:\ash_output\ash_%date:~0,4%%date:~5,2%%date:~8,2%.html
  --其中create_ash.sql内容：
  SET ECHO OFF
  SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(...));
  EXIT;
  --注意复制上面的DBMS_放在()中