面试宝典:Oracle数据库db file scattered read等待事件处理过程

以下是关于 Oracle 数据库 db file scattered read 等待事件的全面解析，涵盖原理、产生过程、典型场景、原因及详细排查方法：

---

一、核心概念

db file scattered read 是一个 I/O 类 等待事件，表示会话正在等待 从磁盘读取多个连续的物理数据块到 Buffer Cache。它是 全表扫描（Full Table Scan, FTS）和索引快速全扫描（Index Fast Full Scan, IFFS） 的典型标志。

关键特征：

• 多块连续读取：一次性读取 db_file_multiblock_read_count 参数定义的连续块（如 128 块）。
• 物理顺序读取：按数据块在磁盘上的物理顺序读取（非逻辑顺序）。
• 分散缓存：读入 Buffer Cache 后，块可能被分散存放（Buffer Cache 空间不连续）。
• 顺序 I/O：适合机械硬盘（减少寻道时间），但对 SSD 同样重要（大吞吐量读取）。

---

二、详细原理与产生过程

1. SQL 执行与逻辑读

   • 会话执行 SQL（如 SELECT * FROM large_table），优化器选择 全表扫描。
   • 服务进程在 Buffer Cache 中未找到所需块（Cache Miss）。

2. 确定读取范围

   • Oracle 根据段的高水位线（HWM）计算需读取的 连续物理块范围（如从块 1000 到 1127）。

3. 发起多块读请求

   • 服务进程向操作系统发起 单次 I/O 请求，要求读取连续的 db_file_multiblock_read_count 个块（如 128 块）。
   • 该请求通过 同步 I/O（或异步 I/O）提交给存储系统。

4. 进入等待状态

   • 服务进程挂起，记录等待事件：
     • P1 = 文件号（File ID）
     • P2 = 起始块号（Starting Block）
     • P3 = 读取块数（Blocks Count）

5. 存储系统响应

   • 存储设备读取连续的物理块（顺序 I/O），传输到 Buffer Cache。

6. 缓存与唤醒

   • 数据块被存入 Buffer Cache（可能分散在不同 Buffer）。
   • 服务进程被唤醒，继续执行后续操作（如处理数据、返回结果）。

graph TD
    A[SQL 执行全表扫描] --> B{块在 Buffer Cache？}
    B -- 是 --> C[逻辑读]
    B -- 否 --> D[发起多块连续读请求]
    D --> E[服务进程进入 db file scattered read 等待]
    E --> F[存储系统读取连续块]
    F --> G[数据存入 Buffer Cache]
    G --> H[服务进程唤醒并处理数据]

---

三、典型场景

1. 全表扫描（FTS）

   • 大表无索引或索引成本过高时触发（如 WHERE 条件非索引列）。
   • 示例：SELECT * FROM sales WHERE discount_rate > 0.1;（discount_rate 无索引）。

2. 索引快速全扫描（IFFS）

   • 当索引本身包含所有查询列时（覆盖索引），且需大量读取索引叶子块。
   • 示例：SELECT product_id FROM orders;（product_id 有索引）。

3. 分区表扫描

   • 全分区扫描（如 SELECT * FROM partitioned_table）。

4. 统计信息收集

   • DBMS_STATS 收集表统计信息时触发全表扫描。

---

四、可能的原因

1. SQL 与设计问题（主要根源）

• 低效 SQL：全表扫描大表（未用索引、索引失效、隐式转换）。
• 缺失索引：关键查询字段无索引。
• 统计信息过时：优化器低估行数，错误选择 FTS。
• 高水位线（HWM）问题：表大量删除后，HWM 未重置，扫描空块。

2. 数据库配置问题

• db_file_multiblock_read_count 设置不当：
  • 值过小（如 8）：增加 I/O 次数，延迟升高。
  • 值过大（如 128+）：超出存储 I/O 处理能力，延迟反而上升。
• Buffer Cache 不足：物理读频繁，加剧 I/O 压力。
• I/O 子系统配置：
  • 未启用异步 I/O（filesystemio_options ≠ SETALL）。
  • 数据文件未条带化（热点文件集中）。

3. 存储性能问题

• 高 I/O 延迟（机械盘寻道慢、SSD 队列饱和）。
• 吞吐量瓶颈（带宽不足、RAID 配置不当）。
• 存储控制器/网络过载（HBA 卡、SAN 交换机瓶颈）。

---

五、详细排查流程

步骤 1：确认问题存在

• AWR/ASH 报告：
  • 检查 Top 5 Timed Foreground Events，关注 Avg Wait (ms) 和 % DB Time。
  • 查看 Segments by Physical Reads 定位热点表/索引。
• 实时监控：

  -- 当前等待会话
  SELECT sid, serial#, sql_id, event, p1, p2, p3 
  FROM v$session 
  WHERE event = 'db file scattered read';
  
  -- 历史等待分析（ASH）
  SELECT sql_id, COUNT(*) AS waits, SUM(time_waited)/1000 AS time_ms
  FROM v$active_session_history 
  WHERE event = 'db file scattered read'
  GROUP BY sql_id ORDER BY time_ms DESC;
  

步骤 2：定位问题 SQL 与对象

• 通过 SQL_ID 找 SQL：

  SELECT sql_fulltext FROM v$sql WHERE sql_id = '&sql_id';
  

• 关联对象：

  -- 根据 P1（文件号）、P2（块号）定位对象
  SELECT owner, segment_name, segment_type
  FROM dba_extents 
  WHERE file_id = &P1 
    AND &P2 BETWEEN block_id AND block_id + blocks - 1;
  

步骤 3：分析执行计划

EXPLAIN PLAN FOR [问题SQL];
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 或从 AWR 获取历史计划
SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

关键点：

• 是否存在 TABLE ACCESS FULL 或 INDEX FAST FULL SCAN。
• 检查 Rows 估算 vs 实际（统计信息是否准确）。
• 观察 Buffers（逻辑读）和 Reads（物理读）。

步骤 4：检查 I/O 性能

• 数据库层面：

  -- 文件级 I/O 延迟
  SELECT file_id, file_name, 
         ROUND((readtim / DECODE(phyrds,0,1,phyrds)) * 10, 2) AS avg_read_ms
  FROM v$filestat fs JOIN dba_data_files df ON fs.file# = df.file_id;
  

  健康指标：

  • SSD：< 5ms
  • 机械盘：< 20ms

• 操作系统层面（Linux 示例）：

  iostat -dxm 2  # 关注 %util, await, svctm
  

  • %util > 70% → 存储饱和
  • await > svctm → I/O 队列堆积

步骤 5：检查配置与对象状态

• 关键参数：

  SHOW PARAMETER db_file_multiblock_read_count;  -- 推荐 32~128（根据存储调整）
  SHOW PARAMETER filesystemio_options;           -- 应为 SETALL 或 ASYNCH
  

• 表/索引碎片：

  -- 检查高水位线
  SELECT table_name, blocks, empty_blocks, num_rows 
  FROM dba_tables WHERE table_name = '&TABLE';
  
  -- 碎片率计算（需定期收集统计信息）
  SELECT (blocks - empty_blocks - 1) / GREATEST(blocks, 1) AS frag_ratio 
  FROM dba_tables WHERE table_name = '&TABLE';
  

  碎片率 > 30% → 需重组表：

  ALTER TABLE &TABLE MOVE;  -- 重建表
  ALTER INDEX &INDEX REBUILD; -- 重建索引
  

---

六、优化策略

1. SQL 优化

• 避免全表扫描：
  • 添加索引（尤其 WHERE/JOIN 列）。
  • 使用分区裁剪（Partition Pruning）。
  • 优化 SQL（避免 SELECT *、使用绑定变量）。
• 强制索引（谨慎使用）：

  SELECT /*+ INDEX(t idx_name) */ col1 FROM table t WHERE ...;
  

2. 存储与配置优化

• 调整 db_file_multiblock_read_count：

  ALTER SYSTEM SET db_file_multiblock_read_count = 64;  -- 根据存储测试调整
  

• 启用异步 I/O：

  ALTER SYSTEM SET filesystemio_options = SETALL SCOPE=SPFILE;
  

• 数据文件条带化：使用 ASM 或存储 LUN 条带化分散 I/O。

3. 对象管理

• 重置高水位线：

  ALTER TABLE &TABLE MOVE;  -- 重建表
  EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE');  -- 立即收集统计信息
  

• 定期维护：
  • 重建碎片化索引。
  • 使用 SHRINK SPACE 压缩段空间。

4. 硬件升级

• SSD 替换机械盘：显著降低随机 I/O 延迟。
• 增加存储带宽：升级 HBA 卡、SAN 网络。

---

总结

db file scattered read 本质是 Oracle 为提升全表扫描效率设计的机制，但当其成为系统瓶颈时，需分三步处理：

1. 定位根源：通过 AWR/ASH 找到高频触发 FTS/IFFS 的 SQL 和对象。
2. 区分原因：
   • Avg Wait (ms) 高 → 存储性能问题
   • Total Wait Time 高但 Avg Wait 低 → SQL/设计问题
3. 精准优化：
   • SQL 优化 > 对象重整 > 参数调整 > 存储升级

通过此流程，可系统性地消除由全表扫描引发的 I/O 性能瓶颈。

欢迎关注我的公众号《IT小Chen》