【DB笔试面试822】在Oracle中，AWR报告中主要关注哪些方面内容？
【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部分的内容：
Per Second | Per Transaction | ||||
|---|---|---|---|---|---|
Redo size: | 918,805.72 | 775,912.72 | |||
Logical reads: | 3,521.77 | 2,974.06 | |||
Block changes: | 1,817.95 | 1,535.22 | |||
Physical reads: | 68.26 | 57.64 | |||
Physical writes: | 362.59 | 306.20 | |||
User calls: | 326.69 | 275.88 | |||
Parses: | 38.66 | 32.65 | |||
Hard parses: | 0.03 | 0.03 | |||
Sorts: | 0.61 | 0.51 | |||
Logons: | 0.01 | 0.01 | |||
Executes: | 354.34 | 299.23 | |||
Transactions: | 1.18 | ||||
% Blocks changed per Read: | 51.62 | Recursive Call %: | 51.72 | ||
Rollback per transaction %: | 85.49 | Rows per Sort: | 16.18 | ||
对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%以上。
下表是该部分的一个示例表格：
Buffer Nowait %: | 100.00 | Redo NoWait %: | 100.00 |
|---|---|---|---|
Buffer Hit %: | 98.72 | In-memory Sort %: | 99.86 |
Library Hit %: | 99.97 | Soft Parse %: | 99.92 |
Execute to Parse %: | 89.09 | Latch Hit %: | 99.99 |
Parse CPU to Parse Elapsd %: | 7.99 | % Non-Parse CPU: | 99.95 |
该部分的各个指标解析如下所示：
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
该部分的一个示例如下所示：
Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|---|---|---|---|---|
CPU time | 515 | 77.6 | |||
SQL*Net more data from client | 27,319 | 64 | 2 | 9.7 | Network |
log file parallel write | 5,497 | 47 | 9 | 7.1 | System I/O |
db file sequential read | 7,900 | 35 | 4 | 5.3 | User I/O |
db file parallel write | 4,806 | 34 | 7 | 5.1 | System I/O |
该部分显示了系统中最严重的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属于重点优化之列。
该部分的一个示例表如下所示：
Elapsed Time (s) | Executions | Elapsed Time per Exec (s) | %Total | %CPU | %IO | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
12,418,953.35 | 2,376,222 | 5.23 | 99.49 | 0.03 | 0.00 | 1cmnjddakrqbv | JDBC Thin Client | update orgtion o set o.qu... |
3,791.90 | 2,129,113 | 0.00 | 0.03 | 24.33 | 2.73 | 26ad9zvt5xgb3 | JDBC Thin Client | insert into tran_success... |
2,882.78 | 3,267,011 | 0.00 | 0.02 | 16.76 | 13.88 | an08dyryjns25 | JDBC Thin Client | select t.trans_id, t.id_type, ... |
1,100.69 | 2,129,218 | 0.00 | 0.01 | 18.21 | 0.01 | g8mxscbjf4t8f | JDBC Thin Client | select count(0) from tra_boo... |
861.17 | 541,558 | 0.00 | 0.01 | 22.34 | 2.40 | 5ww8x9u15a90y | JDBC Thin Client | insert into transuccess... |
675.32 | 19,773,101 | 0.00 | 0.01 | 55.62 | 0.00 | dzmtc8dyfsv0v | JDBC Thin Client | select sysdate from dual |
对于每个指标的解析如下：
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程序员面试笔试宝典》，作者：小麦苗