1.db file sequential read 事件
oracle的官方解释:
rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml">
sequential read reads data into contiguous memory (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA).
The IO is generally issued as a single IO request to the OS - the wait blocks until the IO request completes.
相对于scattered read一次读入多个block, sequential read一次进行单个块的读取,联系到执行计划可以得出 sequential read一般发生在进行索引扫描时(当然有的全表扫描也可能发生),主要从index,rollback(or undo) segments, tables(通过rowid访问表),control files 和data file headers中进行single-block read.,而scattered read一般发生在全表扫描。因此针对db file sequential read等待过高的情况可以从索引的角度进行分析。
“访问数据对象(table,index)总是会产生Physical I/o需求,当出现db file sequential read等待事件时,并不意味着数据库产生系统问题,基至它大量出现都不是一件坏事.真正要引起注意的是像enqueue 和latch free等待事件,它们总是引起系统性能问题的根源.并且它们使single-block(单块读取)变得因难了.”(Thomas.Woo 的读书笔记)
2.可以通过一下脚本发现哪个session正在进行db file sequential read事件
rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml">
SELECT sid, total_waits, time_waited
FROM v$session_event
WHERE event='db file sequential read'
and total_waits>0
ORDER BY 3,2
同时可以查看:
通过视图:V$SQL查找高的DISK_READS的sql语句
通过视图V$SESSTAT查找高的"physical reads"。
3.解决方法:
(1) 增大DB_BLOCK_BUFFERS的值是常用的方法之一。
(2) 检查是否使用了不合适的索引扫描的SQL,优化SQL语句。
(3) 对大表进行分区。
(4) 是否有index碎片,考虑重建索引。