Oracle数据库面试宝典之db file parallel read等待事件处理过程_Oracle

好的，我们来详细剖析一下 oracle 数据库中的 db file parallel read 等待事件。

核心概念：

db file parallel read 是一个 i/o 类 等待事件。它表示一个会话（通常是前台用户进程或服务进程）正在等待一次由数据库自身发起的、并行化的、读取多个非连续数据块（通常来自不同数据文件）的物理 i/o 操作完成。

关键点理解：

并行化 (parallel)： 这是核心。oracle 不是逐个请求这些离散的数据块，而是一次性提交多个 i/o 请求给操作系统（o/s）或存储系统，让它们尽可能并发地去读取这些块。这类似于你同时派多个人去图书馆的不同书架拿书，而不是一个人来回跑多次。
非连续 (non-contiguous)： 请求的数据块在磁盘上的物理位置不是连续的（不像 db file scattered read 那样读取连续范围的多块）。它们可能散落在同一个数据文件的不同区域，或者更常见的是分布在多个不同的数据文件中。
数据库发起 (database initiated)： 这个 i/o 是由 oracle 数据库进程（如服务进程 server process）直接发起的，而不是由后台进程（如 dbwn）发起的。
等待主体： 前台进程（服务进程）在发出这些并行的 i/o 请求后，会进入等待状态 (db file parallel read)，直到所有请求的数据块都被读取到 buffer cache 中（或直到超时）。
与 parallel query/dml 的区别： 不要与并行查询（parallel query/dml）混淆。db file parallel read 描述的是单个进程内部如何执行一次物理 i/o 操作（并行提交多个离散 i/o 请求）。而并行查询是多个进程（并行执行服务器）协作处理一个 sql 语句。当然，并行查询的进程在执行物理读时，它们自己也可能遇到 db file parallel read 等待。

详细原理与产生过程：

sql 执行与逻辑读： 用户会话执行一条 sql 语句（例如，一个需要访问大量离散数据块的查询，或者涉及多个索引查找的操作）。
buffer cache 检查： 服务进程首先在 sga 的 buffer cache 中查找所需的数据块（逻辑读）。如果找不到（cache miss），就需要进行物理 i/o。
识别所需块： 服务进程确定需要从磁盘读取的多个、物理位置不连续的数据块列表。这些块可能属于：
- 多个不同的索引段（如多个索引分支块或叶子块）。
- 一个表的不同数据块（这些块在磁盘上物理不连续）。
- 数据文件头（file header block）。
- 段头块（segment header block），特别是 assm 位图块。
- 控制文件（虽然控制文件读取通常有其特定事件，但有时也可能归入此类）。
发起并行 i/o 请求： 服务进程不是逐个请求这些块，而是构造一个包含所有需要读取的块地址（文件号+块号）的列表，然后通过一次系统调用（如 linux/unix 上的 preadv() 或 aio 接口）将这个列表提交给操作系统。
进入等待状态： 提交 i/o 请求后，服务进程无法继续工作（因为它需要这些块），于是它将自己挂起，并记录一个 db file parallel read 等待事件。p1 = 要读取的文件数量，p2 = 要读取的总块数，p3 = 这些请求被拆分的次数（通常与 p2 相关）。
操作系统/存储处理： 操作系统接收这个批量 i/o 请求列表。它负责将这些请求调度到底层存储设备（磁盘/ssd/存储阵列）。存储系统会尝试并行处理这些离散的 i/o 请求（性能取决于存储的并发处理能力、队列深度、寻道时间/延迟等）。
i/o 完成与唤醒： 当所有请求的数据块都从磁盘读取完毕并传输到 buffer cache 中后，操作系统通知 oracle 服务进程。
恢复执行： 服务进程被唤醒，从 db file parallel read 等待中恢复，获取到所需的数据块，继续执行 sql 语句（进行逻辑处理、返回结果等）。

典型场景：

全表扫描 (full table scans - fts)： 这是最常见的原因之一。虽然 db file scattered read 更常与 fts 关联（读取连续范围的多块），但当表数据非常分散（高水位线下有很多碎片化的空闲空间，或者表经过大量 dml 后没有重组），或者 buffer cache 只能容纳部分数据块时，oracle 在预取 (prefetching) 后续非连续块时，就可能采用 parallel read 的方式。特别是当优化器认为离散读取更高效时。
索引快速全扫描 (index fast full scan - iffs)： iffs 按物理存储顺序读取索引段的所有叶子块（类似全表扫描索引）。如果索引段在磁盘上物理存储不连续（碎片化），读取这些离散的叶子块就可能导致 db file parallel read。
读取文件头/段头： 数据库需要读取多个数据文件的头部信息（例如在打开数据库、检查点期间），或者需要读取多个段的段头块（特别是 assm 表空间中的位图块）时。
控制文件读取： 某些需要访问控制文件多个块的操作（虽然 control file 等待事件更典型，但有时也可能归入 db file parallel read)。
数据块预取 (prefetching)： 优化器或 oracle 内部机制预测到接下来需要访问一些离散的数据块（不在当前连续范围内），并提前发起并行读取。
涉及多个索引的查询： 执行计划需要访问多个索引（如 index join, and-equal），服务进程需要同时从这些不同的索引段中读取离散的数据块（叶子块或分支块）。
rac 环境中的全局缓存访问： 虽然主要等待是 gc 事件，但当实例需要从磁盘读取块（例如首次访问或强制全库扫描）且该块在本地实例的 buffer cache 中没有时，读取磁盘的过程本身就可能触发 db file parallel read。

可能的原因（导致该等待成为瓶颈）：

存储 i/o 性能不足 (最常见)：
- 高 i/o 延迟： 磁盘响应时间过长（特别是机械磁盘的寻道和旋转延迟）。ssd 延迟低，但如果队列深度不足或过载，延迟也会上升。
- i/o 吞吐量瓶颈： 存储带宽（mb/s）或 iops（每秒 i/o 操作数）达到上限。
- 存储控制器/网络/hba 卡瓶颈： 存储阵列控制器、san 交换机、主机 hba 卡过载或配置不当。
- 存储争用： 同一存储上运行的其他数据库或应用消耗了大量 i/o 资源。
操作系统 i/o 子系统配置问题：
- 文件系统缓存或 i/o 调度器（如 cfq, deadline, noop）配置不当。
- os 级别的 i/o 队列深度 (max_sectors_kb, nr_requests, queue_depth) 设置过低，限制了并行处理能力。
- 异步 i/o (aio) 未启用或配置不当（oracle 推荐使用 aio 来优化并行读）。
数据库配置不当：
- db_file_multiblock_read_count 设置过高： 这个参数控制单次 i/o 请求读取的最大连续块数。如果设置得非常高（远大于存储系统能高效处理的单次 i/o 大小），当 oracle 进行离散读取 (parallel read) 时，它可能会尝试提交非常大的离散 i/o 请求列表，超过存储的最佳处理能力，反而导致延迟增加。需要根据存储特性（如 ssd 条带大小、raid 配置）进行合理设置。
- i/o 分布不均衡： 热点数据文件位于慢速磁盘或 i/o 繁忙的存储路径上。未使用 asm 或文件系统条带化，导致单个文件成为瓶颈。
- filesystemio_options 设置不当： 未启用 setall 或 asynch，限制了 oracle 使用异步和直接 i/o 的能力。
数据库负载/设计问题：
- 低效 sql： 产生大量物理 i/o 的 sql（全表扫描大表、低效索引使用、笛卡尔积等）是根源。它们触发了大量的 db file parallel read 请求。
- 索引碎片化： 导致 iffs 需要读取大量离散的索引块。
- 表碎片化/高水位线问题： 导致 fts 需要读取大量离散的表块。
- 频繁访问 assm 位图块： 在 dml 繁忙的系统上，对 assm 位图块的争用可能导致对这些块的读取成为瓶颈（这些读取常是 parallel read)。
- 检查点过慢： 虽然主要等待是 db file parallel write，但检查点期间也需要读取文件头等信息，可能涉及 parallel read。
主机资源瓶颈 (间接)：
- cpu 过载导致处理 i/o 中断和 oracle 进程唤醒延迟。
- 内存不足导致 buffer cache 命中率低，增加物理 i/o 需求。

详细排查过程：

排查的核心思路是：定位引发大量 db file parallel read 的 sql 和对象，分析 i/o 性能，确定是 sql/设计问题还是存储/配置问题。

确认问题存在：
- awr/ash 报告： 这是最重要的起点。查看 top 5 timed foreground events 或 top wait events 部分，确认 db file parallel read 是否在 top 事件中，并且其 total wait time (s) 和 avg wait (ms) 是否显著高于正常水平。注意 % db time。
- 实时监控： 使用 v$session_wait (当前等待) 或 v$active_session_history (近历史) / dba_hist_active_sess_history (历史 ash) 查看当前或历史会话是否正在经历或经历过长时间的 db file parallel read 等待。
```
-- 当前等待的会话
select sid, serial#, event, p1, p2, p3, seconds_in_wait, state
from v$session
where event = 'db file parallel read';

-- 最近15分钟内经历该等待的会话 (ash)
select sample_time, session_id, session_serial#, sql_id, event, wait_time_micro, current_obj#
from v$active_session_history
where event = 'db file parallel read'
and sample_time > sysdate - 15/1440; -- 15分钟
```
定位引发等待的 sql：
- ash 报告： sql statistics -> sqls with top db time / sqls with top event waits。查找在 db file parallel read 上消耗时间最多的 sql_id。
- awr 报告： sql statistics -> sql ordered by elapsed time / sql ordered by user i/o wait time。结合 elapsed time 和 user i/o wait time 高的 sql。
- 从会话定位 sql： 使用步骤 1 中查询到的 sid 和 serial# 或 sql_id：
```
-- 当前 sql
select sql_id, sql_child_number, sql_exec_id, prev_sql_id
from v$session
where sid = &sid and serial# = &serial#;

-- 获取 sql 文本
select sql_fulltext from v$sql where sql_id = '&sql_id';
```
分析 sql 执行计划：
- 使用 dbms_xplan (select * from table(dbms_xplan.display_cursor('&sql_id', &child_number));) 或 awr 报告中的执行计划。
- 重点关注：
  - 是否存在 全表扫描 (table access full)？扫描的对象是什么？有多大？
  - 是否存在 索引快速全扫描 (index fast full scan)？扫描的是哪个索引？
  - 是否存在 多个索引访问 (index range scan, index unique scan) 然后进行 join 或 concatenation？
  - 检查估算的 e-rows (estimated rows) 和实际的 a-rows (actual rows - 如果收集了执行统计) 是否偏差巨大？偏差大可能导致选择了低效的计划。
  - 检查 buffers / reads 列，了解逻辑读和物理读的总量。
定位引发等待的数据库对象：
- 使用 ash 查询结果中的 current_obj# 或 p1/p2 值（结合 v$session_wait 的 p1=files count, p2=blocks count，但通常不如 ash 直接）。
- 更常用的是结合找到的 sql 和执行计划，直接确定被全表扫描或索引快速全扫描的表或索引。
- 查询具体对象：
```
select owner, object_name, object_type, subobject_name
from dba_objects
where object_id = &current_obj#; -- 来自 ash
```
检查 i/o 性能：
- 数据库层面：
  - awr/ash 报告： iostat by function/filetype / iostat by file / tablespace io stats。查看：
    - av rd (ms) / avg wait time (ms)：单块读取平均等待时间（db file sequential read）和 db file parallel read 的平均等待时间。理想情况下（特别是 ssd），应该 < 10ms，最好 < 5ms。机械盘可能 10-20ms 算正常，超过 20ms 通常表示 i/o 慢。
    - read total (mb) / reads：总的物理读量和 i/o 次数。
    - read iops：每秒物理读次数。
    - % of total read i/o：哪些文件/表空间是热点。
  - 动态性能视图：
```
-- 文件级 i/o 统计 (自实例启动)
select file_id, file_name,
       phyrds "physical reads",
       phyblkrd "physical blocks read",
       readtim "read time (cs)", -- 厘秒
       round(readtim / decode(phyrds, 0, 1, phyrds), 3) "avg read time (ms)"
from v$filestat fs
join dba_data_files df on fs.file# = df.file_id
order by readtim desc;

-- 系统级 i/o 统计
select stat_name, value
from v$sysstat
where stat_name like '%physical read%'
   or stat_name like '%read io requests%';
```
- 操作系统层面：
  - 使用 os 工具监控磁盘性能：
    - linux: iostat -dxm 5 (看 await, svctm, %util, r/s, rkb/s), vmstat 1, dstat, sar -d
    - aix: iostat -drl 1, vmstat 1, filemon
    - solaris: iostat -xnz 5, vmstat 1, dtrace
  - 关键指标：
    - 服务时间 (svctm, service time): 磁盘处理一个 i/o 请求的时间。ssd 应 < 1ms，机械盘 < 20ms。
    - 等待时间 (await): i/o 请求在 os 队列中等待时间 + 服务时间。高 await 通常表示磁盘饱和或慢。await 应接近 svctm，远高于 svctm 说明队列长。
    - 利用率 (%util): 磁盘繁忙程度百分比。持续 > 70-80% 通常表示饱和。ssd 可以处理接近 100%，但延迟可能升高。
    - iops (r/s): 每秒读操作数。与存储规格对比。
    - 吞吐量 (rkb/s): 每秒读数据量 (kb/s)。与存储带宽对比。
    - 队列长度 (avgqu-sz): 平均队列长度。持续 > 2-3 可能表示饱和。
- 存储阵列层面： 使用存储厂商的管理工具监控 lun/volume 的性能：延迟、iops、吞吐量、缓存命中率、前端/后端端口状态等。

检查数据库配置：

-- 重要 i/o 相关参数
show parameter db_file_multiblock_read_count
show parameter filesystemio_options
show parameter disk_asynch_io -- (通常 true, 表示尝试使用 aio)
-- 检查数据文件分布 (是否都放在同一慢速盘上?)
select tablespace_name, file_name from dba_data_files;
-- 检查表/索引碎片 (可能需要定期分析)
exec dbms_stats.gather_table_stats('owner', 'table_name');
-- 检查 assm 段位图块访问 (v$segstat 或 awr 的段统计)

区分问题根源：
- 如果 avg wait (ms) 很高 (e.g., > 20ms) 且 os/存储监控也显示高延迟：问题很可能是存储 i/o 性能不足或配置不当（os i/o 参数、db_file_multiblock_read_count 过大）。需要优化存储或调整配置。
- 如果 avg wait (ms) 在可接受范围 (e.g., < 10ms)，但等待事件的 total wait time (s) 很高：问题根源是应用产生了过多的物理 i/o 请求（低效 sql、缺乏索引、全扫大表）。需要优化 sql 和数据库设计。
- 如果 db file parallel read 的等待次数 (waits) 和等待时间 (total wait time) 很高，但 avg wait (ms) 很低：表明虽然每次并行读很快，但数据库不得不发起极其大量的并行读操作。这通常指向极其低效的 sql 或严重碎片化的对象，导致 oracle 需要读取海量离散块。

优化建议：

优化 sql (最根本)：
- 重写低效 sql，避免不必要的全表扫描。
- 添加合适的索引，让查询能走索引范围扫描或唯一扫描。
- 优化连接条件和连接顺序。
- 考虑使用物化视图或查询重写。
- 确保统计信息准确。
优化数据库设计：
- 对大表进行分区（partitioning），减少每次需要扫描的数据量。
- 定期重组碎片化的表和索引 (alter table ... move, alter index ... rebuild)，特别是频繁 dml 的表。考虑使用 shrink space。
- 评估使用 iot（索引组织表）或聚簇表是否合适。
- 考虑对大表启用表压缩。
- 调整 pctfree/pctused 以减少行迁移和碎片。
优化存储配置：
- 升级硬件： 使用更快的存储介质（如 ssd/nvme）。
- 优化存储配置： 确保 raid 级别合适（如 raid 10 for performance），条带化（striping）配置得当，lun 分布均匀，避免热点。
- 使用 asm： oracle asm 能自动进行条带化和负载均衡，通常比传统文件系统管理更优。
- 增加存储带宽： 更多/更快的 hba 卡，升级 san 交换机/链路。
- 调整存储缓存策略： 确保读缓存策略有效（但需注意写缓存的安全性）。
优化 os 配置：
- 确保启用了异步 i/o (aio) 并且工作正常（filesystemio_options=setall 或 asynch）。
- 调整 os i/o 调度器（如 linux 用 deadline 或 noop for ssd）和队列深度参数 (queue_depth, nr_requests)。
- 确保文件系统（如果使用）合理对齐（partition alignment）。
优化数据库配置：
- 谨慎调整 db_file_multiblock_read_count：不要盲目设大。参考存储的最佳 i/o 大小（如 ssd 的条带大小/raid 条带大小）进行设置。通常 128 是一个较高的上限，很多场景下 32 或 64 可能更优。测试是关键。alter system set db_file_multiblock_read_count=64 scope=spfile/both;
- 增加 buffer cache (db_cache_size)：减少物理 i/o 需求（但治标不治本，仍需优化 sql）。
- 使用多dbwr进程： 如果写是瓶颈（间接影响读），可以增加 db_writer_processes。
- 优化检查点： 调整 fast_start_mttr_target 或相关隐含参数（需谨慎），避免过于频繁或过长的检查点。
- 分散 i/o： 将热点数据文件移动到不同的物理磁盘或存储控制器上。