Oracle等待事件-db file scattered read

上一篇说了下 Oracle等待事件-db file sequential read-CSDN博客 ，这一篇说一下它的”孪生兄弟”   另外一个IO 等待事件 db file scattered read(数据库文件分散读)

如果 Oracle 在 SGA 的缓冲区缓存中没有用户请求的数据，则服务器进程会将相应的数据块从数据文件加载到缓冲区缓存中。 这称为常规路径 I/O。 传统的路径I/O可分为多块读取和单块读取。 多块读取方法是一次读取多个连续块的 I/O，而单块读取方法是一次只读取一个块的 I/O。

db file scattered read(数据库文件分散读取) 这个等待是说：进程正在等待从磁盘读取多个连续的数据块到SGA。分散读通常是多块读取这是跟db file sequential read本质不同的地方。在大多数情况下，说明正在进行full table scans(全表扫描)和index fast full scans (FFS)索引快速扫描。这在实际生产情况可能预示着一个低效的SQL正在执行(也可能说明硬件IO性能太差)。

在读取数据块的时候，块被读取到物理上批次不相邻的内存地址，所以取名为分散读取。

SQL语句在进行全表扫描的时候， Oracle进程一次最多读取 DB_FILE_MULTIBLOCK_READ_COUNT个连续的块。然后将读取的这些块(一般是大量的块)分散到SGA缓冲中。

另外一个比较隐藏的情况下也会发生db file scattered read: 就是在索引唯一扫描的时候执行多块读

这有点绕，怎么索引唯一扫描是单块读，也可能会发生多块读。这是有个oracle 隐藏行为再遍历唯一索引的时候会遍历整个索引树。优化器将倾向于索引预取。由参数_index_prefetch_factor 和_db_file_noncontig_mblock_read_count 控制。这种情况一般不会是问题的主要原因。

总结该等待一般发生在
1.全表扫描( full table scans )
2.索引快速全扫描( index fast full scans)

问题诊断：

1.直接查询视图，查看此刻实时的等待情况

SELECT INST_ID, EVENT, COUNT(*)
  FROM GV$SESSION
 WHERE WAIT_CLASS# <> 6
 GROUP BY INST_ID, EVENT
 ORDER BY 1, 3;

或者 实时查看当前那个会话等待过长

select * from v$session_Wait where event = 'db file scattered read' 

P1代表File ID，可通过dba_data_File视图的FILE_ID字段看出是哪个数据文件
P2代表 First block，即该块在数据库上开始的位置
P3代表块数，该值的取值范围为1-DB_FILE_MULTIBLOCK_READ_COUNT的值

查看对应的段：

SELECT segment_name, segment_type
  FROM dba_extents
 WHERE file_id = ''
   AND xx BETWEEN block_id AND (block_id + blocks - 1);

或者直接查询 全表扫描 和索引快速全扫描 的语句进行优化

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;

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;

2.AWR，ASH报告 查看sql ordered by Reads 等找到对应sqlid

awr里面查看高物理读的数据文件Tablespace IO Stats 和File IO Stats 区域来定位最多IO操作的表空间和数据文件

3.单个sql分析，可以使用10046 ，查看更多的细节 P3=128 说明每次读取128个数据块

WAIT #1: nam='db file scattered read' ela= 17628 p1=6 p2=56873 p3=128
WAIT #1: nam='db file scattered read' ela= 29881 p1=6 p2=57001 p3=128
WAIT #1: nam='db file scattered read' ela= 33220 p1=6 p2=57129 p3=128
WAIT #1: nam='db file scattered read' ela= 33986 p1=6 p2=57257 p3=96
WAIT #1: nam='db file scattered read' ela= 46372 p1=6 p2=65577 p3=128
WAIT #1: nam='db file scattered read' ela= 33770 p1=6 p2=65705 p3=128

问题解决：

实际问题还是底层I/O太慢(比如大于 20 毫秒)，而不是等待时间过长。通常不喜欢这个等待事件，因为大量的db file scattered read意味着大量的IO，可能全表扫描发生了。

解决办法跟上篇Oracle等待事件-db file sequential read-CSDN博客的处理有很多共同的地方：

1.优化全表扫码的SQL，这个在99%的OLTP系统下面是必须干掉的。目标是减少物理读和逻辑读

2.索引快速扫描是不是高效的。执行计划中 HASH JOIN和SORT MERGE动作(operation)会导致scattered read

3.分区技术在大部分场景都能减少IO

4.大多数 OLTP 系统通常使用默认块大小(8 KB) 但是对于需要频繁扫描大量数据的DSS系统，使用较大的块可以提高性能

5.合理的配置参数设置，DB_FILE_MULTIBLOCK_READ_COUNT(影响全表扫描一次IO的数量，增大这个参数相当于减少IO次数，但也可能导致CBO 更加倾向走全表扫描)和 optimizer_index_cost_adj(CBO是倾向全表扫描还是走索引)

后续准备把oracle 常见的等待事件都总结一遍，欢迎关注下面公众号同步更新