原文网址：
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相关的竞争等待：
Ø
Ø
Ø
数据库如果发现存在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
Top5WaitEvents
db file parallel write
上面是一个大约30分钟的statpack收集的信息的Top 5 Wait Events部分，如果基于上面给出的列表，我们很容易发现direct path read的wait很高，并且会试图去调整这个等待事件，但是这样做就没有考虑到Service Time。我们来看看在这个statpack中关于Service Time的统计：
Statistic
让我们来大致的计算一下响应时间：
'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
direct path read
db file scattered read
latch free
log file sync
db file parallel write
从上面的计算中我们可以明显的看出来，I/O相关的等待事件所消耗的时间在整个响应时间中占的比例并不大，只不过是很小的一部分，而相对来说Service Time所消耗的时间远远大于Wait Time，因此，应该直接调整的是Service Time(CPU的使用率)而不是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、SAN、NAS的技术在多个物理磁盘之间平衡数据库的I/O，尽量避免数据库产生I/O竞争的瓶颈。
三、
数据文件I/O相关的等待事件：
Ø
Ø
Ø
Ø
Ø
Ø
Ø
控制文件I/O相关的等待事件：
Ø
Ø
Ø
重做日志文件I/O相关的等待事件：
Ø
Ø
Ø
Ø
Ø
Ø
Ø
Ø
Ø
Ø
高速缓存区I/O相关的等待事件：
Ø
Ø
Ø
Ø
下面来对这些I/O相关的等待事件进行具体的说明和相应的处理方法。
数据文件相关的I/O等待事件：
Ø
这个是非常常见的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。
Ø
这个也是一个非常常见的等待事件。当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缓存池中等方法调整这个等待事件。
Ø
扫一扫
772
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
