背景：某个类似准实时的数据分析系统，每15分钟从其他6个数据库中抽取五百张增量数据表，并进行15分钟粒度统计，同时有个前端门户进行查询。

该数据分析系统由数据抽取服务器、应用服务器、数据库服务器组成，全部为虚拟机环境。

问题：当数据抽取定期执行时，应用门户每个页面访问都极其缓慢，10分钟无法响应，甚至无法打开。

初步诊断：厂家一直认为是磁盘问题，甚至准备采用读写分离方式优化。

具体诊断：以数据来说话，以AWR报告为依据，评估和定位问题核心所在。

很久没研究Oracle了，最后正式使用Oracle还是2011年，也想趁此机会，把Oracle复习一下。

AWR是 Oracle 10g 版本推出的新特性，全称叫Automatic Workload Repository-自动负载信息库。

AWR 是通过对比两次快 照(snapshot)收集到的统计信息，来生成报表数据，生成的报表包括多个部分。

AWR报告主要包括如下部分：

Report Summary - 报告摘要

Wait Events Statistics - 等待事件统计

SQL Statistics - SQL统计

Instance Activity Statistics - 实例活动统计

IO Stats - IO统计

Buffer Pool Statistics - 缓冲池统计

Advisory Statistics - 建议统计

Wait Statistics - 等待统计

Undo Statistics - 重做统计

Latch Statistics - 栓锁统计

Segment Statistics - 段统计

Dictionary Cache Statistics - 字典缓存统计

Library Cache Statistics - 库缓存统计信息

Memory Statistics - 内存统计

Streams Statistics - 流统计

Resource Limit Statistics - 资源配额统计

Shared Server Statistics - 共享服务统计

init.ora Parameters - init.ora参数

AWR报告的开始是Workload repository report for (负载信息库报告)

主要包括了

一、DB 名称，DB ID，实例名，实例个数，最近数据库启动时间，数据版本，是否RAC

二、主机名称、操作系统、CPU核数、内存大小

三、AWR报告执行的起止时间，会话数，每个会话的游标数

Elapsed为两次快照的时间间隔

DB Time为花在数据库运算(非后台进程)和等待(非空闲等待)上的时间

DB time = cpu time + all of nonidle wait event time。

数据库负载率=DB time/Cores/Elapsed=1955.68/24/119.70=68.08%，说明数据库还是比较忙的。

关于报告摘要部分，第一节是负载概况

这两部分是数据库资源负载的一个明细列表，分割成每秒钟的资源负载和每个事务的资源负载情况，性能指标的含义如下：

redo size: 每秒/每个事务 产生的redo量 (单位字节)

logical reads: 每秒/每个事务 产生的逻辑读的块数

block changes: 每秒/每个事务 改变的数据块数

physical reads: 每秒/每个事务 产生的物理读

physical writes: 每秒/每个事务 产生的物理写的块数

user calls: 每秒/每个事务 用户的调用次数

parses: 每秒/每个事务 分析次数

hard parses: 每秒/每个事务 硬分析次数

sorts: 每秒/每个事务 排序次数

logons: 每秒/每个事务 登录数据库次数

executes: 每秒/每个事务 SQL的执行次数

rollbacks: 每秒/每个事物回滚次数

transactions: 每秒的事务数

大体可以看出，物理读每秒763M左右，逻辑读每秒2.7G，执行语句12次/秒，业务繁忙程度一般，主要是读数据为主。也就是说磁盘读写不是造成性能问题的根本原因。

Instance Efficiency Percentages为实例命中率

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

Buffer Hit %表示进程从内存中找到数据快的比率，监视这个值是否发生重大变化比这个值本身更重要。根据Oracle的经验，对于OLTP系统，Buffer Hit Ratio理想应该在95%以上。小于90%要增加db_cache_size。命中率很高，不一定代表系统性能最优，比如大量非选择性的索引被频繁访问， 会导致命中率很高的假象(db_file_sequential_read)。命中率突然增大可以检查top buffer get SQL，查看大量逻辑读的语句和索引；命中率突然减小，可以检查top physical_reads SQL，查看大量物理读的语句，主要是那些没有使用索引或索引被删。--编者按，数据库中确实存在大量非选择性的索引，几乎每个表都有。

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等参数。 --编者按，简而言之就是SQL软解析命中率；数据库中也存在不少的写死的SQL语句或拼接的SQL语句。

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

Parse CPU to Parse Elapsd%：SQL总体解析时间包括CPU时间和wait时间，是指sql语句的CPU时间与总体解析时间的比率，解析实际运行时间/(解析实际运行时间+解析中等待资源时间)，越高越好。计算公式为：Parse CPU to Parse Elapsd %= 100*(parse time cpu parse time elapsed)。即：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)。如果该比率为100%，意味着CPU等待时间为0，没有任何等待；这个比率过低说明SQL Parse的wait时间远远大于CPU的 Parse时间不是很正常，可能有大量lib cache latch or shared pool latch。--编者按，本例中这个值比较低0.44%，说明CPU等待情况极为严重，lib cache latch一般是由于SQL未使用绑定变量导致无法共享产生的硬解析，shared pool latch一般是共享池不够大导致的。

通过v$SQL视图查询未绑定变量的SQL语句

select*

from(select plan_hash_value,count(distinct(hash_value)),sum(executions),sum(parse_calls)

from v$sql

group by plan_hash_value

having count(distinct(hash_value))>10

order by2desc)

where rownum<21;

select sql_text from v$sql where plan_hash_value=?and rownum<10;

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

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基本没有被重用。

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

Non-Parse CPU ：SQL实际运行时间/(SQL实际运行时间+SQL解析时间)，太低表示解析消耗时间过多。计算公式为：% Non-Parse CPU

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