【DB笔试面试822】在Oracle中，AWR报告中主要关注哪些方面内容？

题目部分

【DB笔试面试822】在Oracle中，AWR报告中主要关注哪些方面内容？

♣

答案部分

AWR报告中常常需要关注如下的内容：

（一）DB Time/Elapsed

该部分位于AWR报告的头部，如下图所示，需要特别关注DB Time和Elapsed的比值：

Elapsed：60.03(mins)表明采样时间大约是60分钟，任何数据都要通过这个时间来衡量，离开了这个采样时间，任何数据都毫无意义，Elapsed为该AWR性能报告的自然时间跨度，所谓自然时间的跨度，例如前一个快照是4点生成的，后一个快照是6点生成的，如果使用“@?/rdbms/admin/awrrpt”脚本中指定这2个快照的话，那么其Elapsed=(6-4)=2个小时。一个AWR报告至少需要2个AWR快照才能生成（注意在这2个快照之间实例不能重启过，否则指定这2个快照生成AWR报告会报错）。AWR性能报告中的指标往往是后一个快照和前一个快照的指标的delta值，这是因为累计值并不能反映某段时间内的系统负载情况。如果为了诊断特定时段性能问题，那么采用时间不宜过长。如果是看全天负载，那么可以长一些。最常见是60分钟或120分钟。

DB Time：427.44(mins)表明用户操作花费的时间，包括CPU时间和活动的非后台进程的等待时间，也许有人会觉得奇怪，为什么在采样的60分钟过程中，用户操作时间竟然有427分钟呢？远远超过了采样时间，原因是AWR报告是一个数据的集合，例如在一分钟之内，一个用户等待了30秒，那么10个用户就等待了300秒。对于CPU的话，一个CPU处理了30秒，16个CPU就是480秒。这些时间都是以累积的方式记录在AWR报告中的。DB Time不包括Oracle后台进程消耗的时间。一般来说，如果DB Time除以CPU个数大于Elapsed时间，那么说明数据库比较繁忙。

（二）Load Profile

该部分位于AWR报告的总览部分（Report Summary），AWR报告总览部分包括了五个部分：缓存尺寸（Cache Sizes）、负载性能（Load Profile）、数据库效率（Instance Efficiency Percentages）、共享池统计（Shared Pool Statistics）、TOP5事件（Top 5 Timed Events）。这五个部分是整个AWR报告的核心部分，记录了数据库系统的关键性能参数和状况。其中，Load Profile代表负载性能，即系统负载信息，从每秒钟和每个事务两个维度统计的，单纯的数字也无太大意义，需要与Baseline（基线）做比较才有意义。

下表是Load Profile部分的内容：

对Load Profile中的每个指标的解析如下所示：

v Redo size：每秒/每事务产生的日志大小（单位是字节），可标志数据变更频率，数据库任务的繁重与否。

v Logical reads：平均每秒/每事务产生的逻辑读的块数（单位是Block）。Logical Reads= Consistent Gets DB Block Gets。

v Block changes：每秒/每事务修改的块数，即数据库事务改变数据块的数量。

v Physical reads：每秒/每事务物理读（磁盘读）的块数（单位是Block）。

v Physical writes：每秒/每事务物理写的块数。

v User calls：每秒/每事务用户调用次数。

v Parses：SQL每秒/每事务解析的次数，包括Fast Parse、Soft Parse和Hard Parse三种解析的综合。

v Hard parses：每秒/每事务硬解析的次数，硬解析太多，说明SQL重用率不高。每秒产生的硬解析次数超过100次，就可能说明绑定变量使用地不好，也可能是共享池设置不合理。

v Sorts：每秒/每事务的排序次数，对于Sorts大于每秒100，表明排序过多，需要减少SQL代码中排序操作，或调整排序空间。

v Logons：每秒/每事务登录的次数，大于每秒1~2个，表明可能有争用问题。

v Executes：每秒/每事务SQL执行次数，反应负载大小。

v Transactions：每秒事务数，反映数据库任务繁重与否。

v Blocks changed per Read：表示逻辑读用于修改数据块的比例，在每一次逻辑读中更改的块的百分比。

v Recursive Call：递归调用占所有操作的比率。

v Rollback per transaction：每个事务的回滚率。用来观察回滚率是不是很高，因为回滚很占用资源，如果回滚率过高，那么可能说明数据库有太多的无效操作，过多的回滚可能还会带来Undo Block的竞争。

v Rows per Sort：每次排序的行数。

（三）Instance Efficiency Percentages (Target 100%)

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

下表是该部分的一个示例表格：

该部分的各个指标解析如下所示：

v 缓冲区未等待率（Buffer Nowait %）：表示在内存获得数据的未等待比率。Buffer Nowait的这个值一般需要大于99%。否则可能存在争用，可以在后面的等待事件中进一步确认。如果该值较低，那么可能要增大BUFFER CACHE，期望值是100%，不应该低于99%。

v 缓冲区命中率（Buffer Hit %）：表示进程从内存中找到数据块的比率，即数据块在数据缓冲区中的命中率，通常应在95%以上。监视这个值是否发生重大变化比仅仅观察这个值本身更重要。如果小于95%，那么就需要调整重要的参数，如果小于90%，那么就可能需要加DB_CACHE_SIZE。对于一般的OLTP系统，如果此值低于80%，那么应该给数据库分配更多的内存。命中率的突变，往往是一个不好的信息。如果命中率突然增大，那么可以检查top buffer get SQL，查看导致大量逻辑读的语句和索引；如果命中率突然减小，那么可以检查top physical reads SQL，检查产生大量物理读的语句，主要是那些没有使用索引或者索引被删除的SQL语句。

v Redo缓冲区未等待率（Redo Nowait %）：表示在LOG缓冲区（Redo Log Buffer）获得BUFFER的未等待比率，该指标的值应接近100%。如果该值较低，那么有两种可能的情况：1）联机Redo日志文件没有足够的空间；2）LOG切换速度较慢。如果太低（可参考90%阀值），那么考虑增加LOG_BUFFER。

v 库缓存命中率（Library Hit%）：表示Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比率，当应用程序调用SQL或存储过程时，Oracle检查Library Cache确定是否存在解析过的版本，如果存在，那么Oracle立即执行语句；如果不存在，那么Oracle解析此语句，并在Library Cache中为它分配共享SQL区。该值过低说明有过多的解析，CPU消耗增加，性能降低。如果该值低于90%，那么可能需要调大Shared Pool区。

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

v CPU时间占整个解析时间比率（Parse CPU to Parse Elapsd %）：表示在解析SQL语句过程中，CPU占整个的解析时间比例，期望值是100%，说明解析没有产生等待，计算公式为：解析实际运行时间/（解析实际运行时间 解析中等待资源时间），该值越大越好。如果该值为100%，那么意味着CPU等待时间为0，没有任何等待。

v CPU非解析时间百分比（Non-Parse CPU %）：即SQL实际运行时间/(SQL实际运行时间 SQL解析时间)。该值太小表示解析消耗CPU时间过多，该值越大越好，说明计算机执行的大部分工作是执行查询的工作，而不是分析查询的工作。

v 解析与执行的比率（Execute to Parse %）：指的是SQL语句解析与执行的比例，如果SQL重用率高，那么这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。该值越大越好，说明一次解析，到处执行。计算公式为：Execute to Parse=100*(1-PARSES/EXECUTIONS)。如果系统PARSES大于EXECUTIONS，那么就可能出现该比率小于0的情况。若该值小于0，则通常说明Shared Pool设置或者语句效率存在问题，造成反复解析，REPARSE可能较严重。

v 内存排序率（In-memory Sort %）：表示在内存中排序的比率，如果过低，那么说明有大量的排序在临时表空间中进行，此时可以考虑调大PGA。该指标的值应接近100%，如果低于95%，那么可以通过适当调大初始化参数PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE来解决，注意，这两个参数设置作用的范围是不同的，SORT_AREA_SIZE是针对每个SESSION设置的，PGA_AGGREGATE_TARGET则是针对所有的SESSION的。

v 软解析的百分比（Soft Parse %）：表示软解析的百分比，近似当作SQL在共享区的命中率。若该值小于95%，则需要考虑绑定变量，如果低于80%，那么就可以认为SQL基本没有被重用。该指标的值通常应在95%以上，期望值是100%，有一点要说明的是，不要单方面的追求软解析的高比例，而去绑定变量，要看性能的瓶颈在哪里。

（四）Top 5 Timed Events

该部分的一个示例如下所示：

该部分显示了系统中最严重的5个等待事件，按所占等待时间的比例倒序显示。当调优时，该部分是必须要分析的，应当从这里入手确定下一步做什么。例如，“buffer busy waits”是较严重的等待事件，那么应当继续研究报告中Buffer Wait和File/Tablespace I/O区的内容，识别哪些文件导致了问题。如果最严重的等待事件是I/O事件，那么应当研究按物理读排序的SQL语句区以识别哪些语句在执行大量I/O，并研究Tablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待，那么就需要察看详细的LATCH统计识别哪些LATCH产生的问题。

一个性能良好的系统，CPU TIME应该在TOP 5的前面，否则说明系统大部分时间都用在等待上。

（五）SQL Statistics

SQL Statistics分别从执行时间、物理读、逻辑读、子游标个数、执行次数等方面罗列出TOP语句，从该部分可以迅速获取有性能问题的SQL语句，如下所示：

l SQL ordered by Elapsed Time

l SQL ordered by CPU Time

l SQL ordered by Gets

l SQL ordered by Reads

l SQL ordered by Executions

l SQL ordered by Parse Calls

l SQL ordered by Version Count

以“lSQL ordered by Elapsed Time”为例，该部分记录了执行总时间的SQL语句，记录的是监控范围内该SQL的执行时间总和，需要综合分析CPU时间（CPU Time）和执行次数（Executions）才能得到单个SQL的代价。单次执行开销较大的SQL属于重点优化之列。

该部分的一个示例表如下所示：

对于每个指标的解析如下：

v Elapsed Time(s)：SQL语句执行总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL运行的时间，而是监控范围内SQL执行次数的总和时间。单位为秒。Elapsed Time = CPU Time Wait Time。

v CPU Time(s)：SQL语句执行时CPU占用总时长，此时间会小于等于Elapsed Time时间。单位为秒。

v Executions：SQL语句在监控范围内的执行次数总和。若Executions为0，则说明语句没有正常执行完成，被中间停止，需要关注。

v Elapsed Time per Exec (s)：执行一次SQL的平均时间。单位为秒。

v %Total：SQL的Elapsed Time时间占数据库总时间（DB Time）的百分比。

v SQL Id：SQL语句的ID编号，点击之后就能导航到下面的SQL详细列表中，点击浏览器的返回按钮可以回到当前SQL Id的地方。

v SQL Module：显示该SQL是用什么方式连接到数据库的。

v SQL Text：简单的SQL文本。

（六）Segment Statistics

该部分从段（表段、索引段）的角度描述了数据库的繁忙程度，包含了逻辑读、物理读、ITL等方面。若等待事件“enq: TX - row lock contention”发生次数比较多，则可以查看“Segments by Row Lock Waits”部分内容，找到发生行锁的表。若等待事件“enq: TX - allocate ITL entry”发生次数比较多，则可以查看“Segments by ITL Waits”部分内容，找到发生ITL等待的表。若等待事件“Buffer Busy Waits”发生次数比较多，则可以查看“Segments by Buffer Busy Waits”部分内容，找到那些对象访问频繁，从而导致热块的产生。

还有其它的一些需要关注的内容，这里就不详细介绍了。

& 说明：

有关每一种等待事件的解释，可以关注作者微信公众号（参考附录部分）。

有关如何阅读AWR报告可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2121787/

本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗