如何进行ORACLE的AWR报告分析

共计 5594 个字符，预计需要花费 14 分钟才能阅读完成。

这期内容当中丸趣 TV 小编将会给大家带来有关如何进行 ORACLE 的 AWR 报告分析，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

1、什么是 AWR？

AWR (Automatic Workload Repository) 是自动负载信息库的英文缩写，AWR 报告是 Oracle 10g 以后版本提供的一种性能收集和分析工具，能提供一个时间段内整个系统资源使用情况的报告，通过报告可以了解一个系统的整个运行情况，生成的报告包括多个部分。

AWR 每小时对 v$active_session_history 视图 (内存中的 ASH 采集信息，理论为 1 小时) 进行采样一次，并将信息保存到磁盘中，并且保留 7 天，7 天后旧的记录才会被覆盖。这些采样信息被保存在 wrh$_active_session_history 视图 (写入 AWR 库中的 ASH 信息，理论为 1 小时以上) 中。而这个采样频率（1 小时）和保留时间（7 天）是可以根据实际情况进行调整的，这就给 DBA 们提供了更加有效的系统监测工具。 

2、什么情况下会用到 AWR？

DBA 对数据库运行状态及状况的监控了解、测试过程中发现数据库出现瓶颈但无法定位到具体原因时，可以借用 AWR 报告进行分析定位。

数据库出现性能问题，一般都在三个地方：IO、内存、CPU，这三个地方又是息息相关的。假设这个三个地方都没有物理上的故障，当 IO 负载增大时，肯定需要更多的内存来存放，同时也需要 CPU 花费更多的时间来过滤这些数据。相反，CPU 时间花费多的话，有可能是解析 SQL 语句，也可能是过滤太多的数据，倒不一定是和 IO 或内存有关系。

CPU：解析 SQL 语句，尝试多个执行计划，最后生成一个数据库认为是比较好的执行计划，但不一定是最优的。因为关联表太多的时候，数据库并不会穷举所有的执行计划，这会消耗太多的时间，oracle 怎么知道这条数据是你要的，另一个就不是你要的呢，这是需要 cpu 来过滤的。

内存：SQL 语句和执行计划都需要在内存保留一段时间，还有取到的数据，根据 LRU 算法也会尽量在内存中保留，在执行 SQL 语句过程中，各种表之间的连接，排序等操作也要占用内存。

IO：如果需要的数据不在内存中，则需要到磁盘中去取，就会涉及到物理 IO 了，还有表之间的连接数据太多，以及排序等操作内存放不下的时候，需要用到临时表空间，也会消耗物理 io 了。

这里说明下，ORACLE 分配的内存中 PGA 一般只占 20%，对于专用服务器模式，每次执行 SQL 语句、表数据的运算等操作，都在 PGA 中进行的，也就是说只能用 ORACL 分配内存的 20% 左右，如果多个用户都执行多表关联，而且表数据又多，再加上关联不当的话，内存就成为瓶颈了，所以优化 SQL 很重要的一点就是，减少逻辑读和物理读。

3、如何生成 awr 报告？

第一步，登录 ORACLE 数据库服务器，记住当前目录或者切换至 AWR 想要保存的目录；

第二步，SQLplus 用户名 / 密码 @服务连接名，连接 oracle 数据库实例，如下图所示；

第三步，执行 @?/rdbms/admin/awrrpt;，会出现提示，

  可以生成以下几种类型 AWR 报告，大部分情况下都是生成本实例的 AWR 报告

 @?/rdbms/admin/awrrpt;   本实例 AWR 包括

 @?/rdbms/admin/awrrpti; RAC 中选择实例号

 @?/rdbms/admin/awrddrpt; AWR  比对报告

 @?/RDBMS/admin/awrgrpt; RAC 全局 AWR 报告

输入生成 AWR 报告的格式是 html 的，如下图所示：

输入开始值与结束值：（输入天数后会列出，snap 值）

输入 AWR 报告的名称：名称自定义 回车后就开始自动生产 AWR 报告，如下图所示：

结束后就可以去指定目录下照 AWR 报告文件，文件为  test_awr.lst，如下图所示：

4、分析 AWR 报告

AWR 报告内容很丰富这里选其中一小部分来讲解，分析 AWR 报告前先了解一下 Oracle 的硬解析和软解析，首先说一下 Oracle 对 SQL 的处理过程。当你发出一条 SQL 语句交付 Oracle，在执行和获取结果前 Oracle 对此 SQL 将进行几个步骤的处理过程：

1、语法检查(syntax check)

检查此 SQL 的拼写是否语法。

2、语义检查(semantic check)

诸如检查 SQL 语句中的访问对象是否存在及该用户是否具备相应的权限。

3、对 SQL 语句进行解析(prase)

利用内部算法对 SQL 进行解析，生成解析树 (parse tree) 及执行计划(execution plan)。

4、执行 SQL，返回结果(execute and return)

其中，软、硬解析就发生在第三个过程里。

Oracle 利用内部的 hash 算法来取得该 SQL 的 hash 值，然后在 library cache 里查找是否存在该 hash 值；

假设存在，则将此 SQL 与 cache 中的进行比较；

假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。

当然，如果上面的 2 个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。

创建解析树、生成执行计划对于 SQL 的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。

打开 AWR 报告头如下图所示：

显示 SGA 中每个区域的大小，可用来与初始参数值比较。

shared pool 主要包括 library cache 和 dictionary cache。library cache 用来存储最近解析（或编译）后 SQL、PL/SQL 和 Java classes 等。library cache 用来存储最近引用的数据字典。发生在 library cache 或 dictionary cache 的 cache miss 代价要比发生在 buffer cache 的代价高得多，因此 shared pool 的设置要确保最近使用的数据都能被 cache。

上图包含了 Oracle 关键指标的内存命中率及其它数据库实例操作的效率，其中 Buffer Hit Ratio 也称 Cache Hit Ratio，Library Hit ratio 也称 Library Cache Hit ratio。同 Load Profile 一节相同，这一节也没有所谓“正确”的值，而只能根据应用的特点判断是否合适。在一个使用直接读执行大型并行查询的 DSS 环境，20% 的 Buffer Hit Ratio 是可以接受的，而这个值对于一个 OLTP 系统是完全不能接受的。根据 Oracle 的经验，对于 OLTPT 系统，Buffer Hit Ratio 理想应该在 90% 以上。

Buffer Nowait 表示在内存获得数据的未等待比例，在缓冲区中获取 Buffer 的未等待比率，Buffer Nowait 的这个值一般需要大于 99%。否则可能存在争用，可以在后面的等待事件中进一步确认。

Buffer Hit 表示进程从内存中找到数据块的比率，监视这个值是否发生重大变化比这个值本身更重要。对于一般的 OLTP 系统，如果此值低于 80%，应该给数据库分配更多的内存。数据块在数据缓冲区中的命中率，通常应在 95% 以上。否则，小于 95%，需要调整重要的参数，小于 90% 可能是要加 db_cache_size。一个高的命中率，不一定代表这个系统的性能是最优的，比如大量的非选择性的索引被频繁访问，就会造成命中率很高的假相（大量的 db file sequential read），但是一个比较低的命中率，一般就会对这个系统的性能产生影响，需要调整。命中率的突变，往往是一个不好的信息。如果命中率突然增大，可以检查 TOP buffer get SQL，查看导致大量逻辑读的语句和索引，如果命中率突然减小，可以检查 TOP physical reads SQL，检查产生大量物理读的语句，主要是那些没有使用索引或者索引被删除的。

Redo NoWait 表示在 LOG 缓冲区获得 BUFFER 的未等待比例。如果太低（可参考 90% 阈值），考虑增加 LOG BUFFER。当 redo buffer 达到 1M 时，就需要写到 redo log 文件，所以一般当 redo buffer 设置超过 1M，不太可能存在等待 buffer 空间分配的情况。当前，一般设置为 2M 的 redo buffer，对于内存总量来说，应该不是一个太大的值。

Library Hit：表示 Oracle 从 Library Cache 中检索到一个解析过的 SQL 或 PL/SQL 语句的比率，当应用程序调用 SQL 或存储过程时，Oracle 检查 Library Cache 确定是否存在解析过的版本，如果存在，Oracle 立即执行语句；如果不存在，Oracle 解析此语句，并在 Library Cache 中为它分配共享 SQL 区。低的 library hit ratio 会导致过多的解析，增加 CPU 消耗，降低性能。如果 library hit ratio 低于 90%，可能需要调大 shared pool 区。STATEMENT 在共享区的命中率，通常应该保持在 95% 以上，否则需要要考虑：加大共享池；使用绑定变量；修改 cursor_sharing 等参数。

Latch Hit：Latch 是一种保护内存结构的锁，可以认为是 SERVER 进程获取访问内存数据结构的许可。要确保 Latch Hit 99%，否则意味着 Shared Pool latch 争用，可能由于未共享的 SQL，或者 Library Cache 太小，可使用绑定变更或调大 Shared Pool 解决。要确保 99%，否则存在严重的性能问题。当该值出现问题的时候，我们可以借助后面的等待时间和 latch 分析来查找解决问题。

Parse CPU to Parse Elapsd：解析实际运行时间 /(解析实际运行时间 + 解析中等待资源时间)，越高越好。计算公式为：Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time elapsed)。即：解析实际运行时间 /(解析实际运行时间 + 解析中等待资源时间)。如果该比率为 100%，意味着 CPU 等待时间为 0，没有任何等待。

Non-Parse CPU：SQL 实际运行时间 /(SQL 实际运行时间 +SQL 解析时间)，太低表示解析消耗时间过多。计算公式为：% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU),2)。如果这个值比较小，表示解析消耗的 CPU 时间过多。与 PARSE_CPU 相比，如果 TOT_CPU 很高，这个比值将接近 100%，这是很好的，说明计算机执行的大部分工作是执行查询的工作，而不是分析查询的工作。

Execute to Parse：是语句执行与分析的比例，如果要 SQL 重用率高，则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。计算公式为：Execute to Parse =100 * (1 – Parses/Executions)。本例中，差不多每 execution 5 次需要一次 parse。所以如果系统 Parses Executions，就可能出现该比率小于 0 的情况。该值 0 通常说明 shared pool 设置或者语句效率存在问题，造成反复解析，reparse 可能较严重, 或者是可能同 snapshot 有关，通常说明数据库性能存在问题。

In-memory Sort：在内存中排序的比率，如果过低说明有大量的排序在临时表空间中进行。考虑调大 PGA(10g)。如果低于 95%，可以通过适当调大初始化参数 PGA_AGGREGATE_TARGET 或者 SORT_AREA_SIZE 来解决，注意这两个参数设置作用的范围时不同的，SORT_AREA_SIZE 是针对每个 session 设置的，PGA_AGGREGATE_TARGET 则时针对所有的 sesion 的。

Soft Parse：软解析的百分比（softs/softs+hards），近似当作 SQL 在共享区的命中率，太低则需要调整应用使用绑定变量。 SQL 在共享区的命中率，小于 95%, 需要考虑绑定，如果低于 80%，那么就可以认为 SQL 基本没有被重用。

TOP5 time Event 是 AWR 报告概要的最后一节，显示了系统中最严重的 5 个等待，按所占等待时间的比例倒序列示。一个性能良好的系统，CPU Time 应该在 TOP 5 的前面，否则说明你的系统大部分时间都用在等待上。当我们调优时，总希望观察到最显著的效果，因此应当从这里入手确定下一步做什么。例如‘buffer busy wait’是较严重的等待事件，应当继续研究报告中 Buffer Wait 和 File/Tablespace IO 区的内容，识别哪些文件导致了问题。如果最严重的等待事件是 I / O 事件，应当研究按物理读排序的 SQL 语句区以识别哪些语句在执行大量 I /O，并研究 Tablespace 和 I / O 区观察较慢响应时间的文件。如果有较高的 LATCH 等待，就需要察看详细的 LATCH 统计识别哪些 LATCH 产生的问题。

在这里，log file parallel write 是相对比较多的等待，占用了 7% 的 CPU 时间。通常借助 AWR 报告寻找耗时比较长或资源使用比较高的 SQL 语句，按各种资源分别列出对资源消耗最严重的 SQL 语句，并显示它们所占统计期内全部资源的比例，这给出我们调优指南。例如在一个系统中，CPU 资源是系统性能瓶颈所在，那么优化 buffer gets 最多的 SQL 语句将获得最大效果。在一个 I / O 等待是最严重事件的系统中，调优的目标应该是 physical IO 最多的 SQL 语句。

这里列出了耗时比较长的 SQL，从高到低排序 TOP100，在 AWR 报告中点击 SQL ID 连接即可跳转到详细的 SQL 语句的地方。

上述就是丸趣 TV 小编为大家分享的如何进行 ORACLE 的 AWR 报告分析了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注丸趣 TV 行业资讯频道。