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

jiezi

6 年前

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

以命中率为主要参考指标

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

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

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

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

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

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

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

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

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

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

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

管理类 -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 去了解每个等待事件。下面通过一个最为常见的等待事件进行说明。

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

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

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

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

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

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

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

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

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

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

主要的字段包括：

TOTAL_WAITS

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

TIME_WAITED

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

AVERAGE_WAIT

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

time_waited/total_waits

TOTAL_TIMEOUTS

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

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

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

活动会话正在等待的资源或事件信息。在 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 值无用。

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

这个视图记录了等待事件的柱状图分布，从而可以对一个等待事件具体分布有进一步了解。在 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 的等待事件非常多，不同的版本也有些差异。下面对一些常见的等待事件进行说明。希望对大家的日常工作能带来帮助。

发生原因：

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

参数含义：

File#

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

Blocks

等待访问的数据块号

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

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

数据块

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

数据段头

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

撤销块

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

撤销段头

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

发生原因：

内存中数据块的存放位置是记录在一个 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，这两种方法可以同时使用。

发生原因：

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

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

参数含义：

file#

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

block#

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

blocks

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

优化方向：

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

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

发生原因：

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

参数含义：

file#

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

block#

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

blocks

读取的 block 数量。

优化方向：

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

发生原因：

这个等待事件发生在会话将数据块直接读取到 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 量或判断是否通过缓冲区读的方式更加高效。

发生原因：

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

参数含义：

file#

文件号

first block#

读取的起始块号

block count

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

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

发生原因：

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

参数含义：

Handle address

被加载的对象的地址。

Lock address

锁的地址。

Mode

被加载对象的数据片段。

Namespace

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

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

发生原因：

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

参数含义：

Handle address

被加载的对象的地址。

Lock address

锁的地址。

Mode

被加载对象的数据片段。

Namespace

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

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

发生原因：

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

优化方向：

提高 LGWR 性能，尽量使用快速磁盘
使用批量提交
适当使用 nologging/unrecoverable 等选项

发生原因：

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

发生原因：

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

作者：韩锋

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

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

一、等待事件由来

二、等待事件分类

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

2. 等待事件分类说明

三、理解等待事件

四、观察等待事件

1、v$event_name

2、v$system_wait_class

3、v$system_event

4、v$session_event

5、v$session_wait、v$session

6、v$session_wait_history

7、v$event_histogram

五、常见等待事件

1、buffer busy waits

2、buffer latch

3、db file sequential read

4、db file scattered read

5、direct path read

6、direct path write

7、library cache lock

8、library cache pin

9、log file sync

10、SQL*Net message from client

11、SQL*Net message to client