db file scattered read，direct path read 等待事件的原因以及解决方法

db file sequential read (single block read into one SGA buffer)

db file scattered read (multiblock read into many discontinuous SGA buffers)

direct read (single or multiblock read into the PGA, bypassing the SGA)

direct path read and direct path read temp
direct path write and direct path write temp

[@more@]

10G

通过 v$session 查询
SELECT a.owner, a.object_name, a.subobject_name, a.object_type,b.sid,b.program
FROM DBA_OBJECTS a, V$SESSION b
WHERE data_object_id = b.row_wait_obj and b.EVENT = 'db file scattered read';

通过 v$session_wait 查询 等待事件db file scattered read 对应的sql
select /*+ ORDERED */ sql_text from v$sqltext a
where a.hash_value = (
select sql_hash_value from v$session b where sid='34'
)
order by piece asc

sql 所执行的 io 太多 (物理io,逻辑 io)

不是最优的sql :
1) 没有使用索引
2）高并行度的表的查询
3）统计信息不对
4）DB_FILE_MULTIBLOCK_READ_COUNT 设置的太大，使sql 倾向于全表扫描

db file sequential read (single block read into one SGA buffer)

db file scattered read (multiblock read into many discontinuous SGA buffers)

direct read (single or multiblock read into the PGA, bypassing the SGA)

direct path read and direct path read temp
direct path write and direct path write temp

以下情况可以发生该等待时间
大量的排序使排序的数据直接写入磁盘，需要的时候又从磁盘中直接读入pga
多并行度扫描表
磁盘io 太慢

解决方法：

优化sql,增大pga (看pga 是自动管理还是手工管理),增大Hash Area Size 大小

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

转载于:http://blog.itpub.net/66233/viewspace-1022965/