Oracle AWR和ASH诊断工具使用指南

最新推荐文章于 2025-06-19 21:53:51 发布

笑远

最新推荐文章于 2025-06-19 21:53:51 发布

阅读量904

点赞数 15

CC 4.0 BY-SA版权

分类专栏：数据库运维深入浅出文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/weixin_38428874/article/details/145923179

数据库运维深入浅出专栏收录该内容

11 篇文章

订阅专栏

Oracle AWR和ASH诊断工具使用指南

在Oracle数据库性能管理与调优过程中，**AWR（Automatic Workload Repository）和ASH（Active Session History）**是两种关键的诊断工具。它们提供了丰富的数据和报告，帮助数据库管理员（DBA）识别并解决性能瓶颈。本文将详细介绍AWR和ASH的功能、使用方法及最佳实践，助您高效利用这些工具进行数据库性能诊断和优化。

Oracle AWR简介
- 1.1 AWR的功能与作用
- 1.2 AWR的架构与组件
Oracle ASH简介
- 2.1 ASH的功能与作用
- 2.2 ASH的数据采集与存储
AWR的使用方法
ASH的使用方法
结合AWR和ASH进行性能诊断
- 5.1 识别性能瓶颈
- 5.2 细化问题分析
最佳实践与建议
常见问题与解决方案
- 7.1 AWR报告生成失败
- 7.2 ASH数据不完整或缺失
总结

1. Oracle AWR简介

1.1 AWR的功能与作用

**AWR（Automatic Workload Repository）**是Oracle数据库自带的一个强大的性能诊断和调优工具。它通过自动收集和存储数据库性能数据，帮助DBA分析和解决性能问题。

主要功能包括：

性能数据收集：定期收集关键性能指标，如CPU使用率、内存使用、I/O统计、SQL语句性能等。
历史数据存储：存储一段时间内的性能数据，便于趋势分析和历史比较。
报告生成：生成详细的性能报告，帮助识别瓶颈和优化点。
自动调优建议：结合Oracle的自动诊断功能，提供调优建议。

1.2 AWR的架构与组件

AWR主要由以下组件组成：

AWR快照（Snapshot）：AWR通过定期创建快照来记录数据库的性能状态。默认情况下，每小时创建一个快照，保存7天的数据。
AWR表：AWR数据存储在SYSAUX表空间的相关AWR表中，如dba_hist_snapshot、dba_hist_sysmetric_summary等。
AWR报告：基于快照数据生成的详细报告，包含系统活动、性能瓶颈、热点SQL等信息。

2. Oracle ASH简介

2.1 ASH的功能与作用

**ASH（Active Session History）**是Oracle数据库中的另一个关键诊断工具，专注于活动会话的实时监控和历史数据记录。它通过采样活动会话的信息，提供细粒度的性能分析。

主要功能包括：

实时监控：持续采样当前活动会话的信息，提供近实时的性能视图。
细粒度分析：记录会话级别的详细信息，如等待事件、SQL语句、用户信息等。
历史回溯：存储一段时间内的活动会话数据，便于回溯分析历史性能问题。

2.2 ASH的数据采集与存储

ASH通过在V$ACTIVE_SESSION_HISTORY视图中记录会话信息实现数据采集。Oracle会定期（通常每秒）从活跃会话中抽取一定比例的数据，并存储在ASH表中。

关键点：

采样频率：默认每秒采样一次，根据系统负载动态调整采样间隔。
数据存储：ASH数据也是存储在SYSAUX表空间内，与AWR共享部分存储资源。
保留时间：由系统参数决定，通常为7天，可根据需要调整。

3. AWR的使用方法

3.1 生成AWR报告

AWR报告是通过特定的SQL脚本或Oracle Enterprise Manager（OEM）生成的。以下是通过SQL*Plus生成AWR报告的步骤：

连接数据库
```
sqlplus / as sysdba
```
运行awrrpt.sql脚本
```
@?/rdbms/admin/awrrpt.sql
```
选择报告类型

系统会提示选择报告类型（HTML或文本），以及起始和结束快照ID。
```
Enter value for report_type:  html
Enter value for num_days:  7
```
生成报告

脚本将生成一个AWR报告文件，包含详细的性能数据和分析。

3.2 解读AWR报告

AWR报告结构复杂，但关键部分主要包括：

报告摘要（Snapshot Beginning and Ending Snapshot Details）

显示报告涵盖的时间范围及快照信息。
负载概述（Load Profile）

提供系统总体性能指标，如事务数、执行请求数、CPU使用率、物理和逻辑读写等。
系统统计（Instance Efficiency Percentages）

展示关键效率指标，如缓冲区缓存命中率、共享池命中率等。
等待事件（Top Wait Events）

列出耗时最多的等待事件，帮助识别性能瓶颈。
SQL统计（SQL Statistics）

列出消耗最多资源的SQL语句，包括执行次数、CPU时间、磁盘读写等。
段统计（Segment Statistics）

提供对象级别的统计信息，如表或索引的逻辑读、物理读、块改变次数等。
建议和优化（Recommendations and Optimizations）

基于收集的数据，提供优化建议，如调整内存参数、优化SQL语句等。

3.3 AWR报告中的关键部分

目标识别性能瓶颈：

Top Wait Events：高耗时的等待事件往往指示性能瓶颈。例如，db file sequential read可能表示单块读性能问题，buffer cache可能指示缓存命中率低。
SQL Statistics：识别消耗最多资源的SQL语句，集中优化这些热点SQL。
Load Profile：分析系统负载的整体趋势，判断是否存在异常波动。

优化方向：

调整内存参数：如SGA和PGA的大小，提升缓冲区缓存命中率。
优化SQL语句：通过索引优化、重构查询等手段，提高SQL性能。
I/O优化：分析物理读写性能，优化存储系统配置。

4. ASH的使用方法

4.1 生成ASH报告

ASH报告通常通过OEM生成，但也可以通过查询V$ACTIVE_SESSION_HISTORY视图来自定义分析。以下是使用SQL查询ASH数据的示例：

连接数据库
```
sqlplus / as sysdba
```

运行查询

获取指定时间范围内的活动会话信息：

SELECT sample_time, session_id, session_serial#, user_id, event, wait_class, sql_id
FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP('2023-10-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
                   AND TO_TIMESTAMP('2023-10-01 11:00:00', 'YYYY-MM-DD HH24:MI:SS');

使用ASH_REPORT_TEXT包生成报告

Oracle提供ASH_REPORT_TEXT包，可以生成ASH报告。以下是生成报告的示例PL/SQL代码：

SET LONG 100000
SET PAGESIZE 1000
SET LINESIZE 200
SET WRAP OFF
SET TRIMSPOOL ON
SET TRIMOUT ON

SPOOL ash_report.txt

SELECT ash_report_text.report(
          l_dbid        => (SELECT dbid FROM v$database),
          l_inst_num    => (SELECT instance_number FROM v$instance),
          l_bid_1       => to_number(to_char(
                             to_date('2023-10-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),'J')),
          l_bid_2       => to_number(to_char(
                             to_date('2023-10-01 11:00:00', 'YYYY-MM-DD HH24:MI:SS'),'J')))
FROM dual;

SPOOL OFF

4.2 解读ASH报告

ASH报告提供了活动会话的实时和历史数据，关键部分包括：

会话活动摘要（Session Activity Summary）

展示不同会话的活动分类，如等待事件类型、事务状态等。
等待事件分析（Wait Event Analysis）

详细列出各类等待事件的发生频率和时间，帮助识别当前最具影响力的等待事件。
SQL语句分析（SQL Analysis）

显示活跃的SQL语句及其执行情况，包括执行次数、等待时间等。
用户活动分析（User Activity Analysis）

按用户维度展示会话活动，帮助识别哪些用户或应用程序对性能影响最大。

4.3 ASH报告中的关键部分

实时问题诊断：

等待事件分布：通过分析等待事件的分布，快速定位当前系统的性能瓶颈。
热点SQL识别：识别消耗最多资源的SQL语句，集中进行优化。
会话行为分析：了解不同会话的行为模式，识别异常会话或死锁情况。

历史问题回溯：

趋势分析：结合历史ASH数据，分析性能问题的发生频率和持续时间。
事件关联：将等待事件与具体SQL语句、用户行为关联，定位问题根源。

5. 结合AWR和ASH进行性能诊断

AWR和ASH各有侧重，结合使用可以提供更全面的性能分析。以下是结合使用AWR和ASH进行性能诊断的步骤：

5.1 识别性能瓶颈

通过AWR报告获取整体性能概览：
- 负载概述：了解系统的整体负载水平。
- Top Wait Events：识别总体上最耗时的等待事件。
利用ASH深入分析具体瓶颈：
- 等待事件细化：通过ASH查看具体的等待事件发生在哪些会话、哪些SQL语句上。
- 实时监控：使用ASH获取当前系统的实时性能状态，结合AWR报告确认瓶颈。

5.2 细化问题分析

热点SQL优化：
- AWR提供了资源消耗最高的SQL语句列表。
- ASH进一步分析这些SQL语句在特定时间段内的执行情况，识别执行频率和等待事件。
会话行为分析：
- AWR提供了整体的会话统计。
- ASH则提供了具体会话的详细行为，帮助识别异常会话或锁等待情况。
趋势与历史分析：
- AWR的历史数据帮助理解性能问题的趋势。
- ASH的细粒度数据则帮助回溯历史中的具体问题发生点。

6. 最佳实践与建议

6.1 定期收集AWR快照

自动化快照：配置AWR快照的自动收集频率和保留时间，确保关键性能数据的可用性。
合理频率：根据系统负载和业务需求调整快照频率，避免过高频率占用过多资源。

6.2 利用ASH进行实时监控

实时监控工具：结合ASH数据，使用监控工具（如OEM）进行实时性能监控和告警配置。
灵活查询：定期运行ASH查询，获取系统的实时状态和潜在问题。

6.3 结合其他诊断工具

ADDM（Automatic Database Diagnostic Monitor）：结合AWR和ASH数据，利用ADDM提供的自动诊断功能，获取更智能的调优建议。
Oracle Enterprise Manager（OEM）：使用OEM集中管理和分析AWR、ASH报告，提升诊断效率。

6.4 优化资源配置

内存参数调整：根据AWR报表中的缓冲区缓存命中率等指标，优化SGA和PGA的配置。
I/O资源管理：通过AWR和ASH识别I/O瓶颈，优化存储系统的配置和分布。

6.5 定期进行性能测试

性能基准测试：在变更前后进行性能基准测试，利用AWR和ASH对比分析，评估变更影响。
负载模拟：通过模拟不同负载场景，使用AWR和ASH监控系统在各种条件下的表现。

7. 常见问题与解决方案

7.1 AWR报告生成失败

可能原因：

许可证问题：生成AWR报告需要拥有诊断包许可证。确保数据库拥有Diagnostics Pack许可证。
权限不足：执行awrrpt.sql脚本需要相应的权限。确保当前用户拥有SYSDBA权限。
快照数据不足：选择的快照范围内数据不足，无法生成报告。

解决方案：

检查许可证：

SELECT * FROM dba_feature_usage_statistics WHERE name = 'Diagnostics Pack';

确认权限：

使用具备SYSDBA权限的用户执行脚本。
调整快照范围：

确保选择的快照范围内有足够的数据，可以延长时间范围或选择其他快照。

7.2 ASH数据不完整或缺失

可能原因：

ASH快照参数配置不当：ASH_RETENTION参数设置过低，导致数据被过快删除。
系统负载过高：在极高负载下，ASH可能无法及时采样所有活动会话。
数据存储空间不足：SYSAUX表空间不足，导致ASH数据无法正常存储。

解决方案：

调整ASH保留时间：

增加ASH_RETENTION参数值，延长ASH数据保留时间。

EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
   retention => 1440, -- 保留24小时
   interval  => 60     -- 快照间隔60分钟
);

优化系统负载：

分析并优化高负载原因，确保ASH有足够资源进行数据采样。

扩展SYSAUX表空间：

确保SYSAUX表空间有足够的空间存储ASH数据。

ALTER DATABASE DATAFILE '/path/to/syaux01.dbf' RESIZE 10G;
-- 或
ALTER TABLESPACE SYSAUX ADD DATAFILE '/path/to/syaux02.dbf' SIZE 5G;

8. 总结

Oracle AWR和ASH是数据库性能管理中不可或缺的工具。AWR提供了全面的系统级性能数据，适用于整体性能分析与历史趋势分析；而ASH则提供了细粒度的会话级监控，适用于实时问题诊断和深入分析。通过合理配置和结合使用AWR与ASH，DBA能够高效识别和解决数据库性能问题，确保系统的稳定与高效运行。

关键要点：