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

原创

baoqiangwang 2022-04-12 11:27:37 ©著作权

©著作权归作者所有：来自51CTO博客作者baoqiangwang的原创作品，请联系作者获取转载授权，否则将追究法律责任

Wait Event Histogram(等待事件直方图)，顾名思义为各等待事件的按时间区间统计的次数，还包括Wait Event Histogram Detail (64 msec to 2 sec)

Wait Event Histogram Detail (4 sec to 2 min)、Wait Event Histogram Detail (4 min to 1 hr)，值得注意的是，几个直方图的关系并不是加总的关系，前面的是总等待次数，后面的指标是百分比情况。

等待事件直方图主要看总等待次数比较多的和消耗时长比例最高的那些事件即可，本文继续按照数据统计原则进行四个直方图的合并、处理和排序。

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

一个Oracle小白的AWR报告分析（四）_等待事件_02

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

下图为处理和排序后的一些需要关注的等待事件。

一个Oracle小白的AWR报告分析（四）_其它_04

第一个是direct path read

这个等待事件发生在会话将数据块直接读取到PGA当中而不是SGA中的情况，这些被读取的数据通常是这个会话私有的数据，所以不需要放到SGA作为共享数据，因为这样做没有意义。这些数据通常是来自与临时段上的数据，比如一个会话中SQL的排序数据，并行执行过程中间产生的数据，以及Hash Join，merge join产生的排序数据，因为这些数据只对当前的会话的SQL操作有意义，所以不需要放到SGA当中。当发生direct path read等待事件时，意味着磁盘上有大量的临时数据产生，比如排序、并行执行等操作，或者意味着PGA中空闲空间不足。

采取直接路径读的三种方式：

　　①隐含参数：_small_table_threshold ；该参数的默认值为buffer cache 的2%；11gR2之前表的大小 >该参数的5倍就会采取直接路径读的方式；11gR2之后只需要满足该参数定义的大小就会采取直接路径读的方式获取数据；查看隐含参数：SQL> select ksppinm,ksppstvl,ksppdesc from x$ksppi x,x$ksppcv y where x.indx = y.indx AND ksppinm ='_small_table_threshold';

　　②表上的脏块小于表总块数的25%，就会采取直接路径读的方式；

　　③表中的块被cache的比例小于50%的时候，就会采取直接路径读的方式

　　这个等待事件有三个参数：

　　①Descriptor address：一个指针，指向当前会话正在等待的一个direct read I/O。

　　②First dba：descriptor address 中最旧的一个I/O数据块地址。

　　③Block cnt：descriptor address上下文中涉及的有效的buffer 数量。

当索引出现全表扫描时会判断表的大小，如果表过大，则使用直接路径读来获取数据。大量的并发查询该表，并且如果执行计划走的是全表扫描的话，通过直接路径读的方式从存储中获取数据，由于没有SGA的缓存，每一次查询都会产生大量的物理读，最终导致io高；由于处理的速度慢，CPU又产生了大量的等待队列，所以DB time也非常高。

具体可以参考Segments by Logical Reads、Segments by Physical Reads、Segments by Direct Physical Reads，优化相关表的吞吐和索引，减少物理读。

第二个是db file sequential read

db file sequential read 单块读等待是一种最为常见的物理IO等待事件，这里的sequential指的是将数据块读入到相连的内存空间中，而不是指所读取的数据块是连续的。

最为常见的是执行计划中包含了INDEX FULL SCAN/UNIQUE SCAN。

db file sequential read 主要的问题不是对index的访问,而且超额的对错误index的访问.当系统的访问路径发生更改时,可能对效能慢的index进行访问,从而产生等待.当然如果一个SQL执行了大量的index读这也可能是一个性能问题.所以分析SQL的执行计划是一个比较好的方法,当要用FULL TABLE SCAN时,用index就会产生性能问题.还有就是FIRST_ROWS 和ALL_ROWS的问题,当然从大的方面讲OLTP与DSS的混用也会产生不合时适的db file sequential read.还有关于驱动表(driving table)的问题.不对的驱动表,性能也不会好.记住,所有的努力的目的应该是一样的,那就是降低logical and physical I/Os

下面有个种方法:

1)分析SQL,弄清SQL的逻辑,看看SQL到底想获取什么,然后优化,甚至重写

2)将index放在快磁盘上,尤其不要放在RAID-5上,因为慢磁盘导致高average time,然而I/O优化的优先级不可以高于SQL CODE的优化.因为SQL有问题再快的磁盘的也不行,最好用OUTLINE稳固执计计划,尤其是第三方软件

3)关于index表,最好将数据进行排列,以减少I/O.可以通过DBA_INDEXS.CLUSTERING_FACTOR来查看index有没有达到表的所有块的数量,如有是,说明大部份列是排列的,如是不是,表时表是随机排列的.这时可以通过重组表以解决问题.

4)看看表最近没有没建立新的index,使SQL的执行计划发生改变.

定位db file sequential read问题的对象

SELECT b.sid,
nvl(substr(a.object_name,1,30),
'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
a.subobject_name,
a.object_type
FROM dba_objects a, v$session_wait b, x$bh c
WHERE c.obj = a.object_id(+)
AND b.p1 = c.file#(+)
AND b.p2 = c.dbablk(+)
AND b.event = 'db file sequential read'
UNION
SELECT b.sid,
nvl(substr(a.object_name,1,30),
'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
a.subobject_name,
a.object_type
FROM dba_objects a, v$session_wait b, x$bh c
WHERE c.obj = a.data_object_id(+)
AND b.p1 = c.file#(+)
AND b.p2 = c.dbablk(+)
AND b.event = 'db file sequential read'
ORDER by 1;

第三个是read by other session

当从数据库请求信息时，Oracle首先将数据从磁盘读到数据库缓冲缓存中。如果两个或多个会话请求相同的信息，第一个会话将在其他会话等待时将数据读入缓冲缓存。在以前的版本中，此等待被归类为“buffer busy waits”事件。但是，在oracle10.1及更高版本中，这个等待时间现在被分解为“read by other session” wait event。对该事件的过度等待通常是由于多个进程重复读取相同的块，例如许多会话扫描同一索引或对同一表执行全表扫描。调整此问题是找到并消除此争用的关键。

定位和查找问题：

1、当前正在发生的问题：
select sql_fulltext from v$sql a,v$session b where a.sql_id=b.sql_id and b.event='read by other session';
2、历史曾经发生的
select a.sql_id,sql_fulltext from v$sql a,dba_hist_active_sess_history b where a.sql_id=b.sql_id and b.event='read by other session';
往往read by other session伴随着db file sequential read事件的出现。
另外可以查看涉及对象信息，此处就是p1,p2,p3
SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait WHERE event = 'read by other session';
通过p1,p2,p3获得热点对象：
SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents
WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks - 1;
另外，也可以直接查看热点块的信息，如查看热点块导致的sql语句：
select sql_text
from v$sqltext a,
(select distinct a.owner, a.segment_name, a.segment_type
from dba_extents a,
(select dbarfil, dbablk
from (select dbarfil, dbablk from x$bh order by tch desc)
where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk
and a.block_id + a.blocks > b.dbablk) b
where a.sql_text like '%' || b.segment_name || '%'
and b.segment_type = 'TABLE'
order by a.hash_value, a.address, a.piece;
查看热点块对象：
SELECT E.OWNER, E.SEGMENT_NAME, E.SEGMENT_TYPE
FROM DBA_EXTENTS E,
(SELECT *
FROM (SELECT ADDR, TS#, FILE#, DBARFIL, DBABLK, TCH
FROM X$BH
ORDER BY TCH DESC)
WHERE ROWNUM < 11) B
WHERE E.RELATIVE_FNO = B.DBARFIL
AND E.BLOCK_ID <= B.DBABLK
AND E.BLOCK_ID + E.BLOCKS > B.DBABLK;

第四个是db file scattered read

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

定位和查找问题：

1、产生原因
该等待事件通常发生在数据库多块读时，表示发生了与全表扫描和快速索引扫描相关的等待。通常意味着全表扫描过多，或者I/O 能力不足，或者I/O 竞争。
2、确定产生问题对象方法
a)查找全表扫描的SQL 语句可以使用以下语句：
select sql_text
from v$sqltext t, v$sql_plan p
where t.hash_value = p.hash_value
and p.operation = 'TABLE ACCESS'
and p.options = 'FULL'
order by p.hash_value, t.piece;
b)查找Fast Full Index 扫描的SQL 语句可以使用以下语句：
select sql_text
from v$sqltext t, v$sql_plan p
where t.hash_value = p.hash_value
and p.operation = 'INDEX'
and p.options = 'FULL SCAN'
order by p.hash_value, t.piece;
3、解决办法
（1）在合适的字段上建立索引把表的访问方式从全表扫描变为索引扫描可以有效地降低物理IO。
（2）对于大表，在合适的字段，比如年月、地区编码上建立分区把全表扫描变成分区扫描以减少物理IO。
（3）把需要经常扫描的数据库表放在KEEP 池同样会有效地降低物理IO。
（4）调整db_file_multiblock_read_count值，以达到每次读取更多数据块的目的。

第五个为log file parallel write,log file sync,db file async I/O submit基本为一类，log file parallel write,log file sync只能通过改善IO性能来提升了，关于db file async I/O submit，该等待跟oracle的异步io有关。

与oracle异步io相关的参数有2个：

SQL> show parameter filesystem

SQL> show parameter disk_asynch

disk_asynch_io 这个参数默认是true，但是filesystemio_options默认是none。这种情况会出现db file async I/O submit等待事件。

如果不想这个等待事件出现，可以采取两种方法：

1：alter system set disk_asynch_io=false scope=spfile; 关闭异步io

2：alter system set filesystemio_options=asynch scope=spfile;

第六个为Library cache lock，在《一个Oracle小白的AWR报告分析（三）》已有介绍，次数很少，但等待时间却很长，library cache lock是在访问或修改库高速缓冲期的对象时，对库高速缓冲区具柄获得的锁，在获取library cache lock的过程中，如果发生争用，则等待library cache lock事件。可能发生library cache pin和library cache lock的情况：

1、在存储过程或者函数正在运行时被编译。

2、在存储过程或者函数正在运行时被对它们进行授权、或者移除权限等操作。

3、对某个表执行DDL期间，有另外的会话对该表执行DML或者DDL。

4、PL/SQL对象之间存在复杂的依赖性

5、长时间执行不出来的SQL

6、OS僵死进程

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

关于Service Statistics和Service Wait Class Stats，这两个统计可查阅的信息不多，智能通过Sys$users看到大致的数据库CPU消耗和各服务类型等待时长。典型的如Oracle官方文档所示：

In addition to services to be used by applications, Oracle Database also supports two internal services: SYS$BACKGROUND is used by the background processes only and SYS$USERS is the default service for user sessions that are not associated with services.