Oracle面试宝典-等待事件篇

请问Oracle数据库中等待事件的作用是什么?

一、等待事件由来

因为指标体系的发展，才导致等待事件的引入。总结一下，Oracle的指标体系，大致经历了下面三个阶段：

(1)以命中率为主要参考指标

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

命中率是近20年前系统性能优化的一种观点，后来该观点被证明有一定的偏差而被同行逐渐弃用，不能说完全错，命中率只能说是一个参考指标。

20年后的今天，性能优化的理论、实践和手段，相比之前都有了很大的积累和发展。现在，对性能问题进行分析和诊断，更注重各方面信息的综合分析，而不是单纯看某个指标。比较典型的，例如：系统的综合性能情况，可以通过查看ash、awr、addm或osw等各种报告进行分析和确定，单个SQL语句的性能也主要是结合具体SQL、执行计划及数据环境进行分析，获取执行计划的方法比较多，但大同小异，本质差不多。

至于性能分析诊断的思路和方法，一般是从整体到局部，逐渐细化的方法和步骤。

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

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

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

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

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

请问Oracle数据库有哪些类型的等待事件？

等待事件可分为空闲的、非空闲的两大部分。在非空闲的等待事件，又可进一步划分细的类别。

空闲等待：

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

非空闲等待：

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

等待事件分类说明：

selectwait_class,wait_class_id,count(*)

fromv$event_name

groupbywait_class,wait_class_id

orderby1;

---数据库版本Oracle 19C,等待事件数量1920个(在Oracle 10g等待事件有872个，11g等待事件1116个)。

管理类-Administrative

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

例如：

应用程序类-Application

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

例如:

群集类-Cluster

此类等待事件和Oracle RAC的资源有关（比如，gc cr block busy等待事件）。

例如：

提交确认类-Commit

此类等待事件只包含一种等待事件——在执行了一个commit命令后，等待一个重做日志写确认。

例如：

并发类-Concurrency

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

例如：

配置类-Configuration

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

例如：

空闲类-Idle

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

例如：

网络类-Network

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

例如：

其它类-Other

此类等待事件通常比较少见。

例如：

调度类-Scheduler

此类等待事件和资源管理相关。

例如：

系统I/O类-System I/O

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

例如：

用户I/O类-User I/O

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

例如：

请描述下你经常遇到的10个等待事件？

一：buffer busy wait

类型：并发类

发生原因：

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

优化方向：

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

如何找出产生此等待事件的对象和对象类型？

在出现buffer busy waits时查询V$SESSION中的 ROW_WAIT_OBJ#值。例如:

SELECTrow_wait_obj# FROMV$SESSION WHEREEVENT ='buffer busy waits';

要识别争用的对象和对象类型，可以使用从V$SESSION返回的ROW_WAIT_OBJ#的值来查询DBA_OBJECTS。例如:

SELECTowner,object_name,subobject_name,object_type

FROMDBA_OBJECTS

WHEREdata_object_id =&row_wait_obj;

或者通过SID查找对应块号，文件号，类型

select event, sid, p1, p2, p3

from v$session_wait

where sid in (69, 75)

and event like '%buffer busy waits%';

---

P1: File ID

P2: Block ID

P3: Class ID

p1、p2参数和dba_extents进行联合查询得到block所在的segment名称和segment类型

对象类型：数据块

某一或某些数据块被多个进程同时读写，成为热点块，可以通过如下这些办法来解决这个问题：

(1)降低程序的并发度，如果程序中使用了parallel查询，降低parallel degree，以免多个parallel slave同时访问同样的数据对象而形成等待降低性能；

(2)调整应用程序使之能读取较少的数据块就能获取所需的数据，减少buffer gets和physical reads；

(3)减少同一个block中的记录数，使记录分布于更多的数据块中，这可以通过若干途径实现：可以调整segment对象的pctfree值，可以将segment重建到block size较小的表空间中，还可以用alter table minimize records_per_block语句减少每块中的记录数；

(4)若热点块对象是类似自增id字段的索引，则可以将索引转换为反转索引，打散数据分布，分散热点块。

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

对象类型：数据段头

进程经常性的访问 data segment header通常有两个原因

(1) 获取或修改process freelists信息

进程频繁访问process freelists信息导致freelist争用，我们可以增大相应的segment对象的存储参数freelist或者freelist groups；若由于数据块频繁进出freelist而导致进程经常要修改freelist，则可以将pctfree值和pctused值设置较大的差距，从而避免数据块频繁进出freelist；

(2)扩展高水位标记

由于该segment空间消耗很快，而设置的next extent过小，导致频繁扩展高水位标记，解决的办法是增大segment对象的存储参数next extent或者直接在创建表空间的时候设置extent size uniform；

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

对象类型：撤销块

undo block争用是由于应用程序中存在对数据的读和写同时进行，读进程需要到undo segment中去获得一致性数据，解决办法是错开应用程序修改数据和大量查询数据的时间。

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

对象类型：撤销段头

undo segment header争用是因为系统中undo segment不够，需要增加足够的undo segment，根据undo segment的管理方法，若是手工管理模式，需要修改rollback_segments初始化参数来增加rollback segment，若是自动管理模式，可以减小transactions_per_rollback_segment初始化参数的值来使oracle自动增多rollback segment的数量。

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

二：db file sequential read

类型：用户I/O类

发生原因：db file sequential read事件和 Single Block I/O有关。

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

参数含义：

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

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

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

优化方向：

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

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

2增加data buffer，提高命中率。

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

三：db file scattered read

类型：用户I/O类

发生原因：Oracle在执行全表扫描( Full Table Scan,FTS)、索引快速全扫描( Index Fast Full Scan)时，为保障性能，尽量一次性读取多个块，这称为 Multi Block I/O。 每次执行Multi Block I/O，都会等待物理I/O结束，此时等待 db file scattered read事件。这里scattered指的是读取的数据块在内存中的存放方式。它们被读取到内存中后，是以分散的方式存放在内存中，而不是连续的。

参数含义：

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

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

Blocks：读取的block数量。

优化方向：

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

四：direct path read

类型：用户I/O类

发生原因：

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

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

参数含义：

file#：文件号

first block#：读取的起始块号

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

优化方向：

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

direct path read可能出现的问题：

在Oracle 11g中有一个新特性，为了保护已经缓存在buffer cache的数据，当出现全表扫的查询时会判断该表的大小。如果该表过大，则使用直接路径读（Direct Path Read）来获取数据。避免大量冷数据对Buffer Cache的冲击。通过直接路径读的方式绕过SGA从存储上获取数据。由于没有SGA的缓存，每一次查询都需要从存储读取产生了大量的物理读，可能会导致I/O负载过高。

新特性中如何判断全表扫的大小呢？

下面看一个隐含参数：_small_table_threshold

该参数默认为Buffer Cache的2%，如果表大于5倍_small_table_threshold就触发该特性。自动会使用DPR替代FTS。

可以通过设置10949事件屏蔽这个特性，返回到Oracle 11g之前的模式上：

alter session set events '10949 trace name context forever, level 1';

小表受到隐含参数：_small_table_threshold 影响。如果表大于 5 倍的小表限制，则自动会使用DPR替代FTS。可以设置初始化参数： _serial_direct_read 来禁用串行直接路径读。

五：db file single write

类型：用户I/O类

发生原因：其中一种情况，Oracle更新数据文件头信息时（比如发生CheckPoint）会出现这种等待事件。要考虑数据库中的数据文件数量太大，导致Oracle需要花较长的时间来做所有文件头的更新操作（CheckPoint）。

这个等待事件包含三个参数：

file# ：要读取的数据块所在数据文件的文件号。

block#：读取的起始数据块号。

blocks：需要读取的数据块数目。（通常来说在这里应该等于1）

六：direct path write

类型：用户I/O类

发生原因：这个等待事件和direct path read 正好相反，发生在oracle直接从PGA写数据到数据文件或临时文件，这个操作可以绕过SGA。可以执行direct path writes的操作包括磁盘排序、并行DML操作、直接路径插入、并行create table as select操作以及一些LOB操作。对于这种情况应该找到操作最为频繁的数据文件(如果是排序，很有可能是临时文件)，分散负载。

参数含义：

file#：文件号

first block#：读取的起始块号

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

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

七：log file sync

类型：提交类

发生原因：

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

优化方向：

下面优化建议，有助于减少log file sync等待：

(1)优化LGWR速度，以获得良好的磁盘吞吐量。例如：redo log file不要放在RAID 5上(可以考虑RAID 0或RAID 1+0)；

(2)如果有大量小事物，最好可以批量提交，减少提交次数；

(3)特定场景可以考虑使用NOLOGGING / UNRECOVERABLE选项(谨慎使用)；

(4)保证redolog足够大，确保日志切换间隔在15-20分钟；

(5)使用稳定版本数据库避免bug，具体bug修复的版本参考文档；

(6)在11.2.0.3版本中，Oracle 默认启用 _use_adaptive_log_file_sync 参数，使得 LGWR 进程写日志的方式能自动在 post/wait 和 polling 两种方式之间进行取舍，可能会导致比较严重的写日志等待（log file sync的平均单次等待时间较高）,建议关闭此功能。

参考命令：alter system set "_use_adaptive_log_file_sync"=FALSE;

八：Log File Parallel Write

类型：系统I/O

发生原因：

1、Log File Sync是从提交开始到提交结束的时间。Log File Parallel Write是LGWR开始写Redo File到Redo File写结束的时间。明确了这一点，可以知道，Log file sync 包含了log file parallel write。所以，log file sync等待时间一出，必先看log file parallel write。如果log file sync平均等待时间（也可称为提交响应时间）为20ms，log file parallel write为19ms，那么问题就很明显了，Redo file I/O缓慢，拖慢了提交的过程。2、Log File Sync的时间不止log file parallel write。服务器进程开始提交，到通知LGWR写Redo，LGWR写完Redo通知进程提交完毕，来回通知也是要消耗CPU的。除去来回通知外，Commit还有增加SCN等等操作，如果log file sync和log file parallel write差距很大，证明I/O没有问题，但有可能是CPU资源紧张，导致进程和LGWR来回通知或其他的需要CPU的操作，得不到足够的CPU，因而产生延迟。

优化方向：考虑的是如何在单个LGWR 进程的前提下让写的日志量不超过当前的LGWR 写能力。这个可以从两个方面来考虑：

1：考虑是否在应用中产生了太多无意义的重做日志，导致日志产生量太大，从而使日志的产生量超出了LGWR 的写能力，如果是这样，那么考虑通过一些方法限制重做日志的产生。

2：考虑如果日志产生量确定的情况下，如何让LGWR 进程写日志能够写得更多更快，这主要取决于两个方面，一个是LGWR 在写日志的时候是否发生了I/O 竞争，另一方面是重做日志文件所在的磁盘速度是否过低，如果是竞争引起的，移动重做日志文件到其他的磁盘上，如果是磁盘速度引起的，那么选择高速磁盘存放重做日志。

九：library cache lock

类型：并发类

发生原因：

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

参数含义：

Handle address:被加载的对象的地址。

Lock address:锁的地址。

Mode:被加载对象的数据片段。

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

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

十：SQL*Net Events

类型：

应用类:

SQL*Net break/reset to client

如果运行的代码中包含某种可能的错误，且在调用中触发了的话，服务器端本地的服务进程有义务对远程客户端告知该信息，这个告知的过程中服务进程就处于SQL*Net break/reset to client等待中，直到客户端收到问题信息为止。

SQL*Net break/reset to dblink

这个等待事件和SQL*Net more data to client等待事件基本相同，只不过等待发生在分布式事务中，即本地数据库需要将更多的数据通过dblink发送给远程数据库。由于发送的数据太多或者网络性能问题，就会产生SQL*Net more data to dblink等待事件。

空闲类：

SQL*Net vector message from dblink

SQL*Net vector message from client

SQL*Net message from client

表示服务端等待着Cilent发来请求让它处理，这时就会产生SQL*Net message from client等待事件。

网络类：

SQL*Net more data from dblink

SQL*Net vector data to client

SQL*Net vector data from client

SQL*Net vector data to dblink

SQL*Net vector data from dblink

SQL*Net message from dblink

SQL*Net more data from client

服务器端等待用户端发出更多的数据以便完成操作，比如一个大的SQL文本，导致一个SQL*Net数据包无法完成传输，这样服务器端会等待客户端把整个SQL文本发过来在做处理。

SQL*Net more data to dblink

这个等待事件和SQL*Net more data to client等待事件基本相同，只不过等待发生在分布式事务中，即本地数据库需要将更多的数据通过dblink发生给远程数据库。由于发送的数据太多或者网络性能问题导致的等待。

SQL*Net more data to client

这说明数据库在向客户端不停发送太多的数据。如果网络状况不好，或者网络流量过大，都可能导致这一等待非常显著。

SQL*Net message to client

这个等待事件发生在服务端向客户端发送消息或数据的时候，一般意味着网络瓶颈或不正确的TCP连接配置。当然它不能做为对网络延迟的准确评估或量化。

SQL*Net message to dblink

这个等待事件发生在会话在等待一个远程数据库一个确认信息，确认其发送的数据远程数据库是否收到，该数据通过dblink发送，一般是由于目标服务器无法及时接受信息。

参考：

https://dbaplus.cn/news-10-777-1.html

http://www.itpub.net/thread-2102514-1-1.html

http://www.askmaclean.com/archives/db-file-sequential-read-wait-event.html

http://www.itpub.net/thread-1777234-1-1.html

https://www.linuxidc.com/Linux/2015-09/122732.htm

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！