在 Oracle 数据库中，Automatic SQL History (ASH) 是一个非常重要的工具，用于收集和分析 SQL 语句的执行历史数据。ASH 提供了 SQL 语句执行频率、等待时间和 CPU 时间等信息，对于诊断性能问题和优化 SQL 查询至关重要。本文将详细介绍如何生成和解读 Oracle 中的 ASH 报告。
1. ASH 概述
ASH 是 Automatic Workload Repository (AWR) 的一部分，它记录了 SQL 语句的执行历史，包括执行次数、等待时间、CPU 时间等。这些数据对于分析 SQL 语句的性能问题和调优非常有用。
2. ASH 数据收集
ASH 数据由 Oracle 自动收集，无需额外配置。可以通过以下查询来查看 ASH 是否启用：
SELECT value
FROM v$diag_info
WHERE name = 'ASH enabled';
如果返回的值为 TRUE，则 ASH 已经启用。
3. 生成 ASH 报告
Oracle 提供了多种方法来生成 ASH 报告，其中最常用的是通过 DBMS_SQL_MONITOR.REPORT_SQL_MONITOR 过程。下面是一个简单的示例，展示如何生成一个 ASH 报告：
BEGIN
DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
report_type => 'SQL_MONITOR',
report_name => 'my_ash_report',
sql_id => 'your_sql_id',
output_file => '/tmp/my_ash_report.html',
format_mask => 'HTML');
END;
/
在这个例子中，我们生成了一个 HTML 格式的 ASH 报告，报告文件将保存在 /tmp/my_ash_report.html。
或者
参考引用 MOS How to Generate an ASH Report (Doc ID 1433973.1)
To generate an ASH report:
1. At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
2. Specify whether you want an HTML or a text report:
Enter value for report_type: text
In this example, a text report is chosen.
3. Specify the begin time in minutes before the system date:
Enter value for begin_time: -10
In this example, 10 minutes before the current time is selected.
4. Enter the duration in minutes that the report for which you want to capture ASH information from the begin time.
Enter value for duration:
In this example, the default duration of system date minus begin time is accepted.
5. Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name ashrpt_1_0310_0131.txt
In this example, the default name is accepted and an ASH report named ashrpt_1_0310_0131 is generated. The report will gather ASH information beginning from 10 minutes before the current time and ending at the current time:
-10(start time) + 5 (duration time) = -5 (end time)
i.e. "... and ending 5 minutes before current time."
4. 解读 ASH 报告
ASH 报告包含了丰富的信息，下面是一些重要的部分：
SQL 语句执行概览：
执行次数
平均执行时间
等待时间
CPU 时间
等待事件：
等待事件名称
等待次数
等待时间
等待百分比
执行计划：
访问路径
操作类型
估计行数
实际行数
绑定变量：
变量名
类型
值
性能统计：
物理读取次数
缓冲区获取次数
锁等待次数
5. ASH 数据分析
通过 ASH 数据，可以分析 SQL 语句的性能问题，比如找出执行最频繁的 SQL 语句、等待时间最长的 SQL 语句等。以下是一些常见的分析场景：
查找执行最频繁的 SQL 语句：
SELECT sql_id, executions, elapsed_time
FROM v$sql
WHERE executions > 1000
ORDER BY executions DESC;
查找等待时间最长的 SQL 语句：
SELECT sql_id, elapsed_time, executions
FROM v$sql
WHERE elapsed_time > 100000000
ORDER BY elapsed_time DESC;
分析 SQL 语句的 I/O 效率：
SELECT sql_id, physical_reads, buffer_gets, executions
FROM v$sql
WHERE physical_reads > 1000
ORDER BY physical_reads DESC;
6. 性能优化建议
根据 ASH 数据分析的结果，可以采取以下措施进行性能优化：
索引优化：对于物理读取次数较多的 SQL 语句，考虑添加合适的索引来减少 I/O 操作。
查询优化：对于等待时间较长的 SQL 语句，考虑重构查询逻辑或使用提示来优化执行计划。
资源限制：对于执行过于频繁的 SQL 语句，考虑使用绑定变量或者限制 SQL 语句的执行频率。
7. 结论
通过深入学习和运用 ASH 报告，可以有效地诊断和解决 Oracle 数据库中的性能问题。结合 AWR 和其他工具，可以构建一套完整的性能监控和优化体系。希望本文能够帮助你更好地理解和使用 Oracle 中的 ASH 功能。
