外观
Oracle AWR 报告分析
AWR 报告简介
AWR（Automatic Workload Repository）是 Oracle 数据库的自动工作负载仓库，它会定期收集数据库的性能统计信息，并生成详细的性能报告。AWR 报告是 DBA 进行性能分析和调优的重要工具。
AWR 报告的主要内容
- 数据库基本信息：数据库版本、实例名、快照信息
- 工作量概览：DB Time、逻辑读、物理读、执行的 SQL 语句数
- TOP 5 等待事件：最主要的性能瓶颈
- SQL 统计信息：消耗资源最多的 SQL 语句
- 实例效率百分比：缓冲区命中率、库缓存命中率等
- 内存和 I/O 统计：内存使用情况和 I/O 性能
- 段统计信息：热点段分析
- 初始化参数：当前参数设置
生成 AWR 报告
使用 DBMS_WORKLOAD_REPOSITORY 包
sql
-- 查看快照信息
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC;
-- 生成 AWR 报告
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot();
-- 手动创建快照
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot();使用 awrrpt.sql 脚本
sql
-- 在 SQL*Plus 中执行
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
-- 按提示输入：
-- 1. 报告格式（html 或 text）
-- 2. 快照起始 ID
-- 3. 快照结束 ID
-- 4. 报告文件名使用 awrrpti.sql 脚本（指定实例）
sql
-- 在 RAC 环境中指定实例生成报告
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
-- 按提示输入：
-- 1. 报告格式
-- 2. 数据库 ID
-- 3. 实例 ID
-- 4. 快照起始 ID
-- 5. 快照结束 ID
-- 6. 报告文件名使用 Enterprise Manager
- 登录 Oracle Enterprise Manager
- 导航到 "性能" → "性能报告"
- 选择 "AWR 报告"
- 设置时间范围和其他参数
- 点击 "生成报告"
AWR 报告分析方法
1. 数据库基本信息分析
关注要点：
- 数据库版本和补丁级别
- 快照时间范围和持续时间
- 数据库启动时间
分析技巧：
- 确认报告覆盖了性能问题发生的时间段
- 注意数据库版本对性能特性的影响
2. 工作量概览分析
关注指标：
- DB Time：数据库时间，单位为秒
- DB CPU：CPU 时间，单位为秒
- Redo Size：重做日志大小
- Logical Reads：逻辑读数量
- Physical Reads：物理读数量
- Executions：SQL 执行次数
- Parse Calls：解析调用次数
- Hard Parses：硬解析次数
- Sorts：排序操作次数
分析技巧：
- 计算 DB Time/Elapsed Time 比值，判断数据库负载程度
- 计算 Hard Parses/Parse Calls 比值，判断硬解析比例
- 分析 Redo Size 变化，判断事务量大小
3. TOP 5 等待事件分析
关注要点：
- 等待事件名称
- 等待时间（Wait Time）
- 等待次数（Waits）
- 平均等待时间（Avg Wait）
常见等待事件：
- db file sequential read：单个数据块的顺序读取（通常是索引扫描）
- db file scattered read：多个数据块的分散读取（通常是全表扫描）
- log file sync：LGWR 将重做日志缓冲区写入磁盘的等待
- buffer busy waits：缓冲区忙等待
- enqueue：锁等待
- cursor: pin S：游标共享等待
- direct path read/write：直接路径读写
分析技巧：
- 首先解决排名第一的等待事件
- 结合其他部分的信息进行综合分析
- 注意平均等待时间，识别严重的性能瓶颈
4. SQL 统计信息分析
关注指标：
- Elapsed Time：SQL 执行时间
- CPU Time：CPU 消耗时间
- Buffer Gets：逻辑读数量
- Disk Reads：物理读数量
- Executions：执行次数
- Parse Calls：解析调用次数
- Rows Processed：处理的行数
分析技巧：
- 重点关注 Elapsed Time per Execution 高的 SQL
- 检查 Buffer Gets per Exec 异常高的 SQL
- 结合执行计划分析 SQL 性能问题
- 注意 SQL 的执行频率
5. 实例效率百分比分析
关注指标：
- Buffer Nowait %：缓冲区无等待百分比（应 > 99%）
- Buffer Hit %：缓冲区命中率（应 > 95%）
- Library Hit %：库缓存命中率（应 > 95%）
- Execute to Parse %：执行与解析比例（应 > 90%）
- Parse CPU to Parse Elapsd %：解析 CPU 时间与解析总时间比例（应 > 70%）
- Redo NoWait %：重做日志无等待百分比（应 > 99%）
- In-memory Sort %：内存排序百分比（应 > 95%）
分析技巧：
- 低于阈值的指标可能是性能瓶颈
- 结合其他部分的信息进行综合判断
- 注意指标的变化趋势
6. 内存统计信息分析
关注区域：
- SGA：共享池、缓冲区高速缓存、重做日志缓冲区
- PGA：程序全局区
分析指标：
- Shared Pool：库缓存、字典缓存的使用情况
- Buffer Cache：缓冲区命中率、脏缓冲区数量
- PGA：PGA 使用大小、排序区使用情况
分析技巧：
- 检查共享池是否存在内存不足
- 分析缓冲区高速缓存命中率
- 检查 PGA 是否过大或过小
7. I/O 统计信息分析
关注指标：
- Tablespace IO Stats：表空间 I/O 统计
- File IO Stats：数据文件 I/O 统计
- IO Stats by Function：按功能分类的 I/O 统计
分析技巧：
- 识别 I/O 密集的表空间和数据文件
- 检查 I/O 等待时间
- 分析读写比例
8. 段统计信息分析
关注指标：
- Physical Reads：物理读数量
- Logical Reads：逻辑读数量
- Buffer Busy Waits：缓冲区忙等待次数
- Row Lock Waits：行锁等待次数
分析技巧：
- 识别热点段（Hot Segments）
- 分析段的访问模式
- 考虑对热点段进行分区或索引优化
9. 初始化参数分析
关注参数：
- sga_target：SGA 目标大小
- pga_aggregate_target：PGA 聚合目标大小
- shared_pool_size：共享池大小
- db_cache_size：数据库缓冲区大小
- log_buffer：重做日志缓冲区大小
- optimizer_mode：优化器模式
- workarea_size_policy：工作区大小策略
分析技巧：
- 检查参数设置是否合理
- 分析参数变化对性能的影响
- 参考 Oracle 最佳实践调整参数
AWR 报告分析实例
分析步骤
- 收集 AWR 报告：生成性能问题发生时间段的 AWR 报告
- 分析工作量概览：了解数据库负载情况
- 识别 TOP 等待事件：确定主要性能瓶颈
- 分析高消耗 SQL：找出资源消耗最多的 SQL
- 检查实例效率：分析各项性能指标
- 综合分析：结合所有信息进行综合判断
- 制定优化方案：根据分析结果制定优化策略
案例分析
场景：数据库响应缓慢，用户抱怨查询时间长
AWR 报告分析：
工作量概览：
- DB Time/Elapsed Time = 15.2，表明数据库负载很高
- Hard Parses/Parse Calls = 35%，硬解析比例过高
TOP 5 等待事件：
- db file sequential read (Wait Time: 45%)
- cursor: pin S (Wait Time: 20%)
- log file sync (Wait Time: 15%)
SQL 统计信息：
- 发现一个 SQL 语句消耗了 60% 的 Elapsed Time
- 该 SQL 执行计划显示全表扫描
实例效率：
- Library Hit % = 85%，低于阈值
- Buffer Hit % = 92%，接近阈值
优化方案：
- 为高频查询的表添加合适的索引，减少全表扫描
- 使用绑定变量，减少硬解析
- 调整共享池大小，提高库缓存命中率
- 优化 LGWR 性能，减少 log file sync 等待
AWR 报告的高级使用
1. AWR 快照管理
sql
-- 查看快照设置
SELECT * FROM dba_hist_wr_control;
-- 修改快照设置（每 30 分钟收集一次，保留 7 天）
EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 7*24*60, -- 保留分钟数
interval => 30, -- 收集间隔分钟数
topnsql => 100 -- 每个快照收集的 TOP SQL 数量
);
-- 删除旧快照
EXEC DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
low_snap_id => 1000,
high_snap_id => 1100
);2. AWR 基线管理
AWR 基线：是一组快照的集合，用于比较不同时期的性能数据
sql
-- 创建 AWR 基线
EXEC DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_snap_id => 1000,
end_snap_id => 1010,
baseline_name => 'NORMAL_LOAD_BASELINE'
);
-- 查看 AWR 基线
SELECT * FROM dba_hist_baseline;
-- 删除 AWR 基线
EXEC DBMS_WORKLOAD_REPOSITORY.drop_baseline(
baseline_name => 'NORMAL_LOAD_BASELINE'
);3. AWR 报告比较
使用 awrddrpt.sql 脚本：
sql
-- 比较两个时间段的 AWR 报告
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
-- 按提示输入：
-- 1. 报告格式
-- 2. 第一个快照起始 ID
-- 3. 第一个快照结束 ID
-- 4. 第二个快照起始 ID
-- 5. 第二个快照结束 ID
-- 6. 报告文件名分析技巧：
- 比较两个时间段的性能差异
- 识别性能退化的原因
- 评估优化措施的效果
4. 自动性能诊断
使用 ADDM（Automatic Database Diagnostic Monitor）：
sql
-- 运行 ADDM
EXEC DBMS_ADVISOR.execute_task('ADDM', null, null, 'ADDMDIR', 'BEGIN_SNAPSHOT=1000 END_SNAPSHOT=1010');
-- 查看 ADDM 报告
SELECT DBMS_ADVISOR.get_task_report('ADDM') FROM dual;分析技巧：
- ADDM 会自动分析 AWR 数据并提供优化建议
- 优先考虑 ADDM 建议的优化措施
- 结合人工分析进行综合判断
AWR 报告优化策略
1. SQL 优化
- 使用绑定变量：减少硬解析
- 优化 SQL 语句：重写复杂 SQL，使用合适的连接方法
- 添加索引：为高频查询添加合适的索引
- 使用分区表：对于大表使用分区技术
- 调整优化器参数：根据实际情况调整优化器模式
2. 内存优化
- 调整 SGA 大小：根据数据库负载调整 SGA_TARGET
- 调整 PGA 大小：根据排序和哈希操作调整 PGA_AGGREGATE_TARGET
- 优化共享池：适当增加 SHARED_POOL_SIZE
- 优化缓冲区缓存：根据访问模式调整 DB_CACHE_SIZE
3. I/O 优化
- 使用 ASM：使用自动存储管理提高 I/O 性能
- 数据文件分布：将数据文件分布到不同的磁盘组
- 使用快速存储：对于高频访问的数据使用 SSD
- 优化表空间：使用合适的表空间类型和块大小
- 减少 I/O 操作：通过索引优化减少物理读
4. 并发优化
- 减少锁争用：优化事务设计，减少锁持有时间
- 使用适当的隔离级别：根据业务需求选择合适的事务隔离级别
- 优化并行执行：对于大型操作使用适当的并行度
- 监控并解决死锁：及时发现和解决死锁问题
5. 配置优化
- 调整初始化参数：根据 Oracle 最佳实践调整参数
- 优化网络配置：调整网络参数，减少网络延迟
- 使用连接池：减少连接建立和关闭的开销
- 定期维护数据库：执行统计信息收集、索引重建等维护操作
常见问题（FAQ）
Q1: AWR 报告生成失败怎么办？
A1: AWR 报告生成失败的常见原因及解决方法：
- 快照不存在：确保指定的快照 ID 存在
- 权限不足：确保用户有 SELECT_CATALOG_ROLE 权限
- 空间不足：检查 SYSAUX 表空间是否有足够空间
- 参数设置：确保 STATISTICS_LEVEL 参数设置为 TYPICAL 或 ALL
Q2: 如何判断 AWR 报告的时间范围是否合适？
A2: 选择合适的 AWR 报告时间范围：
- 问题发生时间：确保报告覆盖性能问题发生的时间段
- 持续时间：一般选择 15-60 分钟的时间段，太短可能无法捕捉到问题，太长可能包含过多无关信息
- 代表性：选择能够代表正常负载或问题负载的时间段
Q3: 如何分析 AWR 报告中的硬解析比例过高问题？
A3: 分析和解决硬解析比例过高的问题：
- 检查 Hard Parses/Parse Calls 比值：如果超过 10%，说明硬解析比例过高
- 原因分析：
- 未使用绑定变量
- 共享池大小不足
- CURSOR_SHARING 参数设置不当
- 解决方法：
- 使用绑定变量重写 SQL
- 适当增加 SHARED_POOL_SIZE
- 考虑设置 CURSOR_SHARING=FORCE（谨慎使用）
Q4: 如何使用 AWR 报告识别 I/O 瓶颈？
A4: 识别 I/O 瓶颈的方法：
- 查看 TOP 等待事件：关注 db file sequential read、db file scattered read 等 I/O 相关等待事件
- 分析 I/O 统计信息：检查 Tablespace IO Stats 和 File IO Stats 部分
- 查看物理读和逻辑读：分析 Physical Reads 和 Logical Reads 的比例
- 检查段统计信息：识别 I/O 密集的热点段
Q5: AWR 报告中的 "Buffer Busy Waits" 等待事件如何解决？
A5: 解决 Buffer Busy Waits 等待事件的方法：
- 原因分析：
- 热点块竞争
- 段头竞争
- 回滚段竞争
- 解决方法：
- 对于热点块：使用更小的块大小，或考虑分区
- 对于段头竞争：增加 FREELISTS 和 FREELIST GROUPS
- 对于回滚段竞争：确保有足够的回滚段
- 使用自动段空间管理（ASSM）
Q6: 如何使用 AWR 报告评估优化措施的效果？
A6: 评估优化措施效果的方法：
- 生成优化前后的 AWR 报告：在相同的负载条件下生成报告
- 比较关键指标：
- DB Time/Elapsed Time 比值
- TOP 5 等待事件的变化
- 资源消耗最多的 SQL 的性能变化
- 实例效率百分比的变化
- 使用 AWR 比较报告：使用 awrddrpt.sql 生成比较报告
- 综合评估：结合业务指标（如响应时间、吞吐量）进行综合评估
Q7: AWR 报告中的 "log file sync" 等待事件如何解决？
A7: 解决 log file sync 等待事件的方法：
- 原因分析：
- LGWR 进程性能不足
- 重做日志文件所在磁盘 I/O 性能差
- 事务提交过于频繁
- 解决方法：
- 使用更快的存储设备存放重做日志
- 适当增加提交间隔，减少提交频率
- 确保重做日志文件分布在不同的磁盘上
- 调整 LOG_BUFFER 参数大小
Q8: 如何减少 AWR 报告的生成时间？
A8: 减少 AWR 报告生成时间的方法：
- 选择合适的时间范围：不要选择过长的时间范围
- 使用文本格式：文本格式的报告生成速度比 HTML 格式快
- 限制 SQL 数量：通过调整 TOPNSQL 参数限制收集的 SQL 数量
- 使用 Enterprise Manager：对于大型数据库，使用 Enterprise Manager 生成报告可能更高效