面试宝典: Oracle数据库direct path read等待事件处理过程

原创于 2025-08-06 12:28:40 发布 · 857 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

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

584 篇文章

订阅专栏

在这里插入图片描述
好的，我们来深入探讨 Oracle 数据库中的 direct path read 等待事件。它与 db file sequential read 有着本质的不同，理解其机制对于优化涉及大数据量操作的性能至关重要。

1. 什么是 `direct path read` 等待事件？

本质： 它表示一个会话正在进行 绕过 Buffer Cache 的物理 I/O 读取操作。数据块直接从磁盘读取到会话的 PGA (Process Global Area) 或共享内存区域（如并行查询时的共享池），而不是进入 SGA 的 Buffer Cache。
名字解析：
- direct path：指 I/O 路径是“直接”的，不经过 SGA 的 Buffer Cache 机制。
- read：表示这是一个读取操作（对应的写入是 direct path write）。
核心特征：
- 绕过 Buffer Cache： 这是最显著的特点。读取的数据块不会在 Buffer Cache 中缓存或替换现有块。
- 目标在 PGA： 对于串行操作，数据直接读入执行会话的 PGA 内存中。对于并行操作，数据可能读入共享池中的 Large Pool 或 Buffer Cache（取决于版本和设置），再由 Query Coordinator 分配给并行从属进程的 PGA。
- 异步 I/O (通常)： Oracle 会尽可能使用异步 I/O (如果启用且操作系统支持)，允许会话在 I/O 请求发出后继续执行其他工作（如处理已读入的数据），而不是像 sequential read 那样完全阻塞等待单次 I/O 完成。但会话最终仍需等待其发出的所有 I/O 请求完成。
- 大块 I/O： 通常以较大的块（例如 1MB 或 128KB，具体取决于 _db_block_buffers 或 db_file_multiblock_read_count 等参数）进行读取，以提高吞吐量。
等待参数：
- P1：被读取的文件号 (file#)。
- P2：起始块号 (first block#)。
- P3：请求读取的块数 (block count)。这个值通常远大于 1。

2. 产生的过程

当一个会话执行需要大量数据扫描的操作时：

优化器决策/机制触发： 优化器根据对象大小、统计信息、参数设置（如 _serial_direct_read）判断全表扫描/快速全索引扫描更有效，或者 Oracle 的自动机制（如 11g+ 的智能扫描优化）决定使用直接路径读。对于并行查询，直接路径读通常是默认或强制的。
分配 PGA 内存： 会话在它的 PGA 中分配内存（通常是 sort_area_size 或 pga_aggregate_target 管理的区域）来接收即将读取的数据块。
发出直接读请求： Oracle 服务器进程向操作系统发出异步 I/O 请求（如果启用），要求直接从磁盘读取指定文件(file#)、指定起始块(first block#)开始的连续多个数据块(block count)。
进入等待： 虽然异步 I/O 允许会话在请求发出后继续处理已读入的数据，但会话最终必须等待其发出的所有未完成的直接路径读 I/O 操作完成，才能继续后续处理步骤（如排序、聚合、返回数据等）。此时，会话记录 direct path read 等待事件。等待的是一批 I/O 操作的整体完成。
I/O 完成与数据处理： 操作系统完成磁盘读取，将数据块直接写入会话 PGA 指定的内存区域。会话处理这些数据（如应用过滤条件、进行排序等）。
重复步骤 3-5： 如果数据量很大，会话会继续分配 PGA 内存，发出下一批直接读请求，处理数据，并等待这些 I/O 完成，直到扫描完所有需要的数据。
无 Buffer Cache 影响： 读取过程中，Buffer Cache 的内容不受影响。这些数据块不会被缓存。

3. 哪些场景会触发 `direct path read`

主要发生在需要大规模顺序扫描数据的操作中：

全表扫描 (Full Table Scan, FTS): 这是最常见的场景。尤其是当表大小超过 _small_table_threshold（隐含参数，默认约为 Buffer Cache 大小的 2%）或 Oracle 认为直接读更优时。
索引快速全扫描 (Index Fast Full Scan, IFFS): 当查询只需要索引列的数据且顺序无关紧要时，Oracle 可能选择 IFFS，它通常使用直接路径读来高效读取整个索引段。
并行查询 (Parallel Query, PQ): 绝大多数并行查询操作（无论是全表扫描、索引快速全扫描、还是分区扫描）都强制使用或默认使用直接路径读，以避免对 Buffer Cache 的争用，并允许并行从属进程独立读取数据。
排序操作溢出到磁盘后的读取： 当排序操作太大无法在 PGA 的排序区 (sort_area_size / pga_aggregate_target) 中完成时，中间结果会写入临时表空间。当需要从临时文件中读取这些排序后的数据时，会触发 direct path read（更精确地说是 direct path read temp）。
哈希连接溢出到磁盘后的读取： 类似排序，如果哈希连接构建表或探测表太大，部分数据会写入临时文件，后续读取时触发 direct path read temp。
LOB 数据读取 (SecureFiles Out of Line): 当读取存储在表外（Out-of-Line）的 SecureFiles LOB 数据时，可能使用直接路径读。
使用 APPEND 提示的插入操作： 虽然写入是 direct path write，但插入过程中可能需要读取数据（如从子查询），这些读取也可能走直接路径。
数据泵导入 (IMPDP): 在加载表数据时使用直接路径读。

4. 可能的原因

direct path read 本身是处理大数据集的高效机制。但当它导致性能问题时，原因通常是：

A. 合理且预期的操作 (正常或设计使然):
- 执行大型报表查询、批处理作业，这些操作设计上就需要全量扫描大表或分区。
- 并行查询的正常工作机制。
- 大数据量排序/哈希连接需要读写临时文件。
- 刚启动的数据仓库刷新。
B. SQL 执行效率低或计划不当 (根本原因):
- 缺失或失效的索引： 本应通过高效索引访问（走 db file sequential read）的查询，由于缺少合适索引或索引失效，被迫进行了全表扫描（走 direct path read）。
- 错误的优化器统计信息： 统计信息过旧或不准，导致优化器低估了索引扫描的成本，或者高估了全表扫描的成本（但实际上全表扫描是更差的选择），从而错误选择了全表扫描。
- 低效的 SQL 写法： 查询条件无法有效利用索引（如对索引列使用函数、隐式类型转换）、不必要的全表扫描（如 SELECT * 但只用到部分列且无过滤）、笛卡尔积连接等，导致需要扫描的数据量远大于实际所需。
- 过度的排序/哈希： 查询包含不必要的 ORDER BY, GROUP BY, DISTINCT, 或导致大表哈希连接的写法。
- 隐式转换： 导致索引失效，触发全表扫描。
C. 配置或参数影响:
- _serial_direct_read 参数：这个隐含参数控制串行操作是否启用直接路径读。默认通常是 AUTO (11g+)，让 Oracle 决定。如果强制设置为 ALWAYS，即使对小表也会尝试直接读，可能不利。如果设置为 NEVER，会强制全表扫描走 Buffer Cache (产生 db file scattered read)，这可能对某些混合负载环境有好处，但需谨慎。
- _small_table_threshold：影响 Oracle 决定对小表使用 FTS + Buffer Cache 还是 FTS + Direct Path Read 的阈值。如果 Buffer Cache 很大，这个阈值也会相应提高。
- db_file_multiblock_read_count：设置每次直接路径读请求读取的块数。设置过小可能导致更多次 I/O 请求；设置过大可能超出操作系统或存储 I/O 大小限制，反而降低效率。
- PGA 配置不足： pga_aggregate_target 或 pga_aggregate_limit 设置过小，导致排序/哈希操作更频繁地溢出到临时表空间，增加 direct path read temp 等待。
D. I/O 子系统性能差 (硬件/存储层): - 这是导致 direct path read 平均等待时间过高的主要原因。
- 磁盘吞吐量不足： 存储带宽（MB/s, IOPS）无法满足大规模顺序扫描的需求，特别是当多个会话同时进行大扫描时。
- 高延迟： 即使是顺序读，如果磁盘响应时间 (await, Avg Disk sec/Read) 过高，也会显著拖慢扫描速度。
- 存储阵列瓶颈： 控制器过载、Cache 不足或配置不当（如 Read-Ahead 策略）、后端磁盘速度慢（如大量 SATA HDD）、RAID 级别不适用（如 RAID5 写惩罚影响混合负载）。
- 文件系统/ASM 效率： 文件系统碎片、ASM 重平衡、条带化 (Stripe Size/Depth) 设置不合理（太小可能导致并发 I/O 争用一个物理盘，太大可能降低单次 I/O 效率）。
- 操作系统 I/O 配置： 异步 I/O (Async I/O) 未启用或配置不佳 (disk_asynch_io, filesystemio_options)、I/O Scheduler 策略不当。
- 网络瓶颈 (SAN/NAS)： 对于网络存储，网络带宽或延迟成为瓶颈。
- 资源争用： 同一存储上其他数据库或应用的 I/O 密集型任务。

5. 详细排查过程

排查 direct path read 的核心是区分：它是执行必要操作的高效方式？还是低效 SQL 或计划错误导致的意外结果？或者存储本身成为瓶颈？

步骤 1: 确认问题与范围

识别 Top Wait Event: 查看 AWR/ASH 报告 (awrrpt.sql, ashtop.sql)。确认 direct path read (和 direct path read temp) 是否在系统级别或特定时间段是主要等待事件。记录其 Total Wait Time (s) 和 Avg Wait (ms)。
确定影响范围： 是整个数据库（如批处理时段）？特定用户/模块？特定大型查询？使用 ASH 报告 (ashrpt.sql) 或查询 gv$active_session_history / dba_hist_active_sess_history 按 sql_id, module, action, user_id 等维度聚合等待事件。
关注平均等待时间 (Avg Wait (ms)):
- 吞吐量指标： direct path read 的 Avg Wait (ms) 衡量的是 每次 I/O 调用 的等待时间。由于每次调用读取多个块（例如 128 块），这个时间会比 db file sequential read (单块) 自然长一些。
- 关键比较： 更重要的是对比 AWR 报告 “IOStat by Function summary” 中 “Data File Read” 或 “Temp File Read” 的 Av Rd(ms)。这个值应该与 Avg Wait (ms) 接近，并直接反映存储顺序读的延迟。对于高速存储（SSD/NVMe），期望值通常在几毫秒到十几毫秒；对于高速 HDD，可能在十几到几十毫秒。超过 50-100ms 通常表示 I/O 子系统存在瓶颈。
- 高总时间 + 正常平均时间： 表示发生了大量必要的直接读操作（可能是合理的批处理或报表）。
- 高平均时间： 强烈指向 I/O 子系统性能问题或严重争用。
区分类型： 注意区分是 direct path read (读数据文件) 还是 direct path read temp (读临时表空间文件)。后者通常与排序/哈希溢出相关。

步骤 2: 定位源头 SQL 和对象

AWR/ASH SQL 分析：
- 在 AWR 报告的 “SQL Statistics” -> “SQL ordered by Elapsed Time” / “SQL ordered by User I/O Wait Time” / “SQL ordered by Reads” / “SQL ordered by Executions” 部分重点查找：
  - 消耗大量 User I/O Wait Time 的 SQL。
  - 物理读 (Physical Read Bytes, Disk Reads) 或逻辑读 (Buffer Gets) 极高的 SQL。注意：直接路径读的物理读不会计入 Buffer Cache 的统计（consistent gets / db block gets），但会计入 physical reads / physical read IO requests / physical read bytes。
  - 执行次数可能不多，但每次执行开销巨大的 SQL。
- 在 ASH 报告的 “Top SQL with Top Events” 部分，找到 direct path read 等待最多的 sql_id。

查询 v$active_session_history / dba_hist_active_sess_history:

SELECT sql_id, sql_plan_operation, sql_plan_options, sql_plan_line_id, -- 执行计划操作
       COUNT(*) AS waits, SUM(time_waited)/1000 AS total_wait_ms,
       AVG(time_waited)/1000 AS avg_wait_ms, session_id, session_serial#,
       current_obj#, current_file#, current_block#
FROM gv$active_session_history
WHERE event IN ('direct path read', 'direct path read temp')
AND sample_time > SYSDATE - 1/24 -- 例如过去1小时
GROUP BY sql_id, sql_plan_operation, sql_plan_options, sql_plan_line_id,
         session_id, session_serial#, current_obj#, current_file#, current_block#
ORDER BY total_wait_ms DESC, waits DESC;

注意 sql_plan_operation (如 TABLE ACCESS FULL, INDEX FAST FULL SCAN, SORT ORDER BY, HASH JOIN) 和 sql_plan_options (如 PARALLEL)，这直接关联到触发原因。
current_obj# 指示被扫描的对象。

关联 SQL 文本和对象：
- 使用找到的 sql_id，查询 v$sqltext 或 dba_hist_sqltext 获取 SQL 文本。
- 使用 current_obj# 查询 dba_objects 找到具体的表或索引名。
检查对象大小和统计信息：
- 查询 dba_segments 查看涉及的表/索引的大小。是否是大对象？
- 检查 dba_tables / dba_indexes 的 last_analyzed 和统计信息是否最新准确？使用 DBMS_STATS 报告验证。

步骤 3: 分析 SQL 执行计划

获取执行计划： (方法同 db file sequential read 排查)。
关键检查点：
- 访问路径： 执行计划中是否确实出现了 TABLE ACCESS FULL 或 INDEX FAST FULL SCAN？这是触发 direct path read (数据文件) 的直接原因。
- 并行执行： 是否包含 PX 操作（如 PX COORDINATOR, PX SEND, PX RECEIVE）？并行查询是 direct path read 的主要场景。
- 排序/哈希操作： 是否包含 SORT ORDER BY, SORT GROUP BY, SORT UNIQUE, SORT AGGREGATE, HASH JOIN？这些操作可能导致 direct path read temp。
- 预估行数 (E-Rows) vs 实际行数 (A-Rows): 优化器估算是否严重偏离实际？低估行数可能导致优化器错误选择全表扫描（认为快），而实际很慢；高估行数可能导致优化器放弃索引（认为要回表太多行）。
- 缺失索引： 是否存在明显的、能显著减少数据访问量的索引未被使用？检查 WHERE 子句和 JOIN 条件。
- 低效操作： 是否存在笛卡尔积、全表扫描作为被驱动表等低效操作？

步骤 4: 检查配置、PGA 和 I/O 子系统

检查相关参数：

-- 串行直接读控制 (通常保持默认 AUTO)
SELECT x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND x.ksppinm = '_serial_direct_read';
-- 小表阈值
SELECT x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND x.ksppinm = '_small_table_threshold';
-- 多块读次数
SHOW PARAMETER db_file_multiblock_read_count;
-- PGA 配置
SHOW PARAMETER pga_aggregate_target;
SHOW PARAMETER pga_aggregate_limit;
-- 异步 I/O 和文件系统选项
SHOW PARAMETER disk_asynch_io;
SHOW PARAMETER filesystemio_options;

评估 PGA 使用： (AWR 报告)
- “PGA Memory” 部分：查看 PGA target, PGA allocated, PGA used, over allocation count。高 over allocation count 和 %PGA W/A (Work Area Percentage) 表示 PGA 不足，导致大量排序/哈希溢出到磁盘，增加 direct path read temp。
- “Workload Profile” -> sorts (disk)：磁盘排序次数，直接关联 direct path read temp。
I/O 性能指标 (AWR/OS)： (排查重点，特别是当 Avg Wait (ms) 高时)
- AWR:
  - “Load Profile”: 极高 Physical Reads/s 和 Physical Read Total Bytes/s。
  - “Instance Activity Stats”: 高 physical reads, physical read IO requests, physical read bytes。计算 physical read bytes / physical read IO requests 得到平均每次 I/O 请求大小（应接近 db_file_multiblock_read_count * db_block_size）。
  - “IOStat by Function summary”：核心！
    - Data File Read / Temp File Read：
      - Av Reads/s: 平均每秒读次数。
      - Av Rd(ms): 平均每次读等待时间 (毫秒) - 最关键指标！ 对比等待事件的 Avg Wait (ms)。
      - Av Blks/Rd: 平均每次读的块数。对于 direct path read 应该远大于 1 (接近 db_file_multiblock_read_count)。
    - Av Rd(ms) 是否过高？结合期望的存储性能判断。
  - “Tablespace IO Stats” / “File IO Stats”: 定位热点数据文件或临时文件。查看哪些文件承受了最高的读取负载和延迟。
  - “Operating System Statistics”: 查看 AVG_BUSY_TIME, AVG_IDLE_TIME, AVG_IOWAIT_TIME。高 AVG_IOWAIT_TIME 表示操作系统级别 I/O 等待严重。
- 操作系统工具： (同 db file sequential read 排查，但更关注吞吐量 MB/s 和顺序读延迟)
  - Linux: iostat -xm 2 (关注 rMB/s, await, svctm, %util), vmstat 2, sar -d 2。iotop 查看进程级 I/O。
  - AIX: iostat -DRlT 2 (关注 rKBPS, avgserv), sar -d 2。
  - Windows: Perfmon (关注 PhysicalDisk(*)\Disk Bytes/sec, PhysicalDisk(*)\Avg. Disk sec/Read, PhysicalDisk(*)\% Idle Time)。
  - 关键指标： Read Throughput (MB/s) 是否达到存储预期？Read Latency (Avg. Disk sec/Read) 是否与 AWR 的 Av Rd(ms) 一致且过高？Queue Length / %Utilization 是否持续高位？特定磁盘/LUN 是否成为瓶颈？

步骤 5: 综合分析与解决方案

根据排查结果，针对不同原因采取相应措施：

SQL 效率问题 (避免不必要的直接读)：
- 优化执行计划：
  - 收集最新、准确的统计信息 (DBMS_STATS.GATHER_TABLE_STATS, GATHER_SCHEMA_STATS)。考虑使用增量统计、并发收集。
  - 创建必要的、高选择性的索引（尤其是 WHERE 子句和 JOIN 条件的列）。评估组合索引。
  - 使用 SQL Plan Baseline 或 SQL Profile 固定好的计划（避免走 FTS）。
  - 谨慎使用 Optimizer Hints (如 INDEX(...), NO_INDEX_FFS(...)) 引导优化器。
- 重写 SQL：
  - 添加有效过滤条件，减少扫描数据量。
  - 避免 SELECT *，只选择需要的列。
  - 优化 JOIN 条件和顺序，避免笛卡尔积。
  - 重写或移除不必要的 ORDER BY / GROUP BY / DISTINCT。
  - 考虑物化视图预聚合数据（针对报表）。
  - 使用 WITH 子句或临时表分解复杂查询。
  - 解决隐式类型转换问题。
优化必要的直接读操作：
- 分区： 对大表进行分区，使查询只扫描相关分区 (Partition Pruning)。
- 并行执行： 对于真正需要全表扫描的大查询，合理使用并行 (PARALLEL hint 或表/索引并行度设置)，利用多进程加速扫描。注意并行度设置和系统资源平衡。
- 优化排序/哈希：
  - 适当增大 pga_aggregate_target / pga_aggregate_limit 以减少排序/哈希溢出。
  - 优化 SQL 减少排序需求（如用 HASH GROUP BY 代替 SORT GROUP BY）。
  - 确保临时表空间使用高效文件系统（如使用 BIGFILE 且位于高速磁盘）。
调整配置参数：
- 谨慎调整 _serial_direct_read: 除非有明确证据和测试，否则通常保持默认 AUTO。若大量小表意外走直接读且性能差，可考虑在会话或系统级临时设为 NEVER（需充分测试影响）。若确认存储极快且串行扫描大表多，可考虑 ALWAYS（罕见）。
- 调整 db_file_multiblock_read_count: 设置为操作系统和存储能高效处理的值（通常是 1MB / db_block_size）。例如 db_block_size=8k 时设 128。测试不同值的影响。
- 启用并优化异步 I/O： 确保 disk_asynch_io=TRUE (通常默认) 且 filesystemio_options=SETALL (或 ASYNCH / DIRECTIO)，并在操作系统层面启用和优化 AIO。
解决 I/O 子系统瓶颈 (这是降低 Avg Wait (ms) 的关键)：
- 硬件升级/存储迁移： 迁移到更快的存储（SSD, NVMe），特别是对临时表空间。
- 存储配置优化：
  - 确保 RAID 级别合适（如 RAID 10 对 OLAP/混合负载）。
  - 优化存储阵列 Cache（启用足够大的读 Cache，调整预读策略）。
  - 优化条带化： 调整条带大小 (Stripe Size) 和条带宽度 (Stripe Depth/Width) 以匹配 Oracle 的多块读大小 (db_file_multiblock_read_count * db_block_size) 和并发负载。通常更大的条带宽度能提高并发吞吐。
  - 将高负载数据文件、临时文件、重做日志文件分离到不同的物理磁盘组/LUN/控制器。
  - 修复硬件问题（故障盘、降级阵列）。
- 文件系统/ASM 优化：
  - ASM: 使用合适的 AU Size (如 4MB 或 16MB 对大文件有益)，确保磁盘组平衡。考虑将临时表空间放在独立的 ASM 磁盘组（使用高性能磁盘如 NVMe）。
  - 避免文件系统碎片。
- 操作系统优化：
  - 选择适合顺序大 I/O 的 I/O Scheduler (如 Linux 的 deadline 或 noop 对 SSD/NVMe)。
  - 验证并优化 filesystemio_options。
  - 确保足够的操作系统 I/O 队列深度。
- 资源管理： 使用 Oracle Resource Manager 或存储 QoS 限制后台批处理作业的 I/O 带宽，避免影响前台 OLTP。限制同一存储上其他应用的 I/O。

关键诊断视图总结

整体/历史： V$SYSTEM_EVENT, DBA_HIST_SYSTEM_EVENT, AWR/ASH 报告
会话级实时： V$SESSION, V$SESSION_WAIT, V$SESSION_EVENT
会话级历史： V$ACTIVE_SESSION_HISTORY, DBA_HIST_ACTIVE_SESS_HISTORY (关注 SQL_PLAN_OPERATION, SQL_PLAN_OPTIONS)
SQL 级： V$SQL, V$SQLSTATS, V$SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL, DBA_HIST_SQLSTAT, DBA_HIST_SQLTEXT
对象/文件级： V$ACTIVE_SESSION_HISTORY (CURRENT_OBJ#, CURRENT_FILE#), DBA_OBJECTS, DBA_SEGMENTS, V$SEGMENT_STATISTICS, DBA_HIST_SEG_STAT
I/O 统计： V$FILESTAT, V$TEMPSTAT, V$IOSTAT_FILE, V$IOSTAT_FUNCTION (核心！), AWR 的 IOStat 部分
PGA 统计： V$PGASTAT, V$PROCESS (PGA_USED_MEM, PGA_ALLOC_MEM, PGA_MAX_MEM), AWR 的 PGA 部分
参数： V$PARAMETER, V$SYSTEM_PARAMETER, X$KSPPI, X$KSPPCV (查隐含参数)

重要提示

direct path read 不一定是坏事： 它是处理大数据集（尤其是全表扫描和并行查询）的高效且推荐的方式。不要盲目地试图消除它，而要优化其执行效率和减少不必要的触发。
区分 direct path read 和 direct path read temp： 前者读数据文件（通常 FTS/IFFS/PQ），后者读临时文件（排序/哈希溢出）。优化策略不同。
Avg Wait (ms) 是关键瓶颈指示器： 如果这个值很高，优化 SQL 本身可能收效甚微，必须解决存储 I/O 瓶颈。
并行查询依赖直接路径读： 优化并行查询通常意味着优化其底层的 direct path read。
对比 db file scattered read： 传统的全表扫描走 Buffer Cache 会产生 db file scattered read。direct path read 是它的现代替代（绕过 Buffer Cache）。如果看到 db file scattered read 很高，可能意味着 _serial_direct_read=NEVER 或对象很小。
基准测试： 任何存储配置、参数或 SQL 的重大变更，前后都应进行基准测试。
关注资源平衡： 增加并行度或提高 I/O 吞吐可能会消耗更多 CPU 和内存资源。需要整体评估系统容量。