一个Oracle小白的AWR报告分析（二）

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

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

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

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

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

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

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

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

前文分析了Workload repository report for (负载信息库报告)、Report Summary(报告摘要)中的load profile和Instance Efficiency Percentages (Target 100%)，接下来继续分析Top 10 Foreground Events by Total Wait Time、Wait Classes by Total Wait Time和Host CPU等等

关于Top 10 Foregrounds Events by Total Wait Time-按总等待时间列出的前十大事件

排序整理过以后的十大事件，红色部分为重点关注内容。

个人感觉值得关注的包括direct path read,db file sequential read,db file scattered read,library cache lock和log file sync。

direct path read 顾名思义直接路径读取，也就是全表扫描，等待最多，总时间也最多。

造成direct path read 的主要原因主要包括：

1、大量的磁盘排序操作，无法在排序区中完成排序，需要利用temp表空间进行排序.

2、大量的Hash Join操作，利用temp表空间保存hash区。

3、SQL语句的并行处理

4、大表的全表扫描，11g认为大表全表时使用直接路径读

db file sequential read的意思是，物理读发生在一个用户需要的数据块不在SGA，从而将其从磁盘读取到SGA中，其实和direct path read有点像。

db file sequential read一般发生在以下情况:

1、索引扫描

2、表扫描(access by rowid)

3、全表扫描(很少发生，例如刚好在extent边缘恰巧被分割成单块，或者已经在buffer cache中）

1、查看当前正在等待的会话
 
我们可以查看v$session_wait 视图的TIME_WAITED栏位来定位当前哪个会话等待 sequential read过长时间(实时)
 
select p1,p2,p3,a.* from v$session_Wait a where event = 'db file sequential read'
 
P1代表File ID，可通过dba_data_File视图的FILE_ID字段看出是哪个数据文件
 
P2代表 First block，即该块在数据文件上开始的位置
 
P3代表块数，由于sequential read为单块读，则该值始终为1
 
2、根据P1 P2参数得出对象的名称和类型
 
select segment_name,segment_type from dba_extents
 
where file_id = p1 and p2 between (block_id and block_id + blocks - 1);
 
3、查看从实例启动以来等待的会话
 
使用 v$session_event视图来定位哪个会话等待 sequential read过长时间(非实时)
 
也可使用v$system_event视图查看系统整体的等待事件
 
SELECT sid, total_waits, time_waited FROM v$session_event
 
 WHERE event='db file sequential read' and total_waits>0
 
 ORDER BY 3 desc ,2
 
4、查看高物理读的数据文件（Tablespace IO Stats和File IO Stats）和查看高物理读的SQL语句（SQL ordered by Reads

db file scattered read等待事件发生在当一个会话在等待一个多数据块的IO请求完成。其典型的发生在当有全表扫描和索引快速扫描INDEX FAST FULL SCAN时。Oracle一次性读取DB_FILE_MULTIBLOCK_READ_COUNT对应的连续数据块，并将它们分散到buffer cache中的buffer中。换句话说从IO读取上和物理存储的顺序上这些数据块应当是连续的，一个块排在一个块后面，但从buffer cache角度看这些数据块对应的buffer并不连续在一起。

一般发生在以下情况，具体检查方法同上:

1、全表扫描( full table scans )

2、索引快速全扫描( index fast full scans)

library cache lock，只有4个等待，但平均等待时间却很长，平均为800多秒。造成library cachelock的主要原因：

1、alter table或者alter package/procedure会以X模式持有library cache lock，造成阻塞。

2、错误的用户名密码频繁登录

3、热表统计信息的收集

4、错误的语句解析（failed parse）

5、high version count，大量硬解析

关于library cache lock检测的办法如下

--查看锁对象和锁模式，找到session_id
select b.owner,b.object_name,a.session_id,a.locked_mode
  from v$locked_object a,dba_objects b
 where b.object_id=a.object_id;

---查看表锁关联pid，获取sid和serial信息
select 'alter system kill session   '||''''||sess.sid ||',' || sess.serial#  ||''';'，
       lo.oracle_username,ao.object_name,lo.locked_mode, 
      'kill -9 ' || pro.spid  ||';' 
  from v$locked_object lo, dba_objects ao, v$session sess,v$process pro
 where ao.object_id = lo.object_id
   and lo.session_id =  sess.sid
   and sess.paddr = pro.addr;

--查看具体会话信息
select sql_id,sid,serial#,username,osuser,program,machine
  from v$session
 where sid='1901';

--查询具体的SQL信息
select sql_text
  from v$sql
 where sql_id='gsmd1w6vybas2';

log file sync的平均等待时间也比较多,平均等待事件时间超过1ms，如果等待时间过长，说明log write每次写入的时间过长，如果能够优化redo日志文件存储，使之存放在更快的磁盘上，就可以减少这个等待事件的单次等待时间；也可以减少事务提交次数。

接下来是Wait Classes by Total Wait Time,按等待时长排序的等待事件分类。

排序整理过以后的十大事件类别，红色部分为重点关注内容。

其中User I/O在等待次数上占比94%，等待时间为76%，平均每次等待为12毫秒，DB CPU等待时间为9.88%；网络等待次数虽多，但等待时间可以忽略不计。

通过SELECT WAIT_CLASS,count(*) FROM V$EVENT_NAME A group by WAIT_CLASS可以找到各类等待事件类型，一共13类。

User I/O等待事件主要包括db file sequential read（数据文件顺序读）、db file scattered read（数据文件离散读）、db file parallel read、db file single write、direct path read（直接路径读、DPR）、串行全表扫描(Serial Table Scan)、direct path write（直接路径写、DRW）、direct path read temp、direct path write temp。

绝大多数都是全表扫描、低效索引导致，具体就不列举查找办法了，有机会再学习一下各种读写和索引的关系。

接下来看一下Host CPU和Instance CPU Host CPU即OS级的CPU OS级的%User为6.4，%Sys为1.2，%Idle为92.3，所以%Busy应该是100%-96.1%=7.7%，所以系统级的CPU使用率不高。

系统级CPU的使用率在7.7%，在7.7%的基础上，实例级别的CPU消耗为7.6%，7.6/7.7=98.7% 和Instance CPU 报告中%Busy CPU是基本吻合的。

关于IO Profile部分，Database(blocks)和Total(MB)是等值的，Database（DB）和Direct(blocks)是等值的，能够直观看到数据库的IO吞吐，以及读写IO比例。

Memory Statistics中的几个指标基本上和Ora.init文件中配置差不多，反映出AWR前后的对比。

Cache Sizes中的指标也同上。

Shared Pool Statistics的几个指标倒是值得关注。

Memory Usage %：对于一个已经运行一段时间的数据库来说，共享池内存使用率，应该稳定在75%-90%间，如果太小，说明SharedPool有浪费，而如果高于90，说明共享池中有争用，内存不足。这个数字应该长时间稳定在75%～90%。如果这个百分比太低，表明共享池设置过大，带来额外的管理上的负担，从而在某些条件下会导致性能的下降。如果这个百分率太高，会使共享池外部的组件老化，如果SQL语句被再次执行，这将使得SQL语句被硬解析。在一个大小合适的系统中，共享池的使用率将处于75%到略低于90%的范围内。

SQL with executions>1：执行次数大于1的sql比率，如果此值太小，说明需要在应用中更多使用绑定变量，避免过多SQL解析。在一个趋向于循环运行的系统中，必须认真考虑这个数字。在这个循环系统中，在一天中相对于另一部分时间的部分时间里执行了一组不同的SQL语句。在共享池中，在观察期间将有一组未被执行过的SQL语句，这仅仅是因为要执行它们的语句在观察期间没有运行。只有系统连续运行相同的SQL语句组，这个数字才会接近100%。--编者按，% SQL with executions>1的比例为93%左右，在一个持续运行的系统中，说明硬解析还是多了一些。

Memory for SQL w/exec>1：执行次数大于1的SQL消耗内存的占比。这是与不频繁使用的SQL语句相比，频繁使用的SQL语句消耗内存多少的一个度量。这个数字将在总体上与% SQL withexecutions>1非常接近，除非有某些查询任务消耗的内存没有规律。在稳定状态下，总体上会看见随着时间的推移大约有75%～85%的共享池被使用。如果Statspack报表的时间窗口足够大到覆盖所有的周期，执行次数大于一次的SQL语句的百分率应该接近于100%。这是一个受观察之间持续时间影响的统计数字。可以期望它随观察之间的时间长度增大而增大。