程序员笔记|全面解析Oracle等待事件的分类、发现及优化

1、等待事件由来

你们可能有些奇怪，为何说等待事件，先谈到了指标体系。其实，正是由于指标体系的发展，才致使等待事件的引入。总结一下，Oracle的指标体系，大体经历了下面三个阶段：sql

• 以命中率为主要参考指标

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

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

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

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

以各类资源总体消耗为优化入口依据。能够从总体角度了解数据库在一段时间内的消耗状况。较等待事件的方式，更有归纳性。常见的如"DB Time"。Oracle在不断增强这个方面的工做。服务器

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

2、等待事件分类

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

能够经过下面的方法，观察系统包含的等待事件数量及大体分类（如下语句在11g环境运行）。并发

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

1. 区分 — 空闲与非空闲等待事件

空闲等待事件，是指Oracle正等待某种工做，好比用sqlplus登陆以后，但没有进一步发出任何命令，此时该session就处于SQL*Net message from/to client等待事件状态，等待用户发出命令，任何的在诊断和优化数据库的时候，通常不用过多注意这部分事件。ide

非空闲等待事件，专门针对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） 。

3、理解等待事件

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

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

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

• file#: 表明oracle要读取的文件的绝对文件号
• block#: 从这个文件中开始读取的起始数据块块号
• blocks: 读取的block数量。一般是1，表示单个block读取。

经过上面这些参数，关联数据字典能够肯定发生等待事件的对象（即找到了热点对象）。而后针对不一样的状况，有针对性的进行解决。

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

4、观察等待事件

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

一、v$event_name

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

二、v$system_wait_class

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

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

三、v$system_event

等待事件的统计信息（系统级）。展开来讲，是提供了自实例启动后各个等待事件的归纳。经常使用于获取系统等待信息的历史影象。而经过两个snapshot获取等待项增量，则能够肯定这段时间内系统的等待项。

主要的字段包括：

• TOTAL_WAITS

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

• TIME_WAITED

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

• AVERAGE_WAIT

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

time_waited/total_waits

• TOTAL_TIMEOUTS

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

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

四、v$session_event

和v$system\_event相相似，记录的是会话在其生命周期中各个等待事件的累计值。跟前者相比，增长了session\_id信息。这些信息也会被同时累积到v$system_event中。须要注意的是，当一个会话从新创建时，统计信息将被设置为0。

五、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值无用。

六、v$session_wait_history

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

七、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字段同时也被修改。

5、常见等待事件

Oracle的等待事件很是多，不一样的版本也有些差别。下面对一些常见的等待事件进行说明。但愿对你们的平常工做能带来帮助。

一、buffer busy waits

发生缘由：

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

参数含义：

• File#

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

• Blocks

等待访问的数据块号

• Id

10g以前，这个值表示等待事件缘由；10g以后则表示等待事件的类别。

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

• 数据块

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

• 数据段头

通常优化方向是增长FREELISTS和FREELIST GROUPS。确保FCTFREE和PCTUSED之间的间隙不是过小，从而能够最小化FREELIST的块循环。

• 撤销块

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

• 撤销段头

若是是数据库系统管理UNDO段，通常不须要干预。若是是自行管理的，能够减小每一个回滚段的事务个数。

二、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，这两种方法能够同时使用。

三、db file sequential read

发生缘由：

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

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

参数含义：

• file#

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

• block#

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

• blocks

读取的block数量。一般是1，表示单个block读取。

优化方向：

这个等待事件，不必定表明必定有问题。若是能肯定是有问题，能够按照下面优化思路。

• 修改应用，避免出现大量IO的sql，或者减小其频率。
• 增长data buffer，提升命中率。
• 采用更好的磁盘子系统，减小单个IO的响应时间，防止物理瓶颈的出现。

四、db file scattered read

发生缘由：

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

参数含义：

• file#

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

• block#

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

• blocks

读取的block数量。

优化方向：

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

五、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量或判断是否经过缓冲区读的方式更加高效。

六、direct path write

发生缘由：

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

参数含义：

• file#

文件号

• first block#

读取的起始块号

• block count

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

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

七、library cache lock

发生缘由：

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

参数含义：

• Handle address

被加载的对象的地址。

• Lock address

锁的地址。

• Mode

被加载对象的数据片断。

• Namespace

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

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

八、library cache pin

发生缘由：

这个等待事件和library cache lock同样是发生在共享池中并发操做引发的等待事件。一般来说，若是oracle要对一些pl/sql或视图这样的对象作从新编译，须要将这些对象pin到共享池中。若是此时这个对象被其余的对象持有，就会产生一个library cache pin的等待。

参数含义：

• Handle address

被加载的对象的地址。

• Lock address

锁的地址。

• Mode

被加载对象的数据片断。

• Namespace

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

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

九、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等选项

十、SQL*Net message from client

发生缘由：

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

十一、SQL*Net message to client

发生缘由：

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

做者：韩锋

来源：宜信技术学院 （http://college.creditease.cn/）