数据库教程FGMT11‑Oracle性能优化之性能收集与报告分析

数据库教程FGMT11‑Oracle性能优化之性能收集与报告分析

前言

在Oracle数据库运维调优工作中，故障发生之后快速定位瓶颈离不开完整的性能数据采集与报告解析能力。当数据库出现业务响应变慢、CPU冲高、IO压力上涨会话阻塞等现象，DBA不能只依靠实时视图v$session、v$sql做瞬时判断，瞬时视图只能看到当下状态，无法回溯已经结束的历史故障。AWR、ADDM、ASH、Statspack是Oracle体系下核心性能诊断工具，负责采集、保存、分析数据库历史负载、等待事件、SQL资源消耗信息，帮助回溯过去时间窗口内发生的性能问题。风哥教程本文围绕性能工具底层原理、快照管理、各类报告生成、报告解读要点、基线管理、数据字典视图展开完整讲解。风哥 itpux‑com

本套风哥教程面向数据库运维DBA、运维工程师、后端开发人员，全部实验标准化环境配置：主机名称fgedu‑net‑cn，硬件规格64G物理内存、8颗CPU；数据库实例名fgedudb，数据库名fgedudb，测试业务用户名fgedu，文件根目录统一为/fgedudb，整套实验基于Oracle19c企业版完成。风哥教程本文分为前言大纲介绍、核心理论知识、实战操作演练、总结四大模块；实战章节包含大量可直接复制运行SQL脚本、操作系统操作步骤，读者可以在测试环境完整复现全部实验，掌握生产环境故障回溯、性能报告生成与分析整套工作流程。网上搜索风哥教程可以学习全套数据库教程

内容大纲

1. Oracle内置性能诊断工具整体架构与工具适用场景区分
2. Statspack工具原理、安装配置、快照采集与报告生成
3. AWR自动负载信息库原理，快照采集机制、快照生命周期管理
4. AWR基线baseline概念，固定基线、移动窗口基线、基线模板运维
5. AWR各类报告：AWR主报告、AWRSQL单SQL报告、AWRDD对比报告
6. ADDM自动数据库诊断顾问工作原理、任务管理与报告解读
7. ASH活动会话历史原理、ASH报告生成与故障排查使用场景
8. AWR相关核心数据字典视图说明
9. 各类报告标准阅读思路、重点指标、等待事件分析要点
10. 生产环境性能采集运维规范，许可注意事项，故障排查标准流程

一、核心理论知识

本章节为本套风哥教程理论基础，只有理解各个性能工具底层实现逻辑，才可以选对合适工具处理不同故障场景，避免误读报告指标。风哥教程 113257174

1.1 Oracle性能诊断工具整体架构

Oracle19c提供多套性能采集工具，不同工具存储位置、采集粒度、许可要求、适用场景存在明显区别，不能混用。

1. Statspack：早期免费性能采集工具，不需要Diagnostics Pack许可；数据存放在业务用户PERFSTAT表；快照粒度分钟级别；标准版、企业版均可以使用；适合历史性能采集。
2. AWR（Automatic Workload Repository，自动负载信息库）：企业版内置，需要Diagnostics Pack许可；性能数据持久化保存在SYSAUX表空间；数据库默认每小时自动生成快照；保存实例级别负载、等待事件、SQL统计、段统计；用于时间段级别的数据库整体性能分析。
3. ADDM（Automatic Database Diagnostic Monitor，自动数据库诊断顾问）：基于AWR快照数据做智能分析；自动识别系统瓶颈，输出问题影响占比、根因定位、优化建议；ADDM不采集原始数据，全部依赖AWR已经保存的快照数据。
4. ASH（Active Session History，活动会话历史）：采样活动会话，内存环形缓冲区保存，部分数据刷入AWR；以会话为最小粒度，记录每一个活动会话的等待事件、SQL_ID、对象号；适合瞬时突发故障、短时间尖峰故障回溯；可以看到故障时刻会话在做什么操作。

许可重要提示：AWR/ADDM/ASH功能企业版虽然安装自带，商用生产环境使用必须购买Oracle Diagnostics Pack授权；Statspack无额外许可限制，是标准版环境唯一历史性能采集手段。网上搜索风哥教程可以学习全套数据库教程

1.2 Statspack工具原理

Statspack通过PERFSTAT用户下的一套数据表保存快照数据；快照采集会读取v$系列动态视图，把负载、等待、SQL、buffer统计写入普通数据表。

• 快照：手动或者定时job触发采集，生成snap_id；对比两个snap_id生成statspack报告。
• 局限：没有ASH会话粒度信息；不具备ADDM智能分析；但是不受许可约束。
• 存储：快照数据占用普通表空间，需要定期清理旧快照，防止表空间持续膨胀。

1.3 AWR自动负载信息库核心原理

AWR核心单元为快照Snapshot，快照就是某一个时间点数据库全部性能统计的采集镜像。

1. 默认配置：每60分钟自动生成一次快照；快照数据默认保留8天；超过保留时间旧快照自动purge清理；数据存储在SYSAUX表空间，业务繁忙数据库会持续消耗SYSAUX存储空间。
2. 初始化参数STATISTICS_LEVEL控制AWR是否生效：TYPICAL是生产标准配置，开启全部常规性能采集；设置为BASIC会关闭AWR快照采集，同时ADDM、ASH也随之失效。
3. 快照分为自动快照、手工快照；压测、变更前后，可手动执行存储过程立刻生成快照，用于精准捕获变更前后性能差异。
4. AWR Baseline基线：把一组连续快照标记为基线；基线对应的快照不会被自动purge删除；基线分为固定基线、移动窗口基线、基线模板。
   • 固定基线：选定过去某一段业务平稳的时间窗口快照永久保留，作为性能对比基准，业务异常时候拿故障时间段AWR和基线做对比。
   • 移动窗口基线：使用AWR全部保留窗口内快照，用于自适应阈值计算。
   • 基线模板：可以设置未来时间段自动创建基线，比如月末结算、大促窗口，到时间自动保存快照作为基线。

1.4 ADDM自动数据库诊断顾问原理

ADDM任务会在每次AWR快照生成之后自动运行；读取前后两个快照的数据，执行内置调优专家逻辑，计算各个问题对数据库负载的影响百分比。
输出内容包含：问题描述、问题带来负载占比、根因、多条可落地优化建议，同时标注哪些是非问题区域。ADDM可以分析：CPU瓶颈、IO瓶颈、锁等待、解析瓶颈、热块、SQL消耗资源高、PGA/SGA配置不合理等场景。

注意：ADDM只是顾问建议，不能直接自动修改数据库参数，需要DBA人工评估建议可行性再实施。风哥数据库教程 itpux‑com

1.5 ASH活动会话历史原理

ASH在SGA开辟环形内存缓冲区，对活动会话做采样，每秒采集一次活动会话样本；非空闲等待的会话才会被采样。内存缓冲区容量有限，旧样本会被覆盖；一部分样本会随着AWR快照刷入AWR持久化表。
适用场景：数据库突发几分钟尖峰故障，AWR快照间隔1小时粒度太粗，无法捕捉短时间故障，使用ASH报告可以按分钟粒度回溯这几分钟会话等待、SQL、对象信息。

1.6 各类报告说明

1. awrrpt.sql：标准AWR实例报告，输入起始、结束snap_id，输出时间段内实例整体负载、Top等待事件、Top SQL、IO、内存、段统计。
2. awrsqrpt.sql（AWRSQL报告）：针对单条sql_id，输出该SQL在快照时间段完整执行统计、执行计划变化、资源消耗；专门用于单条SQL性能恶化排查。
3. awrddrpt.sql（AWRDD对比报告）：对比两个不同时间段AWR数据；适合业务版本上线前后对比，看负载、等待事件、SQL资源消耗发生了哪些变化。
4. ashrpt.sql：ASH活动会话报告，可以指定开始结束时间，输出会话采样统计。

1.7 AWR核心数据字典视图

• dba_hist_wr_control：查看AWR快照间隔、数据保留时长、topnsql采集数量。
• dba_hist_snapshot：全部AWR快照列表，snap_id、快照起止时间。
• dba_hist_baseline：AWR基线信息。
• dba_hist_active_sess_history：持久化保存ASH会话采样数据。
• dba_hist_sqlstat：AWR中SQL历史统计信息。
• dba_hist_system_event：AWR保存的等待事件历史统计。

重要风险：AWR所有历史数据都保存在SYSAUX表空间，快照保留时间设置过长会造成SYSAUX持续暴涨，生产环境需要定期监控SYSAUX空间使用率。

二、实战操作演练

本套风哥教程全部实战操作，操作主机fgedu‑net‑cn，数据库fgedudb，业务用户fgedu，目录/fgedudb，硬件规格64G内存8CPU。

环境说明：操作系统登录oracle用户，设置环境变量指向fgedudb实例；AWR、ADDM、Statspack大部分操作需要sysdba权限。脚本输出报告文件默认生成在sqlplus当前工作目录，生产环境一般设置输出路径到/fgedudb/dump目录。

2.1 操作系统与数据库环境校验

2.1.1操作系统层面检查（主机fgedu‑net‑cn）

#确认主机名称
hostname
#确认ORACLE_HOME路径全部指向/fgedudb
echo $ORACLE_HOME
#查看内存硬件，确认64G内存
free -h
#查看CPU，确认8CPU
lscpu

校验输出：hostname输出fgedu‑net‑cn，总内存64G，逻辑CPU数量8。

2.1.2 数据库关键初始化参数确认（64G内存8CPU规格）

登录sqlplus / as sysdba查看关键参数

show parameter statistics_level;
show parameter memory_target;
show parameter sga_target;
show parameter pga_aggregate_target;

适配64G内存主机spfile标准配置

alter system set memory_max_target=48G scope=spfile;
alter system set memory_target=48G scope=spfile;
alter system set statistics_level=TYPICAL scope=spfile;

statistics_level=TYPICAL必须开启，AWR、ASH、ADDM全部依赖这个参数；设置BASIC将全部关闭性能采集。

2.1.3 用户权限准备

create user fgedu identified by fgedudb default tablespace users temporary tablespace temp;
grant connect,resource to fgedu;
grant select_catalog_role to fgedu;
grant advisor to fgedu;
grant execute on dbms_workload_repository to fgedu;
grant execute on dbms_addm to fgedu;

2.2 Statspack工具安装与实操（无Diagnostics Pack许可环境使用）

Statspack脚本位于$ORACLE_HOME/rdbms/admin/spcreate.sql，安装创建PERFSTAT用户。

2.2.1 执行安装statspack

conn / as sysdba
@?/rdbms/admin/spcreate.sql

脚本交互提示输入PERFSTAT用户默认表空间、临时表空间；生产建议指定users为默认表空间，temp临时表空间。

2.2.2 手工生成statspack快照

conn perfstat/perfstat
exec statspack.snap;

业务压测前后各执行一次snap生成快照。

2.2.3 生成statspack性能报告

@?/rdbms/admin/spreport.sql

按照提示输入起始snap_id、结束snap_id，输入报告输出文件名，生成文本格式statspack报告。

2.2.4 删除旧statspack快照，释放表空间

@?/rdbms/admin/sppurge.sql

2.3 AWR快照基础管理实操

2.3.1 查询当前AWR全局配置

SELECT snap_interval,retention,topnsql
FROM dba_hist_wr_control;

字段说明：

• snap_interval：自动快照生成间隔，单位分钟；默认60分钟。
• retention：快照数据保留时间，单位分钟；默认8天。
• topnsql：每次快照抓取资源消耗最大SQL条数。

2.3.2 修改AWR快照参数（64G 8CPU生产环境建议配置）

生产建议：快照间隔30分钟，保留时间设置30天，平衡故障回溯能力与SYSAUX空间压力。

BEGIN
dbms_workload_repository.modify_snapshot_settings(
interval => 30,
retention => 30 * 24 * 60,
topnsql => 200
);
END;
/

2.3.3 手工创建AWR快照（业务变更、压测前后使用）

conn / as sysdba
exec dbms_workload_repository.create_snapshot();

业务上线变更前执行一次，变更完成业务运行一段时间再执行一次，两个snap_id拿来生成AWR报告。

2.3.4 查询快照列表，获取snap_id

SELECT snap_id,
TO_CHAR(begin_interval_time,'YYYY‑MM‑DD HH24:MI') snap_begin,
TO_CHAR(end_interval_time,'YYYY‑MM‑DD HH24:MI') snap_end
FROM dba_hist_snapshot
ORDER BY snap_id DESC;

2.3.5 删除指定范围快照，回收SYSAUX空间

BEGIN
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 100,
high_snap_id =>120
);
END;
/

2.4 AWR基线Baseline完整实操

风哥数据库教程 itpux‑com

2.4.1 创建固定基线（选取业务平稳窗口快照作为性能基准）

BEGIN
dbms_workload_repository.create_baseline(
start_snap_id => 110,
end_snap_id =>120,
baseline_name => 'BUSINESS_NORMAL_BASELINE'
);
END;
/

基线对应的快照不会被自动purge清理。

2.4.2 查询已经存在的基线

SELECT baseline_id,baseline_name,start_snap_id,end_snap_id,expiration
FROM dba_hist_baseline;

2.4.3 删除基线

cascade=>false：仅删除基线定义，快照保留，等待AWR自动过期清理；cascade=>true同时删除对应快照数据。

BEGIN
dbms_workload_repository.drop_baseline(
baseline_name => 'BUSINESS_NORMAL_BASELINE',
cascade => FALSE
);
END;
/

2.4.4 创建基线模板（未来指定时间段自动生成基线，适合大促月末）

BEGIN
dbms_workload_repository.create_baseline_template(
template_name => 'MONTH_END_TPL',
start_time => TO_DATE('2026‑09‑30 22:00:00','YYYY‑MM‑DD HH24:MI:SS'),
end_time => TO_DATE('2026‑09‑30 23:30:00','YYYY‑MM‑DD HH24:MI:SS'),
expiration => 365
);
END;
/

2.5 各类AWR报告脚本生成实操

脚本存放路径：$ORACLE_HOME/rdbms/admin，sqlplus中?代表$ORACLE_HOME。报告输出文件写入sqlplus当前工作目录，生产环境切换到/fgedudb/dump目录再执行脚本。

2.5.1 标准AWR实例报告 awrrpt.sql

conn / as sysdba
@?/rdbms/admin/awrrpt.sql

交互步骤：

1. 选择输出格式 html / text，线上故障优先html格式可读性更强。
2. 输入要显示快照的天数，回车代表全部。
3. 输入起始snap_id。
4. 输入结束snap_id。
5. 输入输出报告文件名，例如fgedudb_awr_20260910.html。

2.5.2 AWRSQL单SQL报告 awrsqrpt.sql，针对指定sql_id

用于分析某一条SQL在一段时间内资源消耗、执行计划变更。

@?/rdbms/admin/awrsqrpt.sql

交互输入：报告格式、快照天数、起始snap、结束snap、目标sql_id、输出文件名。

2.5.3 AWR时间段对比报告awrddrpt.sql，版本上线前后对比

对比两个时间窗口负载差异，识别上线之后性能变化。

@?/rdbms/admin/awrddrpt.sql

2.5.4 RAC环境全局AWR报告

@?/rdbms/admin/awrgrpt.sql

2.6 ADDM自动诊断顾问实操

ADDM依赖AWR快照，每次AWR快照生成自动运行ADDM任务，也可以手工执行ADDM分析指定两个快照。

2.6.1 手工执行ADDM任务分析指定快照区间

conn / as sysdba
DECLARE
tname VARCHAR2(60);
BEGIN
tname := dbms_addm.analyze_range(
start_snap_id =>130,
end_snap_id =>140,
dbid => (select dbid from v$database)
);
dbms_output.put_line('ADDM任务名称:'||tname);
END;
/

2.6.2 输出ADDM文本报告

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET PAGESIZE 0
SET LINESIZE 200
SELECT dbms_addm.get_report('TASK_1234') FROM dual;

把上面task名称替换上一步返回的tname。也可以执行脚本@?/rdbms/admin/addmrpt.sql交互式生成ADDM报告。

2.7 ASH活动会话历史报告实操（处理短时突发故障）

ashrpt.sql按照时间而不是snap_id输入，适合几分钟尖峰故障回溯。

conn / as sysdba
@?/rdbms/admin/ashrpt.sql

交互输入：

1. 输出格式 html/text
2. 输入报告开始时间：YYYY‑MM‑DD HH24:MI:SS
3. 输入报告结束时间
4. 过滤目标实例号，单实例直接回车
5. 输出报告文件名

2.8 AWR报告阅读关键要点实战讲解

拿到AWR报告之后，不要直接钻到SQL细节，遵循由宏观到微观分析顺序。

1. 头部基本信息：确认数据库名称、版本、快照时间间隔，确认是故障发生对应的时间窗口。
2. Load Profile负载概要：每秒逻辑读、物理读、事务数、解析次数；看整体数据库负载量级。
3. Instance Efficiency Percentages实例效率指标：缓冲区命中率、库缓存命中率；命中率低代表存在严重扫描或者解析问题。
4. Top 5 Timed Foreground Events 前5等待事件，这是AWR报告最重要部分，定位系统最大瓶颈。
   • db file scattered read：大量全表扫描。
   • db file sequential read：索引读取IO压力。
   • log file sync：提交等待，redo日志、磁盘IO或者事务频繁提交。
   • latch free：闩锁竞争，硬解析、共享池压力。
5. SQL Statistics部分：按elapsed_time、cpu_time、logical reads排序的top SQL，定位消耗资源最高业务SQL。
6. Tablespace IO Stats 表空间IO统计：识别哪个表空间IO压力最大。
7. Segment Statistics段统计：定位热点表、热点索引对象。

实战排查顺序：Top5等待事件确定瓶颈类型 → TopSQL找到消耗资源SQL → 查看对应SQL执行计划 → 结合段统计确认热点对象。

2.9 生产故障完整排查工作流程

1. 业务反馈数据库变慢，确认故障发生精确时间窗口。
2. 查询dba_hist_snapshot找到覆盖故障时间的起始snap_id与结束snap_id。
3. 如果故障持续时间长（大于30分钟）：生成AWR报告 + ADDM报告；分析Top5等待事件、TopSQL。
4. 如果故障是短时几分钟尖峰，AWR快照间隔粒度太粗，优先生成ASH报告，按故障精确起止时间采集会话采样。
5. 单条SQL性能恶化，使用AWRSQL报告分析该sql_id历史统计与执行计划变化。
6. 版本上线前后性能退化，使用AWRDD对比报告对比上线前后两个时间段负载差异。
7. 参考ADDM给出的建议，但不要直接照搬执行，DBA结合业务场景人工评估风险，再实施变更。
8. 无Diagnostics Pack许可环境，使用Statspack采集历史性能数据做分析。
9. 所有参数、索引、SQL变更操作保留回退脚本，变更完成后再次采集性能报告对比前后变化。

三、风哥针对本文总结

本套风哥教程完整覆盖Oracle性能采集与报告分析整套知识，从Statspack、AWR、ADDM、ASH工具原理、许可区分，到AWR快照管理、基线baseline运维、多种报告生成脚本，报告指标阅读方法、生产故障排查完整实战命令。

1. 需要分清各个性能工具的定位：AWR适合小时级别实例整体负载；ASH适合分钟级短时突发故障回溯；ADDM基于AWR做智能问题诊断；Statspack无额外许可，适合Oracle标准版环境历史性能采集。商用生产环境使用AWR/ADDM/ASH必须确认已经具备Diagnostics Pack产品许可。
2. STATISTICS_LEVEL=TYPICAL是AWR、ASH、ADDM生效前提，修改为BASIC会全部关闭性能采集，生产环境严禁随意修改。
3. AWR快照数据存储于SYSAUX表空间，快照保留时间不要无限制拉长，保留时间越长SYSAUX占用越高，建议设置为30天，定期监控SYSAUX表空间使用率。
4. 基线baseline用于保存业务平稳期快照，用于后续故障做性能对比；创建基线的快照不会被自动清理，不再使用的基线务必执行drop_baseline删除，避免快照永久占用存储空间。
5. 解读AWR报告遵循由宏观到微观顺序：优先看Top5等待事件定位瓶颈，再往下定位TopSQL、热点对象，不要一上来直接盯着单条SQL，容易忽略系统层面瓶颈。
6. ADDM是顾问工具，输出建议只能作为参考，不能直接不加评估就执行，部分ADDM建议会带来业务风险，DBA需要结合业务实际情况做判断取舍。
7. 遇到短时几分钟的业务卡顿故障，优先选择ASH报告；AWR默认30分钟快照粒度，会丢失尖峰故障内部细节，无法定位瞬时问题。
8. 业务变更、版本上线、压测场景，变更前后手动执行create_snapshot生成手工快照，拿到精准的前后性能对比窗口，便于事后分析变更带来性能影响。