在Oracle中如何调整 I/O 相关的等待

« 世界反腐败数据库 - 数据库的社会实现 | Blog首页 | Oracle在Solaris的VXFS上的异步I/O问题 »

本文主要介绍的是在出现了I/O竞争等待的时候如何去优化Oracle数据库。对Oracle数据库进行调整优化，基本上最终都可以归结到I/O调整上，因此，了解如何来优化Oracle数据库的I/O对于一个DBA来说就显得至关重要了。

一、 Oracle数据库I/O相关竞争等待简介

当Oracle数据库出现I/O相关的竞争等待的时候，一般来说都会引起Oracle数据库的性能低下，发现数据库存在I/O相关的竞争等待一般可以通过以下的三种方法来查看Oracle数据库是否存在I/O相关的竞争等待：

Ø Statpack报告中在"Top 5 Wait Events"部分中主要都是I/O相关的等待事件。

Ø 数据库的等待事件的SQL语句跟踪中主要都是I/O相关的等待事件的限制。

Ø 操作系统工具显示存储数据库文件的存储磁盘有非常高的利用率。

数据库如果发现存在I/O竞争，那我们就必须要通过各种方法来调整优化Oracle数据库。在调优数据库的过程中，其中一个重要的步骤就是对响应时间的分析，看看数据库消耗的时间究竟是消耗在具体什么上面了。对于Oracle数据库来说，响应时间的分析可以用下面公式来计算：

Response Time = Service Time + Wait Time

Service Time是指'CPU used by this session'的统计时间。

Wait Time是指所有消耗在等待事件上的总的时间。

如果我们使用性能调整的工具(如statpack)来调整数据库的时候，评测的则是所有响应时间中各个部分的相对影响，并且应该根据消耗的时间的多少来调整影响最严重的部分。

因为等待事件有很多，因此我们还需要去判定哪些是真的很重要的等待事件，很多调优工具比如说statpack都是列出最重要的等待事件，statpack工具的报告中的重要的等待事件都是包含在一个叫Top 5 Wait Events的部分中。因为这些工具都已经把重要的等待事件全部列出来了，因此就很容易的处理这些已经列出来的等待事件而不必再去首先评估所有响应时间的影响。在某些情况下， Service Time会比Wait Time显得更加重要(例如CPU使用率),此时等待事件产生的影响就显得不是那么重要了，重点调整的目标应该放在Service Time上。因此，我们应该先比较在Top 5 Wait Events部分中的'CPU used by this session'所占用的时间，然后直接调整最消耗时间的等待事件。在Oracle9i的release2的版本以后，Top 5 Wait Events部分变成了Top 5 Timed Events，Service Time也由'CPU used by this session'变成了'CPU time'来衡量，这也就意味着可以更加精确的判断在响应时间中的等待事件的影响，从而调整最需要优化的部分。

下面举一个例子来具体说明为什么在调整数据库性能的时候必须同时查看Service Time 和Wait Time，因为如果不同时都查看这两个方面，就往往容易走入调整的误区。

Top5WaitEvents ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time -------------------------------------------- ------------ ------------ ------- direct path read 9,590 15,546 86.10 db file scattered read 6,105 1,262 6.99 latch free 2,036 1,047 5.80 log file sync 107 131 .73

db file parallel write 40 69 .38

上面是一个大约30分钟的statpack收集的信息的Top 5 Wait Events部分，如果基于上面给出的列表，我们很容易发现direct path read的wait很高，并且会试图去调整这个等待事件，但是这样做就没有考虑到Service Time。我们来看看在这个statpack中关于Service Time的统计：

Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ CPU used by this session 429,648 238.7 257.4

让我们来大致的计算一下响应时间：

'Wait Time' = 15,546 x 100% / 86.10% = 18,056 cs

'Service Time' = 429,648 cs

'Response Time' = 429,648 + 18,056 = 44,7704 cs

接着来计算一下响应时间中各个部分的比例：

CPU time = 95.97%

direct path read = 3.47%

db file scattered read = 0.28%

latch free = 0.23%

log file sync = 0.03%

db file parallel write = 0.02%

从上面的计算中我们可以明显的看出来，I/O相关的等待事件所消耗的时间在整个响应时间中占的比例并不大，只不过是很小的一部分，而相对来说Service Time所消耗的时间远远大于Wait Time，因此，应该直接调整的是Service Time(CPU的使用率)而不是I/O相关的等待事件，因此，在调优数据库的时候要尽量的避免走入这种误区。

二、 Oracle数据库I/O相关竞争等待的处理方法

接着来具体看看对于出现的I/O问题处理的一些方法。

在使用了statpack这类的工具分析了数据库的响应时间后，如果数据库的性能主要是被一些I/O相关的等待事件所限制住了，那么可以针对这种情况可以采用处理I/O问题的一些方法，下面对这些方法的一些概念和基本原理进行简单的阐述说明。

方法一：优化Oracle数据库的SQL语句来减少数据库对I/O的需求：

如果数据库没有任何用户的SQL运行的话，一般来说只会产生很少的磁盘I/O或者几乎没有磁盘I/O，基本上来说数据库产生I/O的最终原因都是直接或者间接的由于用户执行SQL语句导致的。这也就意味着我们可以控制单个SQL语句避免其产生大量的I/O来减少整个数据库对磁盘I/O的需求，通过优化SQL语句改变其执行计划以便让其产生尽可能少的I/O。一般典型的存在问题的情况仅仅只是很少的几个SQL语句，但是由于其相应的执行计划不理想，会导致产生大量的物理磁盘I/O，从而使得整个数据库的性能非常之差。因此，让用户执行的SQL语句优化产生比较好的执行计划来减少磁盘I/O是一种非常行之有效的方法。

方法二：调整实例的初始化参数来减少数据库的I/O需求：

一般来说可以通过两种途径：

一种途径是通过内存缓存来减少I/O。数据库的I/O分为两种，一种是实际读取了数据文件的物理I/O，一种是从缓存中读取数据的逻辑I/O，可以通过使用一定数量的内存缓存来减少物理I/O(例如高速缓存区、日志缓存区以及各种排序区等等)。适当的增大高速缓存区，可以有更多的缓存供给数据库的进程使用，从缓存中读取所需要的数据，这样产生的I/O都是逻辑I/O，而不是直接从物理磁盘上读取数据，减少了物理I/O的产生。设置一个适当的排序区，可以减少在排序操作中读取临时表空间数据文件所在磁盘的次数，而尽可能多的使用缓存中的排序区来排序。其他的缓存区的工作原理基本都是一致的，都是通过使用缓存来减少读取物理磁盘的次数来降低I/O。

另外一种途径是调整一次读取多个BLOCK的大小，单独的一次多个BLOCK读取的操作的大小是由实例的初始化参数db_file_multiblock_read_count来控制的。如果执行比较大的I/O操作，一次读取的多个BLOCK大小越大，所需要的时间就会越短。例如：操作系统的一次能够传输的最大I/O大小是8M，一次性请求传输50M的数据会比请求5次每次只是请求1M的速度快的多。但是，当一次读取的多个BLOCK的大小超过了操作系统一次能够传输的最大的I/O大小，这个差别就基本不明显了。如一次性传输100M和一次性传输1G的大小在时间上基本上没有什么差别了，效率差不多了。因为整个I/O的所消耗的时间分为I/O Setup Time和I/O Transfer Time两个主要部分，I/O Setup Time可以看成是I/O的寻道所消耗的时间，I/O Transfer Time可以看成是I/O传输所消耗的时间。当一次读取的多个BLOCK的大小比较小的时候，读取的一定数量的BLOCK就使得读取次数就会比较多，每次I/O读取都要先寻道，并且寻道时间在这个时候所用的时间会占到整个I/O完成时间的绝大部分，导致整个I/O完成所消耗的时间也就会比较多了。而I/O传输一定数量的BLOCK的时间相对固定，不管传输的次数多少，基本上变化不大，因此影响I/O读取时间长短的主要就是取决于I/O Setup Time所花费的时间。

因此，在配置数据库初始化参数的时候，根据操作系统的I/O吞吐能力都会设置的一次读取多个BLOCK的大小尽量多，以减少读取I/O的次数。

方法三：在操作系统级别上优化I/O：

在操作系统级别上优化磁盘的I/O，以提高I/O的吞吐量，如果操作系统支持异步I/O，尽量去使用异步I/O；还可以使用高级文件系统的一些特性，例如直接I/O读取，忽略掉操作系统的文件缓存，也就是我们平时所说的使用裸设备。还有一种可行的方法是增大每次传输的最大I/O大小的限制，以便每次能够传输的I/O尽可能的大。

方法四：通过使用RAID、SAN、NAS来平衡数据库的I/O：

RAID是Redundent Array of Independent Disks的缩写，直译为"廉价冗余磁盘阵列"，也简称为"磁盘阵列"。RAID 的优点是传输速率高并且可以提供容错功能。在RAID中，可以让很多磁盘驱动器同时传输数据，而这些磁盘驱动器在逻辑上又是一个磁盘驱动器，所以使用RAID可以达到单个磁盘驱动器几倍、几十倍甚至上百倍的速率。因为普通磁盘驱动器无法提供容错功能，如果不包括写在磁盘上的CRC(循环冗余校验)码的话。RAID容错是建立在每个磁盘驱动器的硬件容错功能之上的，所以它提供更高的安全性。RAID分为以下几个级别：

Ø RAID 0: RAID 0 并不是真正的RAID结构, 没有数据冗余. RAID 0 连续地分割数据并并行地读/写于多个磁盘上. 因此具有很高的数据传输率. 但RAID 0在提高性能的同时,并没有提供数据可靠性,如果一个磁盘失效, 将影响整个数据.因此RAID 0 不可应用于需要数据高可用性的关键应用。

Ø RAID 1: RAID 1通过数据镜像实现数据冗余, 在两对分离的磁盘上产生互为备份的数据. RAID 1可以提高读的性能,当原始数据繁忙时, 可直接从镜像拷贝中读取数据.RAID 1是磁盘阵列中费用最高的, 但提供了最高的数据可用率. 当一个磁盘失效, 系统可以自动地交换到镜像磁盘上, 而不需要重组失效的数据。

Ø RAID 2: 从概念上讲, RAID 2 同RAID 3类似, 两者都是将数据条块化分布于不同的硬盘上, 条块单位为位或字节.然而RAID 2 使用称为"加重平均纠错码"的编码技术来提供错误检查及恢复. 这种编码技术需要多个磁盘存放检查及恢复信息, 使得RAID 2技术实施更复杂. 因此,在商业环境中很少使用。

Ø RAID 3: 不同于RAID 2, RAID 3使用单块磁盘存放奇偶校验信息. 如果一块磁盘失效, 奇偶盘及其他数据盘可以重新产生数据. 如果奇偶盘失效,则不影响数据使用.RAID 3对于大量的连续数据可提供很好的传输率, 但对于随机数据, 奇偶盘会成为写操作的瓶颈。

Ø RAID 4: 同RAID 2, RAID 3一样, RAID 4, RAID 5也同样将数据条块化并分布于不同的磁盘上, 但条块单位为块或记录. RAID 4使用一块磁盘作为奇偶校验盘, 每次写操作都需要访问奇偶盘, 成为写操作的瓶颈. 在商业应用中很少使用。

Ø RAID 5: RAID 5没有单独指定的奇偶盘, 而是交叉地存取数据及奇偶校验信息于所有磁盘上. 在RAID5 上, 读/写指针可同时对阵列设备进行操作, 提供了更高的数据流量. RAID 5更适合于小数据块,随机读写的数据.RAID 3与RAID 5相比, 重要的区别在于RAID 3每进行一次数据传输,需涉及到所有的阵列盘.而对于RAID 5来说, 大部分数据传输只对一块磁盘操作, 可进行并行操作.在RAID 5中有"写损失", 即每一次写操作,将产生四个实际的读/写操作, 其中两次读旧的数据及奇偶信息, 两次写新的数据及奇偶信息。

Ø RAID 6: RAID 6 与RAID 5相比,增加了第二个独立的奇偶校验信息块. 两个独立的奇偶系统使用不同的算法, 数据的可靠性非常高. 即使两块磁盘同时失效,也不会影响数据的使用. 但需要分配给奇偶校验信息更大的磁盘空间,相对于RAID 5有更大的"写损失". RAID 6 的写性能非常差, 较差的性能和复杂的实施使得RAID 6很少使用。

SAN(Storage Area Network，存储局域网)是独立于服务器网络系统之外几乎拥有无限存储能力的高速存储网络，这种网络采用高速的光纤通道作为传输媒体，以FC (Fiber Channel，光通道)+ SCSI(Small Computer System Interface，小型计算机系统接口) 的应用协议作为存储访问协议，将存储子系统网络化，实现了真正高速共享存储的目标。一个完整的SAN包括：支持SAN的主机设备，支持SAN的储存设备，用于连接SAN的连接设备，支持SAN的管理软件，支持SAN的服务。

网络附加存储设备（Network Attached Storage，NAS）是一种专业的网络文件存储及文件备份设备，或称为网络直联存储设备、网络磁盘阵列。NAS是基于LAN的，按照TCP/IP协议进行通信，面向消息传递，以文件的I/O方式进行数据传输。在LAN环境下，NAS已经完全可以实现异构平台之间的数据级共享，比如NT、UNIX等平台的共享。一个NAS包括处理器，文件服务管理模块和多个的硬盘驱动器用于数据的存储。 NAS 可以应用在任何的网络环境当中。主服务器和客户端可以非常方便地在NAS上存取任意格式的文件，包括SMB格式（Windows）NFS格式(Unix, Linux)和CIFS格式等等。NAS 系统可以根据服务器或者客户端计算机发出的指令完成对内在文件的管理。NAS是在RAID的基础上增加了存储操作系统，因此,NAS的数据能由异类平台共享。

因此，利用RAID、SAN、NAS的技术在多个物理磁盘之间平衡数据库的I/O，尽量避免数据库产生I/O竞争的瓶颈。

方法五：手工分配数据文件到不同的文件系统、控制器和物理设备来重新调整数据库I/O:

如果数据库目前的存储设备不算太好，那么采用这种方法是一个不错的选择。这样可以让所有的磁盘得到充分的利用，不至于出现某些磁盘的I/O过于太高，而某些磁盘就根本没有被使用的情况，使得在配置较低的情况下得到一个比较好的数据库性能。

需要注意的一点是对于大部分数据库来说，一些I/O是一直会存在的。如果上述的方法都尝试过但是数据库的I/O性能还是没有达到预定的要求，可以尝试删除数据库中一些不用的旧数据或者使用性能更好的硬件设施。

三、 Oracle数据库I/O相关的等待事件的介绍和相应的解决方法

下面是总结的在Oracle数据库中最经常出现的一些I/O相关的等待事件：

数据文件I/O相关的等待事件：

Ø db file sequential read

Ø db file scattered read

Ø db file parallel read

Ø direct path read

Ø direct path write

Ø direct path read (lob)

Ø direct path write (lob)

控制文件I/O相关的等待事件：

Ø control file parallel write

Ø control file sequential read

Ø control file single write

重做日志文件I/O相关的等待事件：

Ø log file parallel write

Ø log file sync

Ø log file sequential read

Ø log file single write

Ø switch logfile command

Ø log file switch completion

Ø log file switch (clearing log file)

Ø log file switch (checkpoint incomplete)

Ø log switch/archive

Ø log file switch (archiving needed)

高速缓存区I/O相关的等待事件：

Ø db file parallel write

Ø db file single write

Ø write complete waits

Ø free buffer waits

下面来对这些I/O相关的等待事件进行具体的说明和相应的处理方法。

数据文件相关的I/O等待事件：

Ø db file sequential read等待事件：

这个是非常常见的I/O相关的等待事件。在大多数的情况下读取一个索引数据的BLOCK或者通过索引读取数据的一个BLOCK的时候都会去要读取相应的数据文件头的BLOCK。在早期的版本中会从磁盘中的排序段读取多个BLOCK到高速缓存区的连续的缓存中。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表Oracle要读取的文件的ABSOLUTE文件号，P2代表Oracle从这个文件中开始读取的BLOCK号，P3代表Oracle从这个文件开始读取的BLOCK号后读取的BLOCK数量，通常这个值为1，表明是单个BLOCK被读取，如果这个值大于1，则是读取了多个BLOCK，这种多BLOCK读取常常出现在早期的Oracle版本中从临时段中读取数据的时候。

如果这个等待事件在整个等待时间中占主要的部分，可以采用以下的几种方法来调整数据库。

方法一：从statpack的报告中的"SQL ordered by Reads"部分或者从V$SQL视图中找出读取物理磁盘I/O最多的几个SQL语句，优化这些SQL语句以减少对I/O的读取需求。

如果有Index Range scans，但是却使用了不该用的索引，就会导致访问更多的BLOCK，这个时候应该强迫使用一个可选择的索引，使访问同样的数据尽可能的少的访问索引块，减少物理I/O的读取；如果索引的碎片比较多，那么每个BLOCK存储的索引数据就比较少，这样需要访问的BLOCK就多，这个时候一般来说最好把索引rebuild，减少索引的碎片；如果被使用的索引存在一个很大的Clustering Factor，那么对于每个索引BLOCK获取相应的记录的时候就要访问更多表的BLOCK，这个时候可以使用特殊的索引列排序来重建表的所有记录，这样可以大大的减少Clustering Factor，例如：一个表有A,B,C,D,E五个列，索引建立在A,C上，这样可以使用如下语句来重建表：

CREATE TABLE TABLE_NAME AS SELECT * FROM old ORDER BY A,C;

此外，还可以通过使用分区索引来减少索引BLOCK和表BLOCK的读取。

方法二：如果不存在有问题的执行计划导致读取过多的物理I/O的特殊SQL语句，那么可能存在以下的情况：

数据文件所在的磁盘存在大量的活动，导致其I/O性能很差。这种情况下可以通过查看Statpack报告中的"File I/O Statistics"部分或者V$FILESTAT视图找出热点的磁盘，然后将在这些磁盘上的数据文件移动到那些使用了条带集、RAID等能实现I/O负载均衡的磁盘上去。

使用如下的查询语句可以得到各个数据文件的I/O分布：

select d.name name, f.phyrds, f.phyblkrd, f.phywrts, f.phyblkwrt, f.readtim, f.writetim from v$filestat f, v$datafile d where f.file# = d.file# order by f.phyrds desc, f.phywrts desc;

从Oracle9.2.0开始，我们可以从V$SEGMENT_STATISTICS视图中找出物理读取最多的索引段或者是表段，通过查看这些数据，可以清楚详细的看到这些段是否可以使用重建或者分区的方法来减少所使用的I/O。如果Statpack设置的level为7就会在报告中产生"Segment Statistics"的信息。

SQL> select distinct statistic_name from v$segment_statistics;

STATISTIC_NAME

----------------------------------------------------------------

ITL waits

buffer busy waits

db block changes

global cache cr blocks served

global cache current blocks served

logical reads

physical reads

physical reads direct

physical writes

physical writes direct

row lock waits

11 rows selected.

从上面的查询可以看到相应的统计名称，使用下面的查询语句就能得到读取物理I/O最多的段：

select object_name,object_type,statistic_name,value

from v$segment_statistics

where statistic_name='physical reads'

order by value desc;

方法三：如果不存在有问题的执行计划导致读取过多的物理I/O的特殊SQL语句，磁盘的I/O也分布的很均匀，这种时候我们可以考虑增大的高速缓存区。对于Oracle8i来说增大初始化参数DB_BLOCK_BUFFERS，让Statpack中的Buffer Cache的命中率达到一个满意值；对于Oracle9i来说则可以使用Buffer Cache Advisory工具来调整Buffer Cache；对于热点的段可以使用多缓冲池，将热点的索引和表放入到KEEP Buffer Pool中去，尽量让其在缓冲中被读取，减少I/O。

Ø db file scattered read等待事件

这个也是一个非常常见的等待事件。当Oracle从磁盘上读取多个BLOCK到不连续的高速缓存区的缓存中就会发生这个等待事件，Oracle一次能够读取的最多的BLOCK数量是由初始化参数DB_FILE_MULTIBLOCK_READ_COUNT来决定，这个等待事件一般伴随着全表扫描或者Fast Full Index扫描一起出现。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表Oracle要读取的文件的ABSOLUTE文件号，P2代表Oracle从这个文件中开始读取的BLOCK号，P3代表Oracle从这个文件开始读取的BLOCK号后读取的BLOCK数量。

如果这个等待事件在整个等待时间中占了比较大的比重，可以如下的几种方法来调整Oracle数据库：

方法一：找出执行全表扫描者Fast Full Index扫描的SQL语句，判断这些扫描是否是必要的，是否导致了比较差的执行计划，如果是，则需要调整这些SQL语句。

从Oracle9i开始提供了一个视图V$SQL_PLAN，可以很快的帮助找到那些全表扫描或者Fast Full Index扫描的SQL语句，这个视图会自动忽略掉关于数据字典的SQL语句。

查找全表扫描的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;

查找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;

如果是Oracle8i的数据库，可以从V$SESSION_EVENT视图中找到关于这个等待事件的进程sid，然后根据sid来跟踪相应的会话的SQL。

select sid,event from v$session_event where event='db file sequential read'

或者可以查看物理读取最多的SQL语句的执行计划，看是否里面包含了全表扫描和Fast Full Index扫描。通过如下语句来查找物理读取最多的SQL语句：

select sql_text from (

select * from v$sqlarea

order by disk_reads)

where rownum<=10;

方法二：有时候在执行计划很好情况下也会出现多BLOCK扫描的情况，这时可以通过调整Oracle数据库的多BLOCK的I/O，设置一个合理的Oracle初始化参数DB_FILE_MULTIBLOCK_READ_COUNT，尽量使得满足以下的公式：

DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system

DB_FILE_MULTIBLOCK_READ_COUNT是指在全表扫描中一次能够读取的最多的BLOCK数量，这个值受操作系统每次能够读写最大的I/O限制，如果设置的值按照上面的公式计算超过了操作系统每次的最大读写能力，则会默认为max_io_size/db_block_size。例如DB_FILE_MULTIBLOCK_READ_COUNT设置为32，DB_BLOCK_SIZE为8K，这样每次全表扫描的时候能读取256K的表数据，从而大大的提高了整体查询的性能。设置这个参数也不是越大越好的，设置这个参数之前应该要先了解应用的类型，如果是OLTP类型的应用，一般来说全表扫描较少，这个时候设定比较大的DB_FILE_MULTIBLOCK_READ_COUNT反而会降低Oracle数据库的性能，因此CBO在某些情况下会因为多BLOCK读取导致COST比较低从而错误的选用全表扫描。

此外，还可以通过对表和索引使用分区、将缓存区的LRU末端的全表扫描和Fast Full Index扫描的的BLOCK放入到KEEP缓存池中等方法调整这个等待事件。

Ø db file parallel read等待事件

当Oracle从多个数据文件中并行读取多个BLOCK到内存的不连续缓冲中(高速缓存区或者是PGA)的时候可能就会出现这个等待事件。这种并行读取一般出现在恢复操作中或者是从缓冲中预取数据达到最优化(而不是多次从单个BLOCK中读取)。这个事件表明会话正在并行执行多个读取的需求。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表有多少个文件被读取所请求，P2代表总共有多少个BLOCK被请求，P3代表总共有多少次请求。

如果在等待时间中这个等待事件占的比重比较大，可以按照处理db file sequential read等待事件的方法来处理这个事件。

Ø direct path read等待事件

这个等待事件一般出现在Oracle将数据直接读入到PGA内存中(而不是高速缓存区)，如果系统使用了异步I/O，那么Oracle可以一边提交请求一边同时继续处理请求，这样能加速I/O请求的结果并会出现direct path read等待直到请求I/O完成。如果没有使用异步I/O，I/O请求会被阻塞直到之前的I/O请求完成后，但是此时不会出现I/O等待，会话稍后重新恢复并加速I/O请求的完成，此时就会出现direct path read等待。因此，对于这个等待事件容易产生两方面的误解：一是认为等待的总的数量不能反映出I/O请求的数量，二是消耗在这个等待事件上的总的时间不能反映出实际的等待时间。这类型的读取请求主要是用于不在内存中排序的I/O、并行查询以及预读操作(提前请求一个进程即将使用的BLOCK)。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表等待I/O读取请求的文件的ABSOLUTE文件号，P2代表等待I/O读取请求的第一个BLOCK号，P3代表总共有多少个连续的BLOCK被请求读取。

这个等待事件的等待时间是指等待BLOCK直到显著的I/O请求完成的时间。值得注意的是对于异步I/O来说等待时间并不是I/O本身所耗费的时间，因为此时等待并没有开始，而且在这个等待事件中Oracle并不会出现超时的现象。在DSS类型的系统中，执行大量批处理操作的过程中出现这个等待事件属于很正常的现象，然而如果在OLTP类型的系统中大量出现这个等待事件则是表明Oracle数据库存在问题需要调整。

如果在等待时间中这个等待事件占的比重比较大，可以从如下几个方面来调整：

如果是等待的文件是临时表空间的数据文件，那么需要查看是否存在大量不合理的磁盘排序，优化相应的存在问题的SQL语句。如果是Oracle9i可以考虑使用自动SQL执行内存管理，Oracle8i的话可以手工的调整各种排序区。

尽量减少I/O请求的次数，通过设置初始化参数DB_FILE_DIRECT_IO_COUNT，使得满足

DB_BLOCK_SIZE x DB_FILE_DIRECT_IO_COUNT = max_io_size of system

在Oracle8i中默认这个值为64个BLOCK；在Oracle9i中可以设置隐含参数_DB_FILE_DIRECT_IO_COUNT，参数的值也变成了BYTES而不是BLOCK数量了，默认值也变成了1M。

确认异步I/O是否配置正确，使用异步I/O不会减少这个等待事件的等待时间但是却可以减少会话所消耗的时间。

检查是否存在I/O消耗很严重的SQL语句，如果存在，尝试优化SQL语句减少I/O的消耗。

最后确认一下是否达到了磁盘的I/O极限，如果是，则需要考虑更换性能更好的硬件设备。

Ø direct path write等待事件

direct path write是允许一个会话让一个I/O写请求入队列的同时处理操作系统的I/O。如果会话想确认明显的写是否已经完成就会出现这个等待事件。因为会话需要空的缓存和空的槽位(等待之前的I/O释放)，或者是会话需要确认所有的写操作都已经完成。如果没有使用异步I/O，I/O请求会被阻塞直到之前的I/O请求完成后，但是此时不会出现I/O等待，会话稍后重新恢复并加速I/O请求的完成，此时就会出现direct path write等待。因此，对于这个等待事件容易产生两方面的误解：一是认为等待的总的数量不能反映出I/O请求的数量，二是消耗在这个等待事件上的总的时间不能反映出实际的等待时间。这类型的写请求主要是用于直接装载数据的操作(create table as select)、并行的DML操作、不在内存中排序的I/O以及写入没有cache的LOB段操作。

这个等待事件的等待时间是指等待BLOCK直到明显的I/O请求完成的时间。通常来说，如果不是存在特殊的JOB,一般是不会出现这个等待事件，如果在等待时间中这个等待事件占的比重比较大，可以从如下几个方面来调整：

如果是等待的文件是临时表空间的文件，那么需要查看是否存在大量不合理的磁盘排序，优化相应的存在问题的SQL语句。如果是Oracle9i可以考虑使用自动SQL执行内存管理，Oracle8i的话可以手工的调整各种排序区。

尽量减少I/O请求的次数，通过设置初始化参数DB_FILE_DIRECT_IO_COUNT，使得满足

DB_BLOCK_SIZE x DB_FILE_DIRECT_IO_COUNT = max_io_size of system

在Oracle8i中默认这个值为64个BLOCK；在Oracle9i中可以设置隐含参数_DB_FILE_DIRECT_IO_COUNT，参数的值也变成了BYTES而不是BLOCK数量了，默认值也变成了1M。

确认异步I/O是否配置正确，异步I/O不会减少这个等待事件的等待时间但是却可以减少会话所消耗的时间。

检查是否存在I/O消耗很严重的SQL语句，如果存在，尝试优化SQL语句减少I/O的消耗。

最后确认一下是否达到了磁盘的I/O极限，如果是，则需要考虑更换更好的硬件设备。

Ø direct path read/write (lob) 等待事件

这个等待事件是从Oracle8.1.7开始出现的，表明在等待直接路径读取访问一个LOB对象。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表等待I/O读取请求的文件的ABSOLUTE文件号，P2代表等待I/O读取请求的第一个数据BLOCK地址，P3代表总共有多少个连续的BLOCK被请求读取。

对于那些没有cache的LOB对象，强烈建议将其所在的数据文件放置在存在缓存的磁盘上(例如文件系统)，这样使得直接读取操作能够受益于那些非Oracle的cache，加快读取的速度。

控制文件相关I/O等待事件：

Ø control file parallel write等待事件

这个等待事件表明服务器进程在更新所有的控制文件的时候等待I/O的完成。因为控制文件所在的磁盘的I/O过高引起无法完成对所有控制文件的物理写入，写入控制文件的这个会话会拥有CF队列，因此其他的会话都会在这个队列中等待。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，这三个参数都设置为同样的值，代表控制文件对I/O的请求数量。当Oracle更新控制文件的时候是同时更新所有控制文件并写入同样的信息。

如果在等待时间中这个等待事件占的比重比较大，可以从如下几个方面来调整：

在确保控制文件不会同时都丢失的前提下，将控制文件的数量减小到最少。

如果系统支持异步I/O，则推荐尽量使用异步I/O，这样可以实现真正并行的写入控制文件。

将控制文件移动到负载比较低，速度比较快的磁盘上去。

Ø control file sequential read等待事件

读取控制文件的时候遇到I/O等待就会出现这个等待事件，例如备份控制文件的时候、读取BLOCK头部都会引起这个等待事件，等待的时间就是消耗在读取控制文件上的时间。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表正在读取的控制文件号，通过下面的SQL语句可以知道究竟是具体是哪个控制文被读取：

SELECT * FROM X$KCCCF WHERE INDX = <file#>；

P2代表开始读取的控制文件BLOCK号，它的BLOCK大小和操作系统的BLOCK大小一样，通常来说是512K，也有些UNIX的是1M或者2M，P3代表会话要读取BLOCK的数量。一般来说使用参数P1、P2来查询BLOCK,当然也可以包括参数P3，但是那样最终就变成了一个多BLOCK读取，因此我们一般都忽略参数P3。

如果这个等待事件等待的时间比较长，则需要检查控制文件所在的磁盘是否很繁忙，如果是，将控制文件移动到负载比较低，速度比较快的磁盘上去。如果系统支持异步I/O，则启用异步I/O。对于并行服务器来说，如果这种等待比较多，会造成整个数据库性能下降，因为并行服务器之间的一些同步是通过控制文件来实现的。

Ø control file single write等待事件

这个等待事件出现在写控制文件的共享信息到磁盘的时候，这是个自动操作，并且通过一个实例来保护的，如果是并行的数据库服务器，那么对于并行服务器来说也只能有一个实例能够执行这个操作。这个事件的等待事件就是写操作所消耗的时间。

SELECT * FROM X$KCCCF WHERE INDX = <file#>；

P2代表开始读取的控制文件BLOCK号，它的BLOCK大小和操作系统的BLOCK大小一样，通常来说是512K，也有些UNIX的是1K或者2K，P3代表会话要读取BLOCK的数量。一般来说使用参数P1、P2来查询BLOCK,当然也可以包括参数P3，但是那样最终就变成了一个多BLOCK读取，因此我们一般都忽略参数P3。

尽管这个事件的是single write，事实上也会出现多BLOCK写的情况，即P3>1。使用参数P1、P2来查询检测BLOCK而不用去考虑P3的值。

重做日志文件相关的等待事件：

Ø log file parallel write等待事件

这个等待事件出现在当LGWR后台进程从日志缓冲区写日志信息到磁盘上的重做日志文件的时候。只有启用了异步I/O的时候LGWR进程才会并行写当前日志组内的重做日志文件，否则LGWR只会循环顺序逐个的写当前日志组重做日志文件。LGWR进程不得不等待当前日志组所有的重做日志文件成员全部写完，因此，决定这个等待事件的等待时间长短的主要因素是重做日志文件所在磁盘的I/O读写的速度。

如果是当前的LGWR进程写的速度不够快导致了这个等待事件，可以通过查看一些和重做日志相关的统计值判定当前的LGWR进程是否效率很低，具体的可以查看"redo writes"、"redo blocks written"、"redo write time"、"redo wastage"、"redo size"统计值，这些都是和LGWR进程性能直接相关的一些统计值。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表正在被写入的重做日志文件组中的重做日志文件号，P2代表需要写入重做日志组中每个重做日志文件的重做日志BLOCK数量，P3代表I/O请求的次数，需要被写入的BLOCK会被分成多次分别请求。

如果这个等待事件占用的等待时间比较多，可以从以下几个方面来进行调整：

对能使用UNRECOVERABLE/NOLOGGING的操作尽量使用这两个选项来减少重做日志的产生。

在保证不会同时丢失重做日志文件的前提下尽量减少重做日志组中的成员的个数，减少每次写重做日志组文件的时间。

除非在备份的情况下，否则不要在将表空间置于热备的模式下，因为表空间处于热备的模式下会产生更多的重做日志文件。

对于使用LogMiner、Logical Standby或者Streams，在能够满足要求功能的前提下，尽量使用最低级别的追加日志以减少重做日志的产生。

尽量将同一个日志组内的重做日志文件分散到不同的硬盘上，减少并行写重做日志文件的时候产生的I/O竞争。

不要将重做日志文件放置在RAID-5的磁盘上，最好使用裸设备来存放重做日志文件。

如果设置了归档模式，不要将归档日志的目的地设置为存放重做日志存放的磁盘上面，避免引起I/O竞争。

Ø log file sync等待事件

这个等待事件是指等待Oracle的前台的COMMIT和ROLLBACK操作进程完成，有时候这个等待事件也会包括等待LGWR进程把一个会话事务的日志记录信息从日志缓冲区中写入到磁盘上的重做日志文件中。因此，当前台进程在等待这个事件的时候，LGWR进程同时也在等待事件log file parallel write。理解什么造成这个等待事件的关键在于对比这个等待事件和log file parallel write等待事件的平均等待时间：如果它们的等待时间差不多，那么就是重做日志文件的I/O引起了这个等待事件，则需要调整重做日志文件的I/O，这个在之后会有详细的讲述。如果log file parallel write等待事件的平均等待时间明显小于log file sync等待事件的等待时间，那么就是一些其他的写日志的机制在COMMIT和ROLLBACK操作的时候引起了等待，而不是I/O引起的等待，例如重做日志文件的latch的竞争，会伴随着出现latch free或者LGWR wait for redo copy等待事件。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表在日志缓冲区中需要被写入到重做日志文件中的缓存的数量，写入的同时会确认事务是否已经被提交，并且保留提交信息到实例意外中断之前，因此必须等待LGWR将P1数量的缓存写入重做日志文件为止。P2、P3属于无用的参数。

如果这个等待事件在整个等待时间中占了比较大的比重，可以从以下三个方面来调整这个等待事件：

调整LGWR进程使其具有更好的磁盘I/O吞吐量，例如不要将日志文件放置在RAID5的磁盘上。

如果存在很多执行时间很短的事务，可以考虑将这些事务集合成一个批处理事务以减少提交的次数，因为每次提交都需要确认相关的日志写入重做日志文件，因此使用批处理事务来减少提交的次数是一种非常行之有效的减少I/O的方法。

查看是否一些操作可以安全的使用NOLOGGING或者UNRECOVERABLE选项，这样可以减少日志的产生。

Ø log file sequential read等待事件

这个等待事件是指等待读取重做日志文件中的日志记录，等待的时间就是耗费在完成整个读取日志记录的物理I/O操作的时间。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表一个日志组里面所有日志文件的相对sequence号，P2代表日志文件在指定物理块大小的偏移量，P3代表读取BLOCK的数量，如果P3的值为1，一般来说都是在读取日志文件头。

Ø log file single write等待事件

这个等待事件是指等待写重做日志文件操作完成，常常是在等待写重做日志文件头，例如在增加一个新的重做日志组成员的时候，Oracle数据库就会往这个重做日志文件头写入相应的sequence号。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表正在被写入的重做日志文件组的组号，P2代表日志文件在指定物理块大小的偏移量，P3代表写入BLOCK的数量。

因为single write通常都是在写或者重写日志文件头的时候出现，因此开始的block号总是为1。一般如果出现这个等待事件，应该对重做日志文件尽量使用裸设备，避免将多个日志文件放在同一个磁盘上，减少产生I/O竞争的可能。

Ø switch logfile command等待事件

这个等待事件是指执行日志文件切换命令的时候等待日志文件切换完成，Oracle数据库会每隔五秒钟就检测一次是否超时。

如果出现这个等待事件，表明花费了很长的时间去切换重做日志文件，此时我们需要去检查数据库的告警日志文件查看Oracle后台进程LGWR是否正常在工作。

Ø log file switch completion等待事件

这个等待事件是指由于当前重做日志文件已经被写满了而Oracle后台进程LGWR需要完成写完当前重做日志文件并且要打开一个新的重做日志文件而导致的重做日志文件切换的等待，或者是其他请求需要切换重做日志文件导致等待。

如果当前的重做日志写满了，这个时候Oracle数据库就需要切换重做日志文件来提供足够的磁盘空间给重做日志写日志缓存。但是由于一些其他的进程也同样可以引起重做日志的切换，Oracle数据库不会同时去切换重做日志两次，因此，就出现了这个等待事件，在Oracle数据库早期的版本中还有log_file_switch_checkpoint_incomplete、log_file_switch_archiving_needed、log_file_switch_clearing_log_file的等待事件。

Ø log file switch (checkpoint incomplete) 等待事件

这个等待事件是指由于当前重做日志的检查点没有及时的完成而导致重做日志文件无法切换到下一个日志文件引起的日志文件切换的等待。

调整这个等待事件的方法一般是加速检查点的完成，可以通过减小buffer cache缓冲区或者增加更多的DBWR进程、调整相关检查点的初始化参数等方法来达到相应的效果。

Ø log file switch (archiving needed)等待事件

这个等待事件是指当前的重做日志文件准备切换到下一重做日志文件，但是当前重做日志文件因为没有被归档而导致等待，这个等待事件只出现于采用了归档方式的Oracle数据库中。

如果出现这个等待事件，首先应该查看Oracle数据库的告警日志文件，看是否因为写归档日志文件错误导致归档进程停止，其次，可以增加归档进程的数量或者将归档日志文件存放到I/O速度比较快的磁盘上，还可以通过增大和增加重做日志文件的大小和数量来给予归档更多的时间。

高速缓存区相关的I/O等待事件：

Ø db file parallel write等待事件

这个等待事件是指Oracle后台进程DBWR等待一个并行写入文件或者是BLOCK的完成，等待会一直持续到这个并行写入操作完成。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表Oracle正在写入的数据文件的数量，P2代表操作将会写入多少的BLOCK数量，P3在Oracle9i release2版本之前代表总共有多少BLOCK的I/O请求，等于P2的值；在Oracle9i release2版本之后则代表等待I/O完成的超时的时间，单位是百分之一秒。

这个等待事件即使在总的等待时间中占的比例比较大也不会对用户的会话有很大的影响，只有当用户的会话显示存在大量的等待时间消耗在"write complete waits" 或者是"free buffer waits"上的时候才会影响到用户的会话，较明显的影响是这个写操作的等待会影响到读取同一个磁盘上数据的用户会话的I/O。

Ø db file single write等待事件

这个等待事件通常是表明在等待写入数据到数据文件头。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表Oracle正在写入的数据文件的文件号：

SELECT * FROM v$datafile WHERE file# = <file#>;

P2代表Oracle正在写入的BLOCK号，如果BLOCK号不是1，则可以通过如下查询查出Oracle正在写入的对象是什么：

SELECT segment_name , segment_type ,

owner , tablespace_name

FROM sys.dba_extents

WHERE file_id = <file#>

AND <block#>

BETWEEN block_id AND block_id + blocks -1；

P3代表Oracle写入file＃的数据文件中从BLOCK#开始写入的BLOCK的数量。

Oracle数据文件的文件头一般来说都是BLOCK1，操作系统指定的文件头是BLOCK0，如果BLOCK号大于1，则表明Oracle正在写入的是一个对象而不是文件头。

Ø write complete waits等待事件

这个等待事件表明Oracle的会话在等待写入缓存，一般都是缓存的正常老化或者是实例之间的互相调用引起的。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表Oracle正在写入的数据文件的文件号，P2代表Oracle正在写入的BLOCK号，可以通过如下查询查出Oracle正在写入的对象是什么：

SELECT segment_name , segment_type ,owner , tablespace_name

FROM sys.dba_extents

WHERE file_id = <file#>

AND <block#> BETWEEN block_id AND block_id + blocks -1；

P3代表产生这个等待事件原因的id号，具体的id号所代表的原因如下：

1022 无

1027 在写入过程中的buffer，最多等待1秒后会重新扫描cache

1029 试图应用改变到正在写入中的BLOCK，会一直等到BLOCK可用，每次等待的时间都是1秒

1030 无

1031 无

1033 无

1034 实例间的交叉写：一个实例企图去修改一个BLOCK，而另外一个实例想去获得此BLOCK的状态，这个实例产生最多一秒钟的等待

1035 与1034一样，但是产生的原因是由于数据库出于热备的状态下

当Oracle的后台进程DBWR获取可以写入的缓存并标记这些缓存为正在写入的状态，接着这些被收集的缓存中的数据将会被写入磁盘上的数据文件中，当所有的I/O完成后将清除在原来那些被标记的缓存上的标记，这个等待事件出现意味着Oracle想获取的buffer已经被标记为正在写入的状态，只有等标记被清除才能获取到相应的buffer。在Oracle7.2以前的版本中，只有当批处理中所有的buffer都被写入磁盘后标记才被清除，在这之后的版本，每个buffer写入磁盘后就将清除在这个buffer上的标记了。

增加更多的Oracle后台DBWR进程或者是采用异步I/O都将能减少这个等待事件的产生。

Ø free buffer waits等待事件

这个等待事件出现的原因比较多，大致可以分为以下几种：

当一个数据文件从只读状态变成为可读写状态的时候，所有的buffer gets全部都被挂起了，就可能出现这个等待事件。已经存在的所有buffer都必须要失效，因为它们没有链接到lock elements(OPS/RAC环境下时需要)。因此，只有当这些buffers失效完成后才能够被分配给数据库块地址。

当Oracle数据库需要从系统全局区(SGA)中读取一个buffer给一致性读(CR)操作，只读操作或者是用于任何恢复模式中的操作的时候，也可能出现这个等待事件，此时可以加速Oracle后台的DBWR进程来获得较多的空闲buffer。

在检查了'free buffers inspected'之后也会出现这个等待事件，如果没有找到空闲的buffer，Oracle会等待1秒钟后继续试图去获取空闲的buffer。

在V$SESSION_WAIT这个视图里面，这个等待事件有三个参数P1、P2、P3，其中P1代表Oracle读取buffer而引起等待的数据文件的文件号，P2代表数据文件中读取buffer的BLOCK的号，P3代表要读取buffer的缓存中的BLOCK(7.3.X以上的版本)。

一般来说，这个等待事件都是由于Oracle的后台进程DBWR不能及时的将buffer写完到磁盘上的数据文件中而引起的，尽量将I/O平均分配到各个磁盘上，减少出现某个磁盘上I/O负载很高而引起DBWR进程写入慢的情况，可以通过操作系统上的I/O监控工具或者查询V$FILESTAT视图来获取相应的数据：

SELECT name, phyrds, phywrts

FROM v$filestat a, v$datafile b

WHERE a.file# = b.file#;

还可以通过查看数据文件上是否存在全表扫描来判断：

SELECT name, phyrds, phyblkrd, phywrts

FROM v$filestat a, v$datafile b

WHERE a.file# = b.file#

and phyrds!= phyblkrd;

需要注意在应用中要避免漏建立了索引，这样会引起I/O大幅度的增加，导致不必要的磁盘扫描，如果有多块硬盘来存储Oracle的数据文件，尽量使用操作系统的条带化软件来分布Oracle的数据文件使得I/O分配均匀。此外，大量的磁盘排序会导致存在很多的脏缓存需要写完，因此，临时表空间中的数据文件最好能分配到不同的磁盘上，避免同一个磁盘上的I/O竞争。还有如果排序的BLOCK的检查点没有完成，将会存在于正常的缓存写批处理中，如果缓存写批处理中全部都被排序块给占满了，那其他的脏数据块就没法被写入导致前台的应用不得不等待分配空闲的buffer。对于Oracle9i之后的版本，因为排序使用的块通常都是来自临时表空间文件，不会进入到缓存中，因此，由于大量排序引起的这种等待在9i中基本上就不会存在了。

了解了在Oracle数据库I/O性能或者是响应时间低下的时候该如何去调整和优化数据库，还有一点很重要的需要提及的是，无论是何种情况，都应该先去检查操作系统上的日志文件，因为如果是本身在操作系统级别上出现了I/O问题，那不管如何调整Oracle数据库都是徒劳的，所以必须首先要保证在操作系统级别上I/O不存在问题，然后再去Oracle数据库中具体的检查问题产生的原因。

四、结论

不管用何种方法去解决Oracle数据库的I/O性能问题，关键都是先找出产生I/O性能问题的根本最终原因，然后想各种各样的办法去解决产生的原因就可以达到优化数据库的目的了。以上所谈到的都是关于Oracle数据库I/O调整优化的一些基本概念和方法，希望能起到一个抛砖引玉的作用，以便能够更好的深入理解Oracle数据库I/O性能方面的知识。

本文作者：叶梁（Coolyl）

历史上的今天...
>> 2019-11-25文章:

GS-00001 - GaussDB 100 OLTP第一号错误的诊断和解决

GaussDB OLTP 100 华为数据库的创建脚本和模板

>> 2014-11-25文章:

2014 Oracle嘉年华：SQL审核的源头管控

>> 2010-11-25文章:

故障分析:ORA-00904:invalid column name

>> 2005-11-25文章:

Oracle HowTo:如何在Linux上扩展SGA超过1.7G

By eygle on 2011-11-25 09:24 | Comments (0) | FAQ | 2909 |