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

在Oracle中如何调整I/O相关的等待 原文网址：http://www.eygle.com/archives/2011/11/oracle_io_tuning.html 本文主要介绍的是在出现了I/O 竞争等待的时候如何去优化Oracle数据库。对Oracle数据库进行调整优化，基本上最终都可以归结到I/O调整上，因此，了解

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

原文网址：http://www.eygle.com/archives/2011/11/oracle_io_tuning.html

本文主要介绍的是在出现了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'来衡量，这也就意味着可以更加精确的判断在响应时间中的等待事件的影响，从而调整最需要优化的部分。

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

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

db file parallelwrite 40 69 .38

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

Statistic Total perSecond per Trans ------------------------------------------------- ------------ ------------ CPU used by thissession 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

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

CPUtime =95.97%

direct pathread = 3.47%

db file scatteredread = 0.28%

latchfree = 0.23%

log filesync = 0.03%

db file parallelwrite = 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吞吐能力(例如：操作系统的一次能够传输的最大I/O大小是8M)都会设置的一次读取多个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来说, 大部分数据传输只对一块磁盘操作, 可进行并行操作.在RAID5中有"写损失", 即每一次写操作,将产生四个实际的读/写操作, 其中两次读旧的数据及奇偶信息, 两次写新的数据及奇偶信息。

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

SAN(Storage Area Network，存储局域网)是独立于服务器网络系统之外几乎拥有无限存储能力的高速存储网络，这种网络采用高速的光纤通道作为传输媒体，以FC (Fiber Channel， 光通道)+ SCSI(Small ComputerSystem 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 scatteredread

? 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，减少索引的碎片；如果被使用的索引存在一个很大的ClusteringFactor，那么对于每个索引BLOCK获取相应的记录的时候就要访问更多表的BLOCK，这个时候可以使用特殊的索引列排序来重建表的所有记录，这样可以大大的减少Clustering Factor，例如：一个表有A,B,C,D,E五个列，索引建立在A,C上，这样可以使用如下语句来重建表：

CREATE TABLE TABLE_NAMEAS SELECT * FROM old ORDER BYA,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 fromv$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就会在报告中产生"SegmentStatistics"的信息。

SQL> selectdistinct statistic_name from v$segment_statistics;

STATISTIC_NAME

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

ITL waits

buffer busy waits

db block changes

global cache crblocks served

global cachecurrent blocks served

logical reads

physical reads

physical readsdirect

physical writes

physical writesdirect

row lock waits

11 rows selected.

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

selectobject_name,object_type,statistic_name,value

fromv$segment_statistics

wherestatistic_name='physical reads'

order by valuedesc;

方法三：如果不存在有问题的执行计划导致读取过多的物理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 FullIndex扫描的SQL语句，判断这些扫描是否是必要的，是否导致了比较差的执行计划，如果是，则需要调整这些SQL语句。

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

查找全表扫描的SQL语句可以使用如下语句：

select sql_textfrom v$sqltext t, v$sql_planp

wheret.hash_value=p.hash_value and p.operation='TABLE ACCESS'

andp.options='FULL'

order byp.hash_value, t.piece;

查找Fast Full Index扫描的SQL语句可以使用如下语句：

select sql_textfrom v$sqltext t, v$sql_planp

wheret.hash_value=p.hash_value andp.operation='INDEX'

and p.options='FULLSCAN'

order byp.hash_value, t.piece;

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

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

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

select sql_textfrom (

select * fromv$sqlarea

order bydisk_reads)

whererownum<=10;

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

DB_BLOCK_SIZE xDB_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缓存池中等方法调整这个等待事件。