oracle i o优化,Oracle优化之I/O解析（四）

4.1、调整DB_FILE_MULTIBLOCK_READ_COUNT

当SQL已经没有优化余地后，问题仍没有解决，我们可以考虑调整这个参数的大小。

DB_FILE_MULTIBLOCK_READ_COUNT*DB_BLOCK_SIZE是一次IO读取的传输量，它不能大于系统的max_io_size大小。

从Oracle 10gR2开始，如果没有设置DB_FILE_MULTIBLOCK_READ_COUNT的大小，Oracle会自动为其调整一个默认值，这个默认值的大小与平台最大IO大小(max_io_size)相关(对大多数平台来说max_io_size是1M)，其大小被设置为)max_io_size/DB_BLOCK_SIZE.

利用v$session_event视图，我们同样可以找到当前系统中发生全表扫描的对象：

select p1 "fileid",p2 "block_id",p3 "block_num"

from v$session_wait

where enent='db file scattered read';

select

segment_name "segment Name",

segment_type "segment type",

block_id   "first block of segment",

block_id+blocks   "last block of segment"

from dba_extents

where &fileid=file_id

and &block_id >=block_id

and &block_id >=block_id+blocks;

4.2、db file parallel read

当从多个数据文件并行读取数据到非联系的内存(PGA、Buffer Cache)缓冲中时，会发生该等待事件。它通常发生在Recovery

操作或者利用缓冲预提取(Buffer Prefetching)从数据文件并行读取数据时。

select p1 "fileid",p2 "block_id",p3 "requests"

from v$session_wait

where event='db file parallel read';

4.3、direct path read  && direct path read(lob)

当直接读取(Deirect Read )数据到PGA(而不是Buffer Cache)中去时，会 direct path read 等待事件。对Log数据的直接读有一个单独的等待事件--direct path read(lob)

当Oracle设置支持异步IO时，进程可以在提交IO请求后继续做其他操作，并且在稍后再提取IO请求返回的结果，在提取结果时就产生了direct path read等待事件。

在没有启用异步IO时，IO请求在完成之前会被堵塞，但在执行IO操作时并不会产生等待事件。进程稍后回来提取哪些已经读取到的IO数据，这时尽管能够很快返回，但仍然会显示direct path read 等待事件。

和其他IO等待事件不同的是，对direct path read等待事件要注意以下两点：

1)等待次数并不等于IO请求次数；

2)统计(如statspack报告中)得出的direct path read 等等待时间并不一定代表该事件引起的真正等待时间。

直接读(direct read)请求一般发生在以下几种情况：

1)磁盘排序IO(sort area不足时，排序用到的临时数据会被写到临时表空间上去，当读取这些数据时就使用直接读)；

2)预读取(当一个进程认为某个数据块将快被用到而发出IO请求时)

3)Hash Join (Hash Area不足)

4)IO负载系统中，服务进程处理缓存的速度比系统IO返回数据到缓存的速度更快时

4.4、磁盘排序

排序一般是由以下操作引起的：

Order by;

JOIN;

UNION:

Group by

聚合操作

select unique

select distinct

创建索引也会引起排序操作

在专业模式(Dedicated下)，排序所占用的内存是从PGA中分配出来的一块区域，叫Sort Area，由参数sort_area_size控制其大小；在MTS中，排序区是从Large Pool中分配的，当sort area大小无法满足排序操作要求时，就会占用临时表空间来存在排序数据，因而产生Direct Path Read等待事件。我们可以通过适当增加该参数来减少磁盘排序操作。

sort_area_size该蚕食一般推荐设置为1~3M，在9i之后，不推荐设置该参数，我们可以通过设置PGA_AGGREGATE_TARGET进程PGA内存自动管理(设置WORKAREA_SIZE_POLICY为TRUE)。

我们可以通过以下语句来查找系统中存在磁盘排序的会话极其语句：

select a.sid,a.value,b.name,d.sql_text from

v$sessata a,v$statname b,b$session c,v$sqlarea d

where a.statistic#=b.statistic#

and b.name='sorts (disk)'

and a.sid=c.sid

and c.sql_address=d.address(+)

and c.sql_hash_value=d.hash_value(+)

and value>0

order by 2 desc,1;

4.6、并行查询

当设置表的并行度非常高时，优化器可能就对表进行全表扫描，这时会引起Direct Path read等待。

在10g中，可以考虑使用ASM。

对于表 的并行度，我们不建议使用ALERT修改表的物理并行度：

alter table t parallel degree 16;

而是推荐对特定语句使用提示来设置表的并行度：

select /*+FULL(t) parallel(t,4)*/ object_name from t;

4.7、Hash Join

hash area 是用于hash join的内存区域。hash area过小会引起 direct path read等待。

当WORKAREA_SIZE_POLICY为FALSE时，可以考虑增加hash_area_size的大小(建议为sort_area_size大小的1.5倍)；当WORKAREA_SIZE_POLICY为TRUE时，可以考虑增加PGA_AGGREGATE_TARGET大小。

4.8、Direct Path read(lob)

为了减少LOB的读写事件，通常我们会设置LOB的存储参数NOCACHE，这时读取LOB时会引起direct path read(lob)等待事件。但当我们发现direct path read(lob)引起IO性能问题，就需要考虑将那些被经常读取的LOB字段设置为CACHE。

另外，如果操作系统的文件系统有足够的Buffer Cache时可以考虑将LOB数据段存储在文件系统上。

4.8、其他优化措施

当内存资源不足、IO读取数据到内存效率远远低于内存中数据被处理的效率时，会引起Direct Path Read等待事件。

作为对上述处理措施的补充，增加内存(PGA)、在确保操作系统支持AIO情况下设置DISK_ASYNCH_IO为TRUE一支持异步IO、采用效率更高的存储设备都能帮助我们减少direct path read等待。

4、9、direct path write && direct path write (lob)

直接写(Direct Path Write)允许一个会话先将IO写请求放入一个队列中，让操作系统去处理IO，而自身可以继续处理其他操作。

当会话需要知道写操作是否完成(如会话需要一块空闲的缓存块或者会话需要确认内存中所有操作都被flush到磁盘了)，会话就会等待写操作完成从而产生direct path write等待事件。direct path write(lob)是在对LOB数据段(NOCACHE)直接写时产生的等待事件。

直接写请求一般发生在以下几种情况：

1)直接数据载入操作(如CTAS、SQL*Loader设置Direct选项等)；

2)并行DML操作；

3)磁盘排序(排序内存空间不足，数据写入磁盘)；

4)载入NOCACHE数据段；

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29677883/viewspace-1170212/，如需转载，请注明出处，否则将追究法律责任。