46-Oracle ASH-报告生成

远方1609

已于 2025-06-18 17:16:31 修改

阅读量920

点赞数 12

CC 4.0 BY-SA版权

文章标签： oracle 数据库 sql database 大数据

于 2025-06-18 17:15:46 首次发布

本文链接：https://blog.youkuaiyun.com/sharpwt/article/details/148743974

小伙伴们，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_放在()中