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

付明發表於 2025-10-23 11:15:31

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

好的，我们来详细剖析一下 Oracle 数据库中的 <code>db file parallel read</code> 等待事件。
核心概念：
<code>db file parallel read</code> 是一个 I/O 类 等待事件。它表示一个会话（通常是前台用户进程或服务进程）正在等待一次由数据库自身发起的、并行化的、读取多个非连续数据块（通常来自不同数据文件）的物理 I/O 操作完成。
关键点理解：
<ol><li>并行化 (Parallel)： 这是核心。Oracle 不是逐个请求这些离散的数据块，而是一次性提交多个 I/O 请求给操作系统（O/S）或存储系统，让它们尽可能并发地去读取这些块。这类似于你同时派多个人去图书馆的不同书架拿书，而不是一个人来回跑多次。</li><li>非连续 (Non-contiguous)： 请求的数据块在磁盘上的物理位置不是连续的（不像 <code>db file scattered read</code> 那样读取连续范围的多块）。它们可能散落在同一个数据文件的不同区域，或者更常见的是分布在多个不同的数据文件中。</li><li>数据库发起 (Database Initiated)： 这个 I/O 是由 Oracle 数据库进程（如服务进程 <code>server process</code>）直接发起的，而不是由后台进程（如 DBWn）发起的。</li><li>等待主体： 前台进程（服务进程）在发出这些并行的 I/O 请求后，会进入等待状态 (<code>db file parallel read</code>)，直到所有请求的数据块都被读取到 Buffer Cache 中（或直到超时）。</li><li>与 Parallel Query/DML 的区别： 不要与并行查询（Parallel Query/DML）混淆。<code>db file parallel read</code> 描述的是单个进程内部如何执行一次物理 I/O 操作（并行提交多个离散 I/O 请求）。而并行查询是多个进程（并行执行服务器）协作处理一个 SQL 语句。当然，并行查询的进程在执行物理读时，它们自己也可能遇到 <code>db file parallel read</code> 等待。</li></ol>
详细原理与产生过程：
<ol><li>SQL 执行与逻辑读： 用户会话执行一条 SQL 语句（例如，一个需要访问大量离散数据块的查询，或者涉及多个索引查找的操作）。</li><li>Buffer Cache 检查： 服务进程首先在 SGA 的 Buffer Cache 中查找所需的数据块（逻辑读）。如果找不到（Cache Miss），就需要进行物理 I/O。</li><li>识别所需块： 服务进程确定需要从磁盘读取的多个、物理位置不连续的数据块列表。这些块可能属于：<ul><li>多个不同的索引段（如多个索引分支块或叶子块）。</li><li>一个表的不同数据块（这些块在磁盘上物理不连续）。</li><li>数据文件头（<code>file header block</code>）。</li><li>段头块（<code>segment header block</code>），特别是 ASSM 位图块。</li><li>控制文件（虽然控制文件读取通常有其特定事件，但有时也可能归入此类）。</li></ul></li><li>发起并行 I/O 请求： 服务进程不是逐个请求这些块，而是构造一个包含所有需要读取的块地址（文件号+块号）的列表，然后通过一次系统调用（如 Linux/Unix 上的 <code>preadv()</code> 或 AIO 接口）将这个列表提交给操作系统。</li><li>进入等待状态： 提交 I/O 请求后，服务进程无法继续工作（因为它需要这些块），于是它将自己挂起，并记录一个 <code>db file parallel read</code> 等待事件。<code>P1</code> = 要读取的文件数量，<code>P2</code> = 要读取的总块数，<code>P3</code> = 这些请求被拆分的次数（通常与 <code>P2</code> 相关）。</li><li>操作系统/存储处理： 操作系统接收这个批量 I/O 请求列表。它负责将这些请求调度到底层存储设备（磁盘/SSD/存储阵列）。存储系统会尝试并行处理这些离散的 I/O 请求（性能取决于存储的并发处理能力、队列深度、寻道时间/延迟等）。</li><li>I/O 完成与唤醒： 当所有请求的数据块都从磁盘读取完毕并传输到 Buffer Cache 中后，操作系统通知 Oracle 服务进程。</li><li>恢复执行： 服务进程被唤醒，从 <code>db file parallel read</code> 等待中恢复，获取到所需的数据块，继续执行 SQL 语句（进行逻辑处理、返回结果等）。</li></ol>
典型场景：
<ol><li>全表扫描 (Full Table Scans - FTS)： 这是最常见的原因之一。虽然 <code>db file scattered read</code> 更常与 FTS 关联（读取连续范围的多块），但当表数据非常分散（高水位线下有很多碎片化的空闲空间，或者表经过大量 DML 后没有重组），或者 Buffer Cache 只能容纳部分数据块时，Oracle 在预取 (<code>prefetching</code>) 后续非连续块时，就可能采用 <code>parallel read</code> 的方式。特别是当优化器认为离散读取更高效时。</li><li>索引快速全扫描 (Index Fast Full Scan - IFFS)： IFFS 按物理存储顺序读取索引段的所有叶子块（类似全表扫描索引）。如果索引段在磁盘上物理存储不连续（碎片化），读取这些离散的叶子块就可能导致 <code>db file parallel read</code>。</li><li>读取文件头/段头： 数据库需要读取多个数据文件的头部信息（例如在打开数据库、检查点期间），或者需要读取多个段的段头块（特别是 ASSM 表空间中的位图块）时。</li><li>控制文件读取： 某些需要访问控制文件多个块的操作（虽然 <code>control file</code> 等待事件更典型，但有时也可能归入 <code>db file parallel read</code>)。</li><li>数据块预取 (Prefetching)： 优化器或 Oracle 内部机制预测到接下来需要访问一些离散的数据块（不在当前连续范围内），并提前发起并行读取。</li><li>涉及多个索引的查询： 执行计划需要访问多个索引（如 <code>INDEX JOIN</code>, <code>AND-EQUAL</code>），服务进程需要同时从这些不同的索引段中读取离散的数据块（叶子块或分支块）。</li><li>RAC 环境中的全局缓存访问： 虽然主要等待是 <code>gc</code> 事件，但当实例需要从磁盘读取块（例如首次访问或强制全库扫描）且该块在本地实例的 Buffer Cache 中没有时，读取磁盘的过程本身就可能触发 <code>db file parallel read</code>。</li></ol>
可能的原因（导致该等待成为瓶颈）：
<ol><li>存储 I/O 性能不足 (最常见)：
<ul><li>高 I/O 延迟： 磁盘响应时间过长（特别是机械磁盘的寻道和旋转延迟）。SSD 延迟低，但如果队列深度不足或过载，延迟也会上升。</li><li>I/O 吞吐量瓶颈： 存储带宽（MB/s）或 IOPS（每秒 I/O 操作数）达到上限。</li><li>存储控制器/网络/HBA 卡瓶颈： 存储阵列控制器、SAN 交换机、主机 HBA 卡过载或配置不当。</li><li>存储争用： 同一存储上运行的其他数据库或应用消耗了大量 I/O 资源。</li></ul></li><li>操作系统 I/O 子系统配置问题：<ul><li>文件系统缓存或 I/O 调度器（如 <code>cfq</code>, <code>deadline</code>, <code>noop</code>）配置不当。</li><li>OS 级别的 I/O 队列深度 (<code>max_sectors_kb</code>, <code>nr_requests</code>, <code>queue_depth</code>) 设置过低，限制了并行处理能力。</li><li>异步 I/O (<code>AIO</code>) 未启用或配置不当（Oracle 推荐使用 AIO 来优化并行读）。</li></ul></li><li>数据库配置不当：<ul><li>db_file_multiblock_read_count 设置过高： 这个参数控制单次 I/O 请求读取的最大连续块数。如果设置得非常高（远大于存储系统能高效处理的单次 I/O 大小），当 Oracle 进行离散读取 (<code>parallel read</code>) 时，它可能会尝试提交非常大的离散 I/O 请求列表，超过存储的最佳处理能力，反而导致延迟增加。需要根据存储特性（如 SSD 条带大小、RAID 配置）进行合理设置。</li><li>I/O 分布不均衡： 热点数据文件位于慢速磁盘或 I/O 繁忙的存储路径上。未使用 ASM 或文件系统条带化，导致单个文件成为瓶颈。</li><li>filesystemio_options 设置不当： 未启用 <code>SETALL</code> 或 <code>ASYNCH</code>，限制了 Oracle 使用异步和直接 I/O 的能力。</li></ul></li><li>数据库负载/设计问题：<ul><li>低效 SQL： 产生大量物理 I/O 的 SQL（全表扫描大表、低效索引使用、笛卡尔积等）是根源。它们触发了大量的 <code>db file parallel read</code> 请求。</li><li>索引碎片化： 导致 IFFS 需要读取大量离散的索引块。</li><li>表碎片化/高水位线问题： 导致 FTS 需要读取大量离散的表块。</li><li>频繁访问 ASSM 位图块： 在 DML 繁忙的系统上，对 ASSM 位图块的争用可能导致对这些块的读取成为瓶颈（这些读取常是 <code>parallel read</code>)。</li><li>检查点过慢： 虽然主要等待是 <code>db file parallel write</code>，但检查点期间也需要读取文件头等信息，可能涉及 <code>parallel read</code>。</li></ul></li><li>主机资源瓶颈 (间接)：<ul><li>CPU 过载导致处理 I/O 中断和 Oracle 进程唤醒延迟。</li><li>内存不足导致 Buffer Cache 命中率低，增加物理 I/O 需求。</li></ul></li></ol>
详细排查过程：
排查的核心思路是：定位引发大量 db file parallel read 的 SQL 和对象，分析 I/O 性能，确定是 SQL/设计问题还是存储/配置问题。
<ol><li>确认问题存在：
<ul><li>AWR/ASH 报告： 这是最重要的起点。查看 <code>Top 5 Timed Foreground Events</code> 或 <code>Top Wait Events</code> 部分，确认 <code>db file parallel read</code> 是否在 Top 事件中，并且其 <code>Total Wait Time (s)</code> 和 <code>Avg Wait (ms)</code> 是否显著高于正常水平。注意 <code>% DB time</code>。</li><li>实时监控： 使用 <code>v$session_wait</code> (当前等待) 或 <code>v$active_session_history</code> (近历史) / <code>DBA_HIST_ACTIVE_SESS_HISTORY</code> (历史 ASH) 查看当前或历史会话是否正在经历或经历过长时间的 <code>db file parallel read</code> 等待。<div class="jb51code"><pre class="brush:sql;">-- 当前等待的会话
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分钟
</pre></div></li></ul></li><li>定位引发等待的 SQL：
<ul><li>ASH 报告： <code>SQL Statistics</code> -> <code>SQLs with Top DB Time</code> / <code>SQLs with Top Event Waits</code>。查找在 <code>db file parallel read</code> 上消耗时间最多的 SQL_ID。</li><li>AWR 报告： <code>SQL Statistics</code> -> <code>SQL ordered by Elapsed Time</code> / <code>SQL ordered by User I/O Wait Time</code>。结合 <code>Elapsed Time</code> 和 <code>User I/O Wait Time</code> 高的 SQL。</li><li>从会话定位 SQL： 使用步骤 1 中查询到的 <code>SID</code> 和 <code>SERIAL#</code> 或 <code>SQL_ID</code>：<div class="jb51code"><pre class="brush:sql;">-- 当前 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';
</pre></div></li></ul></li><li>分析 SQL 执行计划：
<ul><li>使用 <code>DBMS_XPLAN</code> (<code>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_number));</code>) 或 AWR 报告中的执行计划。</li><li>重点关注：<ul><li>是否存在 全表扫描 (TABLE ACCESS FULL)？扫描的对象是什么？有多大？</li><li>是否存在 索引快速全扫描 (INDEX FAST FULL SCAN)？扫描的是哪个索引？</li><li>是否存在 多个索引访问 (INDEX RANGE SCAN, INDEX UNIQUE SCAN) 然后进行 JOIN 或 CONCATENATION？</li><li>检查估算的 E-Rows (Estimated Rows) 和实际的 <code>A-Rows</code> (Actual Rows - 如果收集了执行统计) 是否偏差巨大？偏差大可能导致选择了低效的计划。</li><li>检查 Buffers / Reads 列，了解逻辑读和物理读的总量。</li></ul></li></ul></li><li>定位引发等待的数据库对象：
<ul><li>使用 ASH 查询结果中的 <code>CURRENT_OBJ#</code> 或 <code>P1</code>/<code>P2</code> 值（结合 <code>v$session_wait</code> 的 <code>P1</code>=files count, <code>P2</code>=blocks count，但通常不如 ASH 直接）。</li><li>更常用的是结合找到的 SQL 和执行计划，直接确定被全表扫描或索引快速全扫描的表或索引。</li><li>查询具体对象：<div class="jb51code"><pre class="brush:sql;">SELECT owner, object_name, object_type, subobject_name
FROM dba_objects
WHERE object_id = &current_obj#; -- 来自 ASH
</pre></div></li></ul></li><li>检查 I/O 性能：
<ul><li>数据库层面：
<ul><li>AWR/ASH 报告： <code>IOStat by Function/Filetype</code> / <code>IOStat by File</code> / <code>Tablespace IO Stats</code>。查看：
<ul><li><code>Av Rd (ms)</code> / <code>Avg Wait Time (ms)</code>：单块读取平均等待时间（<code>db file sequential read</code>）和 <code>db file parallel read</code> 的平均等待时间。理想情况下（特别是 SSD），应该 < 10ms，最好 < 5ms。机械盘可能 10-20ms 算正常，超过 20ms 通常表示 I/O 慢。</li><li><code>Read Total (MB)</code> / <code>Reads</code>：总的物理读量和 I/O 次数。</li><li><code>Read IOPS</code>：每秒物理读次数。</li><li><code>% of Total Read I/O</code>：哪些文件/表空间是热点。</li></ul></li><li>动态性能视图：<div class="jb51code"><pre class="brush:sql;">-- 文件级 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%';
</pre></div></li></ul></li><li>操作系统层面：<ul><li>使用 OS 工具监控磁盘性能：<ul><li>Linux: <code>iostat -dxm 5</code> (看 <code>await</code>, <code>svctm</code>, <code>%util</code>, <code>r/s</code>, <code>rkB/s</code>), <code>vmstat 1</code>, <code>dstat</code>, <code>sar -d</code></li><li>AIX: <code>iostat -DRl 1</code>, <code>vmstat 1</code>, <code>filemon</code></li><li>Solaris: <code>iostat -xnz 5</code>, <code>vmstat 1</code>, <code>dtrace</code></li></ul></li><li>关键指标：<ul><li>服务时间 (<code>svctm</code>, <code>service time</code>): 磁盘处理一个 I/O 请求的时间。SSD 应 < 1ms，机械盘 < 20ms。</li><li>等待时间 (<code>await</code>): I/O 请求在 OS 队列中等待时间 + 服务时间。高 <code>await</code> 通常表示磁盘饱和或慢。<code>await</code> 应接近 <code>svctm</code>，远高于 <code>svctm</code> 说明队列长。</li><li>利用率 (<code>%util</code>): 磁盘繁忙程度百分比。持续 > 70-80% 通常表示饱和。SSD 可以处理接近 100%，但延迟可能升高。</li><li>IOPS (<code>r/s</code>): 每秒读操作数。与存储规格对比。</li><li>吞吐量 (<code>rkB/s</code>): 每秒读数据量 (KB/s)。与存储带宽对比。</li><li>队列长度 (<code>avgqu-sz</code>): 平均队列长度。持续 > 2-3 可能表示饱和。</li></ul></li></ul></li><li>存储阵列层面： 使用存储厂商的管理工具监控 LUN/Volume 的性能：延迟、IOPS、吞吐量、缓存命中率、前端/后端端口状态等。</li></ul></li><li>检查数据库配置：
<div class="jb51code"><pre class="brush:sql;">-- 重要 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 的段统计)
</pre></div></li><li>区分问题根源：
<ul><li>如果 <code>Avg Wait (ms)</code> 很高 (e.g., > 20ms) 且 OS/存储监控也显示高延迟：问题很可能是存储 I/O 性能不足或配置不当（OS I/O 参数、<code>db_file_multiblock_read_count</code> 过大）。需要优化存储或调整配置。</li><li>如果 <code>Avg Wait (ms)</code> 在可接受范围 (e.g., < 10ms)，但等待事件的 <code>Total Wait Time (s)</code> 很高：问题根源是应用产生了过多的物理 I/O 请求（低效 SQL、缺乏索引、全扫大表）。需要优化 SQL 和数据库设计。</li><li>如果 <code>db file parallel read</code> 的等待次数 (<code>Waits</code>) 和等待时间 (<code>Total Wait Time</code>) 很高，但 <code>Avg Wait (ms)</code> 很低：表明虽然每次并行读很快，但数据库不得不发起极其大量的并行读操作。这通常指向极其低效的 SQL 或严重碎片化的对象，导致 Oracle 需要读取海量离散块。</li></ul></li></ol>
优化建议：
<ol><li>优化 SQL (最根本)：
<ul><li>重写低效 SQL，避免不必要的全表扫描。</li><li>添加合适的索引，让查询能走索引范围扫描或唯一扫描。</li><li>优化连接条件和连接顺序。</li><li>考虑使用物化视图或查询重写。</li><li>确保统计信息准确。</li></ul></li><li>优化数据库设计：<ul><li>对大表进行分区（Partitioning），减少每次需要扫描的数据量。</li><li>定期重组碎片化的表和索引 (<code>ALTER TABLE ... MOVE</code>, <code>ALTER INDEX ... REBUILD</code>)，特别是频繁 DML 的表。考虑使用 <code>SHRINK SPACE</code>。</li><li>评估使用 IOT（索引组织表）或聚簇表是否合适。</li><li>考虑对大表启用表压缩。</li><li>调整 PCTFREE/PCTUSED 以减少行迁移和碎片。</li></ul></li><li>优化存储配置：<ul><li>升级硬件： 使用更快的存储介质（如 SSD/NVMe）。</li><li>优化存储配置： 确保 RAID 级别合适（如 RAID 10 for performance），条带化（<code>striping</code>）配置得当，LUN 分布均匀，避免热点。</li><li>使用 ASM： Oracle ASM 能自动进行条带化和负载均衡，通常比传统文件系统管理更优。</li><li>增加存储带宽： 更多/更快的 HBA 卡，升级 SAN 交换机/链路。</li><li>调整存储缓存策略： 确保读缓存策略有效（但需注意写缓存的安全性）。</li></ul></li><li>优化 OS 配置：<ul><li>确保启用了异步 I/O (AIO) 并且工作正常（<code>filesystemio_options=SETALL</code> 或 <code>ASYNCH</code>）。</li><li>调整 OS I/O 调度器（如 Linux 用 <code>deadline</code> 或 <code>noop</code> for SSD）和队列深度参数 (<code>queue_depth</code>, <code>nr_requests</code>)。</li><li>确保文件系统（如果使用）合理对齐（<code>partition alignment</code>）。</li></ul></li><li>优化数据库配置：<ul><li>谨慎调整 <code>db_file_multiblock_read_count</code>：不要盲目设大。参考存储的最佳 I/O 大小（如 SSD 的条带大小/RAID 条带大小）进行设置。通常 128 是一个较高的上限，很多场景下 32 或 64 可能更优。测试是关键。<code>ALTER SYSTEM SET db_file_multiblock_read_count=64 SCOPE=SPFILE/BOTH;</code></li><li>增加 Buffer Cache (<code>db_cache_size</code>)：减少物理 I/O 需求（但治标不治本，仍需优化 SQL）。</li><li>使用多DBWR进程： 如果写是瓶颈（间接影响读），可以增加 <code>db_writer_processes</code>。</li><li>优化检查点： 调整 <code>fast_start_mttr_target</code> 或相关隐含参数（需谨慎），避免过于频繁或过长的检查点。</li><li>分散 I/O： 将热点数据文件移动到不同的物理磁盘或存储控制器上。</li></ul></li></ol>
总结：
<code>db file parallel read</code> 是 Oracle 优化离散块读取性能的一种机制。它本身不是错误，而是数据库工作的体现。当它成为系统的主要瓶颈等待事件时，表示系统在执行大量离散物理读操作，并且/或者这些操作的延迟较高。
排查的核心是：
<ol><li>找到源头 SQL 和对象。</li><li>分析 I/O 性能（数据库报告 + OS/存储监控），确定延迟是否正常。</li><li>区分是“读得太慢”（存储问题/配置问题）还是“读得太多”（SQL/设计问题）。</li></ol>
根据分析结果，采取针对性的优化措施，优先优化 SQL 和数据库设计，其次是存储和配置调整。

頁: [1]

琼殿技术论坛's Archiver

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