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

原创于 2025-08-05 21:27:38 发布 · 727 阅读

25 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述
好的，我们来详细剖析一下 Oracle 数据库中的 db file scattered read 等待事件。这是一个非常常见且重要的 I/O 相关等待事件，理解它对于数据库性能调优至关重要。

1. 定义与本质

官方定义： 当会话发出一个 I/O 请求，要求数据库从磁盘读取多个（通常是连续的，但物理上可能不连续）数据块（db block）到一个非连续的内存区域（Buffer Cache 中的分散缓冲区）时发生。这个请求是异步的，会话会等待这个 I/O 操作完成。
核心含义： 它代表了数据库执行多块读取操作时产生的物理 I/O 等待时间。
关键特征：
- 多块读取： 一次 I/O 请求读取多个块（由 DB_FILE_MULTIBLOCK_READ_COUNT 参数控制）。
- 分散缓冲： 读取的数据块在 Buffer Cache 中不一定物理连续（这与 db file sequential read 不同）。
- 物理 I/O： 表示数据不在内存中，需要从磁盘读取。
- 异步 I/O： 会话发出请求后进入等待状态，直到 I/O 子系统完成操作。

2. 产生的过程 (生命周期)

SQL 解析与执行计划： 优化器为 SQL 语句生成执行计划。
选择全扫描： 执行计划决定使用全表扫描或索引快速全扫描来访问数据。这是触发 db file scattered read 的主要操作。
检查 Buffer Cache： Oracle 进程（如 Server Process）首先在 Buffer Cache 中查找所需的数据块。
缓存未命中 (Cache Miss)： 如果所需的一个或多个数据块（构成一个多块读请求）都不在 Buffer Cache 中（或者部分不在，但 Oracle 通常会按多块读请求大小去读），则发生缓存未命中。
发出 I/O 请求： Server Process 向操作系统发出一个异步 I/O 请求，要求读取 DB_FILE_MULTIBLOCK_READ_COUNT 指定数量的数据块（通常是连续的 extent 或一组块）。
进入等待状态： Server Process 将自身状态标记为 WAITING，并记录等待事件为 db file scattered read。它挂起当前操作，释放 CPU。
I/O 子系统工作： 操作系统 I/O 层（可能涉及存储控制器、磁盘驱动器、HBA 卡等）定位并读取请求的数据块。这些块在磁盘上可能是物理连续的（理想情况），也可能是不连续的（碎片化表）。
数据加载到 Buffer Cache： 读取到的数据块被放入 Buffer Cache 的空闲缓冲区或可覆盖的缓冲区中。这些缓冲区在内存中物理地址不一定是连续的（这就是“scattered”的来源）。
I/O 完成通知： I/O 子系统通知 Oracle 进程 I/O 操作已完成。
恢复执行： Server Process 被唤醒，状态变为 RUNNABLE，等待获取 CPU。获得 CPU 后，它继续处理这些现在已在内存中的数据块，执行后续操作（如过滤、连接、返回数据等）。
等待结束： db file scattered read 等待事件记录结束，记录此次等待的时长（WAIT_TIME）、等待的文件号（P1）、起始块号（P2）、读取的块数（P3）等信息。

3. 哪些场景会触发？

全表扫描： 这是最常见的原因。当查询需要访问表中大部分数据、缺少合适的索引、或优化器认为全表扫描代价更低时发生。
- 报表查询、数据仓库 ETL 过程、SELECT COUNT(*)（无索引时）、CREATE TABLE AS SELECT。
索引快速全扫描： 当查询只需要索引中的列，且优化器认为读取整个索引比按索引范围扫描更有效时（例如 SELECT indexed_column FROM table）。
分区操作： 对分区表进行全分区扫描。
LOB 访问： 读取存储在 LOB 段中的大对象数据（尤其是 SECUREFILE 的预读）。
排序操作溢出： 当排序内存不足 (PGA_AGGREGATE_TARGET, SORT_AREA_SIZE) 时，排序中间结果会写入临时表空间磁盘段，后续读取这些临时段也可能触发多块读（虽然临时文件 I/O 通常有单独事件如 direct path read temp，但机制类似）。
并行查询： 并行执行服务器 (PX Slaves) 在执行全表扫描或索引快速全扫描部分时。
数据泵导入： 在传统路径导入时读取转储文件。
备份恢复操作： RMAN 备份读取数据文件时。

4. 可能的原因 (为什么会出现高等待？)

db file scattered read 本身是正常操作，但过高或持续很长时间的等待通常表示潜在的性能问题。原因可分为几类：

SQL 与执行计划问题：
- 不必要的大规模全表扫描： 这是最普遍的原因。SQL 语句缺少有效的过滤条件（WHERE 子句）或连接条件。
- 缺失索引： 应该在频繁过滤或连接的列上创建索引，但索引不存在、不可用或未被优化器选择（可能由于统计信息问题）。
- 低效的索引： 索引选择性差（如性别列索引），导致优化器认为全表扫描更快。
- 陈旧的优化器统计信息： 表和索引的统计信息过时或不准确，导致优化器错误地选择了全表扫描而不是更优的索引扫描或嵌套循环。
- 绑定变量窥探/基数反馈问题： 导致对后续执行选择了错误的计划（如本应索引扫描却用了全表扫描）。
- 高 DB_FILE_MULTIBLOCK_READ_COUNT： 过高的值可能鼓励优化器更倾向于全表扫描（因为单次 I/O 成本看似更低），但如果存储 I/O 带宽不足或延迟高，反而导致更大的单次 I/O 等待时间。过低的值会导致需要更多次 I/O 来完成扫描，虽然单次等待可能变小，但总等待时间可能增加。
数据模型与设计问题：
- 表过大： 扫描非常大的表自然需要更多 I/O。
- 表碎片化严重： 高水位线 (HWM) 之下存在大量空块、行迁移/行链接严重、或数据文件物理碎片化（操作系统/存储层面）。这会导致多块读请求实际读取的物理块在磁盘上不连续，增加寻道时间，显著延长 db file scattered read 等待时间。
内存配置问题：
- Buffer Cache 过小： 无法缓存常用数据，导致频繁的物理 I/O，包括全扫描需要的数据。
I/O 子系统性能瓶颈：
- 磁盘速度慢： 使用低速磁盘（如 SATA HDD）。
- I/O 带宽不足： 存储控制器、HBA 卡、网络（如果是 SAN/NAS）带宽达到瓶颈。
- 高 I/O 延迟： 存储响应时间 (AVG WAIT TIME) 过长（毫秒级）。
- I/O 队列深度过大： 操作系统或存储层 I/O 请求积压。
- 存储控制器缓存策略不当： 如 Write-Back 缓存未启用或太小。
- 热点磁盘/文件： 数据文件物理存放在 I/O 繁忙的磁盘上。
- RAID 级别不当： 如使用 RAID-5/6 存放频繁写入的重负载 OLTP 数据文件（虽然 scattered read 是读，但慢的写也会影响整体 I/O 性能）。
- 文件系统/卷管理开销： 某些文件系统或卷管理器对大型 I/O 处理效率低。
并发与资源竞争：
- 高并发全表扫描： 多个会话同时执行大表全扫描，争抢 I/O 资源。

5. 详细排查过程

排查是一个系统性的工作，需要结合多种工具和视图：

确认问题与范围：
- AWR/ASH 报告： 这是首要工具。查看 Top 5 Timed Foreground Events。确认 db file scattered read 是否在 TOP 且 Total Wait Time (sec) 和 % DB Time 显著。查看 Load Profile 看物理读 (Physical read) 是否高。查看 Instance Activity Stats 中的 physical reads / physical reads direct / physical reads cache (后者对应 Buffer Cache 物理读，是 scattered read 和 sequential read 的来源)。
- 实时监控：
  - SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO, AVERAGE_WAIT_MICRO FROM V$SYSTEM_EVENT WHERE EVENT LIKE 'db file scattered read'; (系统级)
  - SELECT SID, SERIAL#, EVENT, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, STATE FROM V$SESSION WHERE EVENT = 'db file scattered read'; (会话级，找出正在等待的会话及其等待的文件、块号、块数)。
  - SELECT * FROM V$SESSION_LONGOPS WHERE TIME_REMAINING > 0; (查找长时间运行的操作，如全表扫描)。
  - 使用 top, iostat, vmstat, sar 等操作系统命令监控整体 CPU、I/O 负载和磁盘响应时间 (await, svctm)。

定位相关 SQL 和对象：

ASH 报告 (DBA_HIST_ACTIVE_SESS_HISTORY / V$ACTIVE_SESSION_HISTORY)： 这是最强大的实时和历史诊断工具。查询等待事件为 db file scattered read 的会话，关联 SQL_ID, SQL_PLAN_HASH_VALUE。

SELECT ASH.SQL_ID, ASH.SQL_PLAN_HASH_VALUE, ASH.CURRENT_OBJ#,
       O.OBJECT_NAME, O.OBJECT_TYPE, SUM(ASH.TM_DELTA_DB_TIME) DB_TIME,
       COUNT(*) WAIT_COUNT
FROM V$ACTIVE_SESSION_HISTORY ASH
JOIN DBA_OBJECTS O ON (ASH.CURRENT_OBJ# = O.OBJECT_ID)
WHERE ASH.EVENT = 'db file scattered read'
  AND ASH.SAMPLE_TIME > SYSDATE - 15/1440 -- 最近15分钟
GROUP BY ASH.SQL_ID, ASH.SQL_PLAN_HASH_VALUE, ASH.CURRENT_OBJ#,
         O.OBJECT_NAME, O.OBJECT_TYPE
ORDER BY DB_TIME DESC, WAIT_COUNT DESC;

AWR 报告 SQL 部分： 查看 SQL ordered by Elapsed Time, SQL ordered by CPU Time, SQL ordered by Gets, SQL ordered by Reads。高 Elapsed Time、高 CPU Time、极高 Buffer Gets、极高 Disk Reads 且执行计划包含 TABLE ACCESS FULL 或 INDEX FAST FULL SCAN 的 SQL 是主要嫌疑人。
SQL Trace / TKPROF： 对可疑会话进行 SQL_TRACE (ALTER SESSION SET SQL_TRACE=TRUE;) 或使用 DBMS_MONITOR，然后用 TKPROF 分析跟踪文件。查看 EXECUTION PLAN 和 WAITS 部分，确认 db file scattered read 发生在全扫描操作上，并统计等待次数和总时间。

V $SQ L / V$ SQLAREA： 使用从 ASH/AWR 获取的 SQL_ID 查询详细信息：

SELECT SQL_ID, SQL_TEXT, EXECUTIONS, DISK_READS, BUFFER_GETS,
       ROWS_PROCESSED, ELAPSED_TIME, CPU_TIME
FROM V$SQL
WHERE SQL_ID = '&sql_id_from_ash';
-- 计算单次执行平均物理读 (高即问题)
SELECT SQL_ID, DISK_READS / EXECUTIONS AVG_DISK_READS_PER_EXEC
FROM V$SQL WHERE EXECUTIONS > 0 AND SQL_ID = '&sql_id';

分析 SQL 执行计划：
- 使用 EXPLAIN PLAN FOR ... 或 DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ADVANCED') (需要 SELECT_CATALOG_ROLE) 查看 SQL 的实际执行计划。
- 重点： 查找计划中的 TABLE ACCESS FULL 或 INDEX FAST FULL SCAN 操作。
- 检查：
  - 为什么选择全扫描？ 是否缺少索引？过滤条件是否无效？统计信息是否准确？OPTIMIZER_INDEX_COST_ADJ / OPTIMIZER_INDEX_CACHING 设置是否合理？
  - 全扫描的对象有多大？ (DBA_SEGMENTS 查询 BYTES, BLOCKS)。
  - 基数估算 (Rows) 是否严重偏离实际？ (表明统计信息问题)。
检查对象健康状况：
- 表碎片化：
  - 高水位线 (HWM)： 查询 DBA_TABLES.BLOCKS (HWM 下块数) 和 DBA_TABLES.EMPTY_BLOCKS (HWM 下空块数)。BLOCKS 远大于实际数据量（可通过 ANALYZE TABLE ... COMPUTE STATISTICS 后查 NUM_ROWS, AVG_ROW_LEN 估算 NUM_ROWS * AVG_ROW_LEN / DB_BLOCK_SIZE）表明碎片。
  - 行迁移/链接： ANALYZE TABLE ... LIST CHAINED ROWS; 然后查 CHAINED_ROWS 表。严重的行迁移/链接会增加 I/O。
- 索引： 确认是否存在缺失的、应该使用的索引 (DBA_INDEXES, DBA_IND_COLUMNS)。检查现有索引是否失效、不可用或需要重建。
检查统计信息：
- 查询 DBA_TAB_STATISTICS 和 DBA_IND_STATISTICS 查看 LAST_ANALYZED 时间。长时间未收集或收集方法不当（如过低采样率 ESTIMATE_PERCENT）会导致统计信息不准。
- 使用 DBMS_STATS 报告检查统计信息健康状况 (DBMS_STATS.REPORT_COL_USAGE, DBMS_STATS.REPORT_GATHER_*_STATS)。
评估 I/O 子系统性能：
- AWR 报告 I/O 部分： 查看 Tablespace IO Stats / File IO Stats。关注 Av Rd(ms) (平均读等待时间)。理想值通常在 5-20ms 以下（SSD）或 10-20ms（高速 SAN），超过 20ms（尤其 HDD 超过 50ms）通常表示 I/O 瓶颈。 查看 Reads 和 Writes 总量。
- 操作系统工具： iostat -x 5 监控关键磁盘的 %util (使用率，接近 100% 饱和)、await (平均 I/O 等待时间，包括队列和服务时间)、svctm (平均服务时间，应接近磁盘物理延迟，如 HDD ~5-15ms, SSD ~0.1-2ms)。avgqu-sz (平均队列长度) 持续大于 1 也可能表示瓶颈。
- 存储设备监控： 利用存储设备自带的管理工具查看 LUN/卷的吞吐量 (MB/s, IOPS)、延迟 (ms)、缓存命中率、队列深度等关键指标。
检查内存配置：
- Buffer Cache 命中率： AWR 报告的 Instance Efficiency Percentages -> Buffer Nowait % 和 Buffer Hit %。虽然命中率不是绝对指标，但极低（如 <90%）可能表明 Buffer Cache 太小。更可靠的是查看 V$DB_CACHE_ADVICE 预测调整 Buffer Cache 的效果。
- PGA 配置： AWR 报告的 PGA Memory Advisory。如果 % Optimal 低，表明 PGA_AGGREGATE_TARGET 可能不足，导致排序等操作溢出到磁盘，间接增加 I/O 压力。

6. 优化建议 (根据排查结果)

优化 SQL 与执行计划：
- 添加合适的索引： 在 WHERE 子句、JOIN 条件、ORDER BY/GROUP BY 列上创建选择性好的索引。考虑组合索引、函数索引等。
- 重写 SQL： 避免 SELECT *，只取所需列；优化连接条件；使用 EXISTS 代替 IN 等。
- 使用提示 (Hint)： 谨慎使用 INDEX(), FULL() 等提示强制或避免特定计划（通常作为临时解决方案，优先解决根本原因如统计信息）。
- 分区： 对大表进行分区，使查询只扫描相关分区（分区消除）。
- 物化视图： 为复杂报表创建物化视图预聚合数据。
维护对象：
- 收缩表 (Table Reorganization)： 使用 ALTER TABLE ... MOVE 或在线重定义 (DBMS_REDEFINITION) 降低 HWM 并整理碎片。注意：这会重建表并使索引失效！
- 重建索引： ALTER INDEX ... REBUILD 整理索引碎片。
- 修复行迁移/链接： ALTER TABLE ... MOVE 或导出/导入表通常可以修复。
维护统计信息：
- 定期收集： 使用 DBMS_STATS 包设置合理的自动统计信息收集任务（考虑表大小、变更频率）。对大表使用足够高的 ESTIMATE_PERCENT（如 AUTO_SAMPLE_SIZE）和 DEGREE（并行度）。
- 锁定关键执行计划的统计信息： 对执行计划稳定的关键 SQL 对象使用 DBMS_STATS.LOCK_TABLE_STATS。
调整内存：
- 增大 Buffer Cache： 增加 DB_CACHE_SIZE（如果使用 ASMM/AMM，则增加 SGA_TARGET/MEMORY_TARGET）。参考 V$DB_CACHE_ADVICE。
- 优化 PGA： 增加 PGA_AGGREGATE_TARGET，参考 V$PGA_TARGET_ADVICE 和 AWR PGA 建议。
优化 I/O 子系统：
- 升级存储： 使用 SSD 替代 HDD。
- 增加带宽： 添加更多磁盘（使用 RAID 10）、升级 HBA 卡/网络。
- 分散 I/O：
  - 将数据文件、重做日志、归档日志、临时表空间、Undo 表空间分散到不同的物理磁盘/控制器上。
  - 使用 ASM 条带化 (STRIPE) 和冗余 (REDUNDANCY)。
  - 考虑使用数据库闪存缓存 (DB_FLASH_CACHE_FILE, DB_FLASH_CACHE_SIZE)（如果硬件支持）。
- 调整存储参数： 确保 RAID 控制器配置正确（如启用 Write-Back 缓存，使用合适 RAID 级别 - OLTP 优先 RAID 10）。
- 文件系统优化： 使用裸设备或支持 Direct I/O 的文件系统（如 XFS, ext4 with mount -o noatime, nobarrier），避免文件系统缓存双重缓冲。
调整数据库参数：
- DB_FILE_MULTIBLOCK_READ_COUNT： 谨慎调整！ 增大它可以减少全扫描需要的 I/O 次数（如果存储带宽足够），但可能鼓励优化器选择更多全扫描。减小它可能迫使优化器选择索引扫描（如果 I/O 延迟高）。一般建议设置为操作系统最大 I/O 大小（通常是 1MB）除以 DB_BLOCK_SIZE（如 8k 块则为 128）。在 Exadata 或全闪存阵列上可以设置得更大。
- OPTIMIZER_INDEX_COST_ADJ / OPTIMIZER_INDEX_CACHING： 极端谨慎！ 仅在明确理解影响且作为临时措施时调整。默认值通常合理。调整它们可以改变优化器对索引访问代价的评估。
- 使用并行查询： 对大型全扫描操作（如报表）使用 PARALLEL 提示或设置表并行度 (DEGREE)，利用多个进程加速扫描（需要足够 CPU 和 I/O 带宽支持）。
- 缓存小表： 使用 ALTER TABLE ... CACHE 将频繁访问的小表固定到 Buffer Cache 的 LRU 热端（KEEP 池），避免物理读。使用 KEEP 池 (DB_KEEP_CACHE_SIZE) 管理这类对象。

重要原则

db file scattered read 不是洪水猛兽： 它是数据库正常工作的必要机制。目标是消除不必要的全表扫描和优化必要的全扫描的性能。
不要只看等待事件本身： 必须结合 SQL_ID、执行计划、对象信息、I/O 性能指标综合分析。
优先优化 SQL 和索引： 这是解决由不必要全扫描引起的高等待最根本、最有效的方法。
存储性能是关键： 对于必要的全扫描，I/O 子系统的吞吐量和延迟是决定性因素。
平衡： 调整参数（如 MULTIBLOCK_READ_COUNT）或使用并行查询时，需考虑对系统整体资源（CPU, I/O）的影响。