[ORACLE]，SQL性能报告(AWR)导出，扶你走上调优大神之路

1.简介

自动工作负载信息库 (Automatic Workload Repository)即 AWR 实质上是一个 Oracle 的内置工具。采集与性能相关的统计数据，并从这些统计数据中导出性能量度，以跟踪潜在的问题。

AWR 使用几个表来存储采集的统计数据，所有的表都存储在新的名称为 SYSAUX 的特定表空间中的 SYS 模式下，并且以 WRM$_* 和 WRH$_* 的格式命名。前一种类型存储元数据信息（如检查的数据库和采集的快照），后一种类型保存实际采集的统计数据。 H 代表“历史数据 (historical)”而 M 代表“元数据 (metadata)”。在这些表上构建了几种带前缀BA_HIST_ 的视图，这些视图可以用来编写您自己的性能诊断工具。视图的名称直接与表相关；例如，视图 DBA_HIST_SYSMETRIC_SUMMARY 是在 WRH$_SYSMETRIC_SUMMARY 表上构建的。

1.1.STATISTICS_LEVEL

在默认情况下， Oracle 启用数据库统计收集这项功能（即启用 AWR）。是否启用 AWR 由初始化参数 STATISTICS_LEVEL 控制。

STATISTICS_LEVEL 参数有三个值：

如果 STATISTICS_LEVEL 的值为 TYPICAL 或者 ALL，表示启用 AWR。

如果 STATISTICS_LEVEL 的值为 BASIC，表示禁用 AWR。

可以通过 SHOW PARAMETER STATISTICS_LEVEL 查看当前数据库配置

1.2.快照

快照由后台进程 MMON 自动地每小时采集一次。为了节省空间，采集的数据在 8 天后自动清除。快照频率和保留时间都可以由用户修改。

当前数据库的快照的详细信息可以查看表 SYS.WRH$_ACTIVE_SESSION_HISTORY，或者视图DBA_HIST_SNAPSHOT

2.使用方法

2.1.调用

用 sys 用户登录数据库之后调用脚本：

执行该脚本之后，会依次出现下列参数供用户设置

1. report_type ： 报告的文件类型为 txt 或者 html

2. num_days： 报告涉及的天数，设置之后会自动显示这几天内的所有 snapshot

3. begin_snap：报告的起始快照

4. end_snap：报告的终了快照

5. report_name：报告的名称，设置该参数是可以加上文件路径，方便查找。例如：‘D:\awr_report.html’

实际操作情况如下：

也可以通过程序包 DBMS_WORKLOAD_REPOSITORY 生成 AWR 的 html 报告

结果如下：

2.2.异常处理

如果在导出 awr 时包以下错误：

ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小

ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 919

ORA-06512: 在 line 1

处理办法：

UPDATE WRH$_SQLTEXT SET SQL_TEXT = SUBSTR(SQL_TEXT, 1, 1000);

COMMIT;

重新执行脚本即可。

3. AWR 操作

当前的 AWR 保存策略存放在视图 DBA_HST_WR_CONTROL 中

通常查询结果如下：

1. SNAP_INTERVAL： 表示收集 SNAPSHOT 的频率

2. RETENTION： 记录 SNAPSHOT 保存的时间

以上结果表示,每小时产生一个 SNAPSHOT，保留 8 天。

3.1.配置调整

对 AWR 的配置通过 DBMS_WORKLOAD_REPOSITORY 包实现

修改收集快照的时间间隔和保留天数

将收集间隔时间改为 30 分钟一次。并且保留 5 天时间（单位都是分钟）：

关闭 AWR

把 interval 设为 0 则关闭自动捕捉快照 ：

创建快照：

删除指定范围内的快照：

创建 baseline，保存这些数据用于将来分析和比较：

删除 baseline：

3.2.数据导出

通过程序包 DBMS_SWRF_INTERNAL 实现对 AWR 的导入导出

可以在对应的 MPDIR 中找到对应的 dmp 文件，以及导出的日志文件

也可以通过脚本实现 AWR 数据的导出

执行该脚本之后，会依次出现下列参数供用户设置

1. dbid： 数据库的 DBID

2. num_days：涉及的天数

3. begin_snap：起始快照

4. end_snap：终了快照

5. directory_name：保存 dmp 文件的 directory 名称

6. file_name：新生成的 dmp 文件名

实际操作情况如下：

3.3.数据导入

用程序包 DBMS_SWRF_INTERNAL 导入 AWR 数据的过程分为两个步骤， 首先使用

AWR_LOAD 方法把数据导入到一个临时的 schema 中（ 本例是 AWR_TEMP，该用户必须实际

存在），然后使用 MOVE_TO_AWR 方法把数据导入到 sys 中。

迁移 AWR 数据到临时数据库：

把 AWR 数据转移到 SYS 模式中：

也可以通过脚本实现 AWR 数据的导入

执行该脚本之后，会依次出现下列参数供用户设置

1. directory_name： 保存 dmp 文件的 directory 名称

2. file_name： 新生成的 dmp 文件名

3. schema_name： 导入 awr 时用到的临时 schema，该 schema 会在导入完成后背自动删

除，建议使用 oracle 提供的默认用户 awr_stage，这样 oracle 会创建该用户并在导入完

成后删除。

4. default_tablespace：临时 schema 使用的表空间，可以使用默认值

5. temporary_tablespace：临时 schema 使用的临时表空间，可以使用默认值

实际操作情况如下：

3.4.删除报告

可以将从其他服务器上导入的 AWR 报告删除，但是不能删除本地数据库的 AWR。

如下图当前数据库的 dbid 为 1341466941，导入了 1308840241 的 AWR 报告，可以通过UNREGISTER_DATABASE方法删除 1308840241的 AWR报告，但是无法删除 1341466941的 AWR报告。

查询当前数据库上所有的 DBID 可通过视图 DBA_HIST_DATABASE_INSTANCE

4.报告分析

3.1 SQL ordered by Elapsed Time

记录了执行总和时间的 TOP SQL(请注意是监控范围内该 SQL 的执行时间总和，而不是单次

SQL 执行时间 Elapsed Time = CPU Time + Wait Time)。

1. Elapsed Time(S): SQL 语句执行用总时长，此排序就是按照这个字段进行的。注意该时

间不是单个 SQL 跑的时间，而是监控范围内 SQL 执行次数的总和时间。单位时间为

秒。 Elapsed Time = CPU Time + Wait Time

2. CPU Time(s): 为 SQL语句执行时 CPU占用时间总时长，此时间会小于等于 Elapsed Time

时间。单位时间为秒。

3. Executions: SQL 语句在监控范围内的执行次数总计。

4. Elap per Exec(s): 执行一次 SQL 的平均时间。单位时间为秒。

5. % Total DB Time: 为 SQL 的 Elapsed Time 时间占数据库总时间的百分比。

6. SQL ID: SQL 语句的 ID 编号，点击之后就能导航到下边的 SQL 详细列表中，点击 IE 的

返回可以回到当前 SQL ID 的地方。

7. SQL Module: 显示该 SQL 是用什么方式连接到数据库执行的，如果是用 SQL*Plus 或者

PL/SQL 链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的

sql 该位置为空。

8. SQL Text: 简单的 sql 提示，详细的需要点击 SQL ID。

3.2 SQL ordered by CPU Time:

记录了执行占 CPU时间总和时间最长的 TOP SQL(请注意是监控范围内该 SQL的执行占 CPU

时间总和，而不是单次 SQL 执行时间)。

3.3 SQL ordered by Gets:

记录了执行占总 buffer gets(逻辑 IO)的 TOP SQL(请注意是监控范围内该 SQL 的执行占 Gets

总和，而不是单次 SQL 执行所占的 Gets)。

3.4 SQL ordered by Reads:

记录了执行占总磁盘物理读(物理 IO)的 TOP SQL(请注意是监控范围内该 SQL 的执行占磁盘

物理读总和，而不是单次 SQL 执行所占的磁盘物理读)。

3.5 SQL ordered by Executions:

记录了按照 SQL 的执行次数排序的 TOP SQL。该排序可以看出监控范围内的 SQL 执行次数。

3.6 SQL ordered by Parse Calls:

记录了 SQL 的软解析次数的 TOP SQL。说到软解析(soft prase)和硬解析(hard prase)

3.7 SQL ordered by Sharable Memory:

记录了 SQL 占用 library cache 的大小的 TOP SQL。 Sharable Mem (b)：占用 library cache 的

大小，单位是 byte。

3.8 SQL ordered by Version Count:

记录了 SQL 的打开子游标的 TOP SQL。

3.9 SQL ordered by Cluster Wait Time:

记录了集群的等待时间的 TOP SQL

（Zyx）