全面解析Oracle等待事件的分类、发现及优化

http://click.aliyun.com/m/21917/
摘要： 作者介绍 韩锋，宜信技术研发中心数据库架构师。精通多种关系型数据库，曾任职于当当网、TOM在线等公司，曾任多家公司首席DBA、数据库架构师等职，多年一线数据库架构、设计、开发经验。著有《SQL优化最佳实践》一书。

作者介绍
韩锋，宜信技术研发中心数据库架构师。精通多种关系型数据库，曾任职于当当网、TOM在线等公司，曾任多家公司首席DBA、数据库架构师等职，多年一线数据库架构、设计、开发经验。著有《SQL优化最佳实践》一书。



  一、等待事件由来


大家可能有些奇怪，为什么说等待事件，先谈到了指标体系。其实，正是因为指标体系的发展，才导致等待事件的引入。总结一下，Oracle的指标体系，大致经历了下面三个阶段：



以命中率为主要参考指标



以各种命中率为主要的优化入口依据，常见的有”library cache hit radio“等。但这种方式弊端很大，一个命中率为99%的系统，不一定就比95%的系统优化的更好。在老的Oracle版本中，往往采用这种方式，如8i、9i等。



以等待事件为主要参考指标



以各种等待事件为优化入口依据，常见的有"db file sequential read"等。可以较直观的了解，在一段时间内，数据库主要经历了那些等待。这些"瓶颈"，往往就是我们优化的着手点。在10g、11g版本中，广泛使用。



以时间模型为主要参考指标



以各种资源整体消耗为优化入口依据。可以从整体角度了解数据库在一段时间内的消耗情况。较等待事件的方式，更有概括性。常见的如"DB Time"。Oracle在不断加强这个方面的工作。



从上面三个阶段可见，等待事件的引入，正是为了解决以命中率为指标的诸多弊端。与后面的时间模型相比，等待事件以更加直观、细粒度的方式观察Oracle的行为，往往作为优化的重要入口。而时间模型，更侧重于整体、系统性的了解数据库运行状态。两者的侧重点不同。



  二、等待事件分类


让我们首先从等待事件的分类入手，认识等待事件。从大的分类上来看，等待事件可分为空闲的、非空闲的两大部分。在非空闲的等待事件，又可进一步划分细的类别。



可以通过下面的方法，观察系统包含的等待事件数量及大致分类（以下语句在11g环境运行）。



20161101103051868.jpg

20161101103102757.jpg



其中WAIT_CLASS为“Idle”的等待事件就是空闲的，其他的都是非空闲的等待事件。

1）区分 — 空闲与非空闲等待事件
空闲等待事件，是指Oracle正等待某种工作，比如用sqlplus登录之后，但没有进一步发出任何命令，此时该session就处于SQL*Net message from/to client等待事件状态，等待用户发出命令，任何的在诊断和优化数据库的时候，一般不用过多注意这部分事件。



非空闲等待事件，专门针对Oracle的活动，指数据库任务或应用运行过程中发生的等待，这些等待事件是调整数据库的时候应该关注与研究的。



2）等待事件分类说明
管理类-Administrative

此类等待事件是由于DBA的管理命令引起的，这些命令要求用户处于等待状态（比如，重建索引） 。



应用程序类-Application

此类等待事件是由于用户应用程序的代码引起的（比如，锁等待） 。



群集类-Cluster

此类等待事件和真正应用群集RAC的资源有关（比如，gc cr block busy等待事件） 。



提交确认类-Commit

此类等待事件只包含一种等待事件——在执行了一个commit命令后，等待一个重做日志写确认（也就是log file sync） 。



并发类-Concurrency

此类等待事件是由内部数据库资源引起的（比如闩锁） 。



配置类-Configuration

此类等待事件是由数据库或实例的不当配置造成的（比如，重做日志文件尺寸太小，共享池的大小等） 。



空闲类-Idle

此类等待事件意味着会话不活跃，等待工作（比如，sql * net messages from client） 。



网络类-Network

和网络环境相关的一些等待事件（比如sql* net more data to dblink） 。



其它类-Other

此类等待事件通常比较少见（比如wait for EMON to spawn） 。



调度类-Scheduler

此类等待事件和资源管理相关（比如resmgr: become active'） 。



系统I/O类-System I/O

此类等待事件通过是由后台进程的I/O操作引起的（比如DBWR等待-db file paralle write） 。



用户I/O类-User I/O

此类等待事件通常是由用户I/O操作引起的（比如db file sequential read） 。





  三、理解等待事件


每一个等待事件，都表明数据库的一种活动状态。从上面的查询可见，系统内置了很多等待事件，可以通过数据字典V$EVENT_NAME去了解每个等待事件。下面通过一个最为常见的等待事件进行说明。



20161101103128488.jpg

20161101103142928.jpg



这个等待事件“db file sequential read”,直译过来为“数据文件顺序读取”，是属于“User I/O”类的等待事件。它通常是与单个数据块相关的读取操作，大多数情况下读取一个索引块或者通过索引读取一个数据块，会记录这个等待。该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕，或者使用了非选择性索引。通过将这种等待与statspack报表中已知其它问题联系起来（如效率不高的sql），通过检查确保索引扫描是必须的，并确保多表连接的连接顺序来调整，DB_CACHE_SIZE可以决定该事件出现的频率。



该等待事件包含了三个参数，分别为：



file#: 代表oracle要读取的文件的绝对文件号

block#: 从这个文件中开始读取的起始数据块块号

blocks: 读取的block数量。通常是1，表示单个block读取。



通过上面这些参数，关联数据字典可以确定发生等待事件的对象（即找到了热点对象）。然后针对不同的情况，有针对性的进行解决。



对等待事件的了解越多，可更加深入理解数据库运行机制，进而提高整体优化能力。后面，我会介绍一下常见的等待事件。



  四、观察等待事件


系统内置了一些视图，通过这些视图可以了解整体（系统级）、局部（会话级）的等待事件发生情况及各类别事件的分类统计。下面针对一些主要的视图，说明一下。



1、v$event_name



系统支持的等待事件，可以查看等待事件所属类别、参数的含义等信息。



2、v$system_wait_class



displays the instance-wide time totals for each registered wait class.



等待事件类别的统计信息（系统级）。通过这一视图，可从全局角度了解系统那类操作等待较多。



3、v$system_event



等待事件的统计信息（系统级）。展开来说，是提供了自实例启动后各个等待事件的概括。常用于获取系统等待信息的历史影象。而通过两个snapshot获取等待项增量，则可以确定这段时间内系统的等待项。



主要的字段包括：



TOTAL_WAITS





自数据库启动到现在，此等待事件总等待次数。



TIME_WAITED





此等待事件的总等待时间（单位：百分之一秒）。这个数据表示从数据库启动以来这个等待事件在所有会话（包括已经结束和正保持连接状态的会话）总的等待事件之和。



AVERAGE_WAIT







此等待事件的平均等待用时（单位：百分之一秒）。

time_waited/total_waits



TOTAL_TIMEOUTS





此等待事件总等待超时次数。

SQL – 按等待时长查看顶级事件



20161101103159169.jpg

20161101103219318.jpg



4、v$session_event



和v$system_event相类似，记录的是会话在其生命周期中各个等待事件的累计值。跟前者相比，增加了session_id信息。这些信息也会被同时累积到v$system_event中。需要注意的是，当一个会话重新建立时，统计信息将被设置为0。



5、v$session_wait、v$session



活动会话正在等待的资源或事件信息。在10g将这个视图和v$session视图进行了合并。这是一个寻找性能瓶颈的关键视图。它提供了任何情况下session在数据库中当前正在等待什么。当系统存在性能问题时，本视图可以做为一个起点指明探寻问题的方向。



需要注意的是，当等待不再存在时，会话先前出现的那些等待的历史也将消失，从而使得事后诊断非常困难。V$SESSION_EVENT提供了累积的但不是非常详细的数据。可以通过历史视图v$session_wait_history获得历史信息。



主要的字段包括：



EVENT



会话当前等待的事件，或者最后一次等待事件。



WAIT_TIME



会话等待事件的时间（单位：百分之一秒）。

值>0: 最后一次等待时间(单位:10ms)，当前未在等待状态。

值=0: session正在等待当前的事件。

值=-1: 最后一次等待时间小于1个统计单位，当前未在等待状态。

值=-2: 时间统计状态未置为可用，当前未在等待状态。



STATE



等待状态(提供对wait_time和second_in_wait字段的解释)



1）waiting：

SESSION正等待这个事件。

2）waited unknown time：

由于设置了timed_statistics值为false，导致不能得到时间信息。表示发生了等待，但时间很短。

3）wait short time：

表示发生了等待，但由于时间非常短不超过一个时间单位，所以没有记录。

4）waited knnow time:

如果session等待然后得到了所需资源，那么将从waiting进入本状态。



WAIT_TIME/SECOND_IN_WAIT



Wait_time和Second_in_wait字段值与state相关。



1）state=waiting

wait_time无用，second_in_wait值是实际的等待时间(单位:秒)。

2）state=wait unknow time

wait_time和second_in_wait都无用。

3）state=wait short time

wait_time和second_in_wait都无用。

4）state=waiting known time

wait_time值就是实际等待时间(单位:秒)，second_in_wait值无用。



6、v$session_wait_history



记录会话最近n次等待事件，即v$session_wait的历史记录。默认是记录10次，可进行修改。



7、v$event_histogram



这个视图记录了等待事件的柱状图分布，从而可以对一个等待事件具体分布有进一步了解。在v$session_event或v$system_event视图记录的是累积信息以及关于等待的平均值，无法得知个别等待消耗的时间。



下面将会话等待事件与各视图之间的关系，总结整理如下：



.一个会话一次只发生一个等待事件。如果看到了其他的等待事件，那仅仅表示在下一个时间片上发生了等待。在某个时刻只存在一个等待。

v$session_wait中的wait_time和second_in_wait字段以秒为单位，而v$session_event中的time_waited和average_wait字段是以百分之一秒为单位。

v$session_wait的等待事件结束后，v$session_event的统计信息将会发生改变。

v$session_wait的统计信息意义不大，因为信息是实时变化的。

当v$session_wait里面的等待事件结束时，v$session_wait中的seconds_in_wait字段值被复制到v$session_event中time_waited字段，而v$session_event视图的average_time字段同时也被修改。



  五、常见等待事件


Oracle的等待事件非常多，不同的版本也有些差异。下面对一些常见的等待事件进行说明。希望对大家的日常工作能带来帮助。



1、buffer busy waits



发生原因：

当一个会话将数据块从磁盘读到内存中时，它需要到内存中找到空闲的内存空间来存放这些数据块，当内存中没有空闲的空间时，就会产生这个等待。除此之外，还有一种情况就是会话在做一致性读时，需要构造数据块在某个时刻的前映像。此时需要申请内存块来存放这些新构造的数据块，如果内存中无法找到这样的内存块，也会发生这个等待事件。



参数含义：

File#

等待访问数据块所在的文件id号

Blocks

等待访问的数据块号

Id

10g之前，这个值表示等待事件原因；10g之后则表示等待事件的类别。



优化方向：根据产生此等待事件的类别不同，优化方向也不太一样。



数据块

一般优化方向是优化SQL，减少逻辑读、物理读；或者是减少单块的存储数据规模。



数据段头

一般优化方向是增加FREELISTS和FREELIST GROUPS。确保FCTFREE和PCTUSED之间的间隙不是太小，从而可以最小化FREELIST的块循环。



撤销块

一般优化方向为应用程序，错峰使用数据对象。



撤销段头

如果是数据库系统管理UNDO段，一般不需要干预。如果是自行管理的，可以减少每个回滚段的事务个数。



2、buffer latch



发生原因：

内存中数据块的存放位置是记录在一个Hash列表当中的。当一个会话需要访问某个数据块时，它首先要搜索这个Hash列表，从列表中获得数据块的地址，然后通过这个地址去访问需要的数据块，这个列表oracle会使用一个latch来保护它的完整性。当一个会话需要访问这个列表时，需要获取一个latch，只有这样，才能保证这个列表在这个会话的浏览当中不会发生改变。如果列表过长，导致会话搜索这个列表花费的时间太长，使其他的会话处于等待状态。同样的数据块被频繁访问，就是我们通常说的热块问题。



参数含义：

latch addr

会话申请的latch在SGA中的虚拟地址。

chain#

buffer chains hash列表中的索引值。当这个参数的值等于0xffffff时，说明当前的会话正在等待一个LRU latch。



优化方向：

可以考虑的优化方向有使用多个buffer pool的方式来创建更多的buffer chains或者使用参数db_block_lru_latches来增加latch的数量，以便于更多的会话可以获得latch，这两种方法可以同时使用。



3、db file sequential read



发生原因：

通常是与单个数据块相关的读取操作，大多数情况下读取一个索引块或者通过索引读取一个数据块，会记录这个等待。可能显示表的连接顺序不佳，或者不加选择地进行索引。对于大量事务处理、调整良好的系统，这一数值大多是很正常的，但在某些情况下，它可能暗示着系统中存在问题。应当将这一等待统计量与性能报告中的已知问题（如效率较低的SQL）联系起来。检查索引扫描，以保证每个扫描都是必要的，并检查多表连接的连接顺序。



DB_CACHE_SIZE 也是这些等待出现频率的决定因素。有问题的散列区域(Hash-area)连接应当出现在PGA 内存中，但它们也会消耗大量内存，从而在顺序读取时导致大量等待。它们也可能以直接路径读/写等待的形式出现。



参数含义：

file#

代表oracle要读取的文件的绝对文件号

block#

从这个文件中开始读取的起始数据块块号

blocks

读取的block数量。通常是1，表示单个block读取。



优化方向：

这个等待事件，不一定代表一定有问题。如果能确定是有问题，可以按照下面优化思路。



修改应用，避免出现大量IO的sql，或者减少其频率。

增加data buffer，提高命中率。

采用更好的磁盘子系统，减少单个IO的响应时间，防止物理瓶颈的出现。



4、db file scattered read



发生原因：

这是一个用户操作引起的等待事件，当用户发出每次I/O需要读取多个数据块这样的SQL操作时，会产生这个等待事件，最常见的两种情况全表扫描和索引快速扫描。这个名称中的scattered（发散）可能会导致很多人认为它是以scattered的方式来读取数据块的，其实恰恰相反，当发生这种等待事件时，SQL的操作都是顺序地读取数据块的，比如FTS或IFFS方式。其实这里scattered指的是读取的数据块在内存中的存放方式。它们被读取到内存中后，是以分散的方式存放在内存中，而不是连续的。



参数含义：

file#

代表oracle要读取的文件的绝对文件号。

block#

从这个文件中开始读取的起始数据块块号。

blocks

读取的block数量。



优化方向：

这种情况通常显示与全表扫描相关的等待。当全表扫描被限制在内存时，它们很少会进入连续的缓冲区内，而是分散于整个缓冲存储器中。如果这个数目很大，就表明该表找不到索引，或者只能找到有限的索引。尽管在特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要。



5、direct path read



发生原因：

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



在11g中，全表扫描可能使用direct path read方式，绕过buffer cache，这样的全表扫描就是物理读了。在10g中，都是通过gc buffer来读的，所以不存在direct path read的问题。



参数含义：

file#

文件号

first block#

读取的起始块号

block count

以first block为起点，连续读取的物理块数



优化方向：

有了这个等待事件，需要区分几种情况。一个方向是增大排序区等手段，一个方向是减少读取IO量或判断是否通过缓冲区读的方式更加高效。



6、direct path write



发生原因：

发生在oracle直接从PGA写数据到数据文件或临时文件，这个操作可以绕过SGA。在磁盘排序中最为常见。对于这种情况应该找到操作最为频繁的数据文件(如果是排序，很有可能是临时文件)，分散负载。



参数含义：

file#

文件号

first block#

读取的起始块号

block count

以first block为起点，连续写入的物理块数



优化方向：减少IO写入规模。



7、library cache lock



发生原因：

这个等待事件发生在不同用户在共享池中由于并发操作同一个数据库对象导致的资源争用的时候。比如当一个用户正在对一个表做DDL操作时，其他的用户如果要访问这张表，就会发生library cache lock等待事件，它要一直等到DDL操作完毕后，才能继续操作。



参数含义：

Handle address

被加载的对象的地址。

Lock address

锁的地址。

Mode

被加载对象的数据片段。

Namespace

被加载对象在v$db_object_cache视图中的namespace的名称。



优化方向：优化方向是查看锁定对象，减少争用。



8、library cache pin



发生原因：

这个等待事件和library cache lock一样是发生在共享池中并发操作引起的等待事件。通常来讲，如果oracle要对一些pl/sql或视图这样的对象做重新编译，需要将这些对象pin到共享池中。如果此时这个对象被其他的对象持有，就会产生一个library cache pin的等待。



参数含义：

Handle address

被加载的对象的地址。

Lock address

锁的地址。

Mode

被加载对象的数据片段。

Namespace

被加载对象在v$db_object_cache视图中的namespace的名称。



优化方向：优化方向是查看锁定对象，减少争用。



9、log file sync



发生原因：

这是一个用户会话行为导致的等待事件。当一个会话发出一个commit命令时，LGWR进程会将这个事务产生的redo log从log buffer里写到磁盘上，以保证用户提交的信息被安全地记录到数据库中。会话发出commit指令后，需要等待LGWR将这个事务产生的redo成功写入到磁盘之后，才可以继续进行后续的操作，这个等待事件就叫做log file sync。当系统中出现大量的log file sync等待事件时，应该检查数据库中是否有用户在做频繁的提交操作。这种等待事件通常发生在OLTP系统上。OLTP系统中存在很多小的事务，如果这些事务频繁被提交，可能引起大量log file sync的等待事件。



优化方向：

提高LGWR性能，尽量使用快速磁盘

使用批量提交

适当使用nologging/unrecoverable等选项



10、SQL*Net message from client



发生原因：

表明前台服务器进程等待客户进行响应。这个等待事件是由于等待用户进程的响应所引起的，它并不表明数据库就存在什么不正常。如果网络出现故障时，这种等待时间就会经常发生。



11、SQL*Net message to client



发生原因：

这个等待事件发生在服务器端向客户端发送消息的时候。当服务器端向客户端发送消息产生等待时，可能的原因是用户端太繁忙，无法及时接收服务器端送来的消息，也可能是网络问题导致消息无法从服务器端发送给客户端。



本文来自云栖社区合作伙伴"DBAplus"，原文发布时间：2016-11-01
http://click.aliyun.com/m/21917/