面试宝典:介绍下Oracle数据库动态性能视图 V$SQL_PLAN_MONITOR

好的，我将为您全面、深入地解析 Oracle 19C 数据库中的 V$SQL_PLAN_MONITOR 动态性能视图。

这是 SQL 监控（SQL Monitor）功能的核心视图之一，它提供了被监控 SQL 语句执行计划中每一个操作步骤的详细运行时统计信息，是进行精细化 SQL 性能诊断的终极工具。

🔍 Oracle 19C V$SQL_PLAN_MONITOR 动态性能视图详解

1️⃣ 视图概述与核心作用

V$SQL_PLAN_MONITOR 动态性能视图是 V$SQL_MONITOR 的补充和细化。如果说 V$SQL_MONITOR 提供了一次 SQL 执行的“总体报告”，那么 V$SQL_PLAN_MONITOR 则提供了这份报告的 “详细附录” ，它深入到执行计划的每个操作步骤（如 HASH JOIN, TABLE ACCESS），并记录其实际运行时的性能指标。

该视图的核心作用在于：

1. 执行计划步骤级诊断：精确识别出复杂执行计划中哪个具体操作步骤是性能瓶颈（消耗了最多时间、产生了最多 I/O）。
2. 验证优化器估算：将优化器的估算（CARDINALITY, BYTES）与实际运行结果（OUTPUT_ROWS, ACTUAL_ROWS）进行对比，快速发现估算错误，这是许多性能问题的根源。
3. 分析并行执行：展示每个并行服务器进程（PX Server）在处理每个计划步骤时的详细工作量，用于诊断并行执行倾斜（Parallel Execution Skew）问题。
4. 提供无可辩驳的证据：为 SQL 调优提供基于实际运行数据的、步骤级的证据，而不是基于猜测。

2️⃣ 字段详细含义

V$SQL_PLAN_MONITOR 的字段可以看作是 V$SQL_PLAN (计划结构) 和 V$SQL_PLAN_STATISTICS (执行统计) 在 SQL Monitor 框架下的结合与增强。下表详细解释了其关键字段：

标识与关联字段

字段名称	数据类型	含义说明
KEY	NUMBER	监控条目的唯一标识符。与 V$SQL_MONITOR.KEY 完全一致，是连接这两个视图的主要键。
SQL_ID	VARCHAR2(13)	被监控的 SQL 语句的 SQL_ID。
SQL_EXEC_ID	NUMBER	SQL 执行标识符。唯一标识一次具体的SQL执行。与 V$SQL_MONITOR.SQL_EXEC_ID 对应。
SQL_EXEC_START	DATE	该次 SQL 执行开始的准确时间。与 V$SQL_MONITOR.SQL_EXEC_START 对应。
PLAN_LINE_ID	NUMBER	执行计划中的步骤行ID。此字段与 V$SQL_PLAN.ID 语义相同，唯一标识一个执行计划中的操作步骤。
PLAN_OPERATION	VARCHAR2(64)	计划步骤的操作类型。与 V$SQL_PLAN.OPERATION 相同（例如：HASH JOIN, TABLE ACCESS）。
PLAN_OPTIONS	VARCHAR2(64)	计划步骤的操作选项。与 V$SQL_PLAN.OPTIONS 相同（例如：FULL, RANGE SCAN）。
OBJECT_NAME	VARCHAR2(128)	操作所涉及的数据库对象名称（表名、索引名）。

实际运行时统计字段（核心价值）

字段名称	数据类型	含义说明
OUTPUT_ROWS	NUMBER	该操作步骤实际输出的行数。这是最关键的字段之一，用于与优化器估算的 CARDINALITY 进行对比。如果差异巨大，说明统计信息或估算模型有问题。
ACTUAL_ROWS	NUMBER	该操作步骤实际处理的行数。对于 TABLE ACCESS，这可能与 OUTPUT_ROWS 相同；对于有过滤条件的操作，它可能更大。
PHYSICAL_READ_BYTES	NUMBER	该操作步骤导致的物理读取字节数。精确地将物理 I/O 开销归属到具体的计划步骤。
PHYSICAL_WRITE_BYTES	NUMBER	该操作步骤导致的物理写入字节数。
PHYSICAL_READ_REQUESTS	NUMBER	该操作步骤发起的物理读请求次数。
IO_INTERCONNECT_BYTES	NUMBER	该操作步骤通过存储网络传输的总字节数（包含缓存）。比物理读字节数更能反映真实的 I/O 负载。
ELAPSED_TIME	NUMBER	该操作步骤消耗的总时间（微秒）。用于识别最耗时的步骤。
CPU_TIME	NUMBER	该操作步骤消耗的 CPU 时间（微秒）。
QUEUING_TIME	NUMBER	该操作步骤在队列中等待的时间（微秒）（例如，并行执行中的等待）。
TEMP_SPACE	NUMBER	该操作步骤使用的临时表空间大小（字节）。对于排序、哈希连接等操作非常重要。

并行执行相关字段

字段名称	数据类型	含义说明
PROCESS_NAME	VARCHAR2(64)	执行该步骤的进程名称。对于并行查询，这里显示的是并行服务器进程的名称（如 p000, p001）。对于串行执行，可能是服务器进程或后台进程名。
CON_ID	NUMBER	容器 ID。在多租户环境（CDB）中，标识该行信息所属的容器。

3️⃣ 相关视图与基表

3.1 核心相关视图

• V$SQL_MONITOR：这是最直接的父视图。V$SQL_PLAN_MONITOR 提供的是 V$SQL_MONITOR 中某次SQL执行的详细计划步骤分解。两者通过 KEY (或 SQL_ID, SQL_EXEC_ID, SQL_EXEC_START) 字段进行关联。

  -- 关联查询：获取SQL总体信息及每个步骤的详情
  SELECT sm.sql_text, smpm.plan_operation, smpm.plan_options,
         smpm.object_name, smpm.output_rows, smpm.physical_read_bytes
  FROM v$sql_monitor sm
  JOIN v$sql_plan_monitor smpm ON sm.key = smpm.key
  WHERE sm.sql_id = '&sql_id';
  

• V$SQL_PLAN：存储执行计划的结构信息。V$SQL_PLAN_MONITOR.PLAN_LINE_ID 与 V$SQL_PLAN.ID 对应。可以关联查询以获得优化器的估算值（如 CARDINALITY），并与 V$SQL_PLAN_MONITOR 中的实际值（如 OUTPUT_ROWS）进行对比。

• V$SQL_MONITOR_SESSTAT：提供会话级的统计信息增量。而 V$SQL_PLAN_MONITOR 提供计划步骤级的统计信息。两者从不同维度分解SQL执行的资源消耗。

• DBA_HIST_SQL_PLAN_MONITOR：AWR 历史快照中的对应视图，用于对过去的 SQL 计划监控记录进行历史回溯分析。

3.2 底层基表与原理

与 V$SQL_MONITOR 一样，V$SQL_PLAN_MONITOR 也是一个动态性能视图，其数据直接来源于 SGA 中为 SQL 监控功能分配的实时内存数据结构。

1. 内存中的步骤级跟踪：当一条 SQL 语句被监控时，Oracle 会为其执行计划中的每一个操作步骤初始化一个监控结构，用于持续收集该步骤的详细运行时统计信息。
2. 数据记录与更新：在执行过程中，每个操作步骤在执行时都会更新其对应的监控结构中的计数器（如处理的行数、消耗的时间、产生的I/O）。
3. 视图投影：V$SQL_PLAN_MONITOR 视图是这些内存中、步骤级监控数据的直接对外接口。

其底层基表极有可能是 X$QESPMON 或类似名称的 X$ 表。同样，严禁直接查询 X$ 表。

4️⃣ 底层原理与机制

4.1 步骤级监控的实现

SQL Monitor 基础设施在运行时对执行引擎进行了高度 Instrumentation（植入）：

1. 执行引擎钩子：执行计划中的每一个操作符（Operator）都包含了额外的代码钩子。当一个操作符开始处理数据、处理完一行数据或结束处理时，这些钩子函数会被触发。
2. 数据收集：这些钩子函数负责收集诸如已处理行数、耗时等信息，并更新到该操作符对应的监控数据结构中。
3. 低开销设计：这种植入被设计为低开销。它不会记录每一行数据，而是进行高效的计数和采样，以确保监控功能本身不会对SQL性能产生显著影响。

4.2 实际行数 vs. 估算行数

这是该视图提供的最具价值的洞察。

• V$SQL_PLAN.CARDINALITY：优化器估算的该步骤输出行数。基于统计信息、假设和成本模型。
• V$SQL_PLAN_MONITOR.OUTPUT_ROWS：该步骤实际输出的行数。

诊断模式：

-- 查找优化器估算严重失准的计划步骤
SELECT p.id, p.operation, p.options, p.object_name,
       p.cardinality AS estimated_rows,
       m.output_rows AS actual_rows,
       ROUND(m.output_rows / NULLIF(p.cardinality, 0), 2) AS ratio
FROM v$sql_plan p, v$sql_plan_monitor m
WHERE p.sql_id = m.sql_id
AND p.id = m.plan_line_id
AND p.child_number = 0 -- 假设子游标号
AND m.sql_id = '&sql_id'
AND m.output_rows > 0
ORDER BY ABS(m.output_rows - p.cardinality) DESC;

如果 ratio 偏离 1 很远（例如，大于 10 或小于 0.1），则表明优化器对该步骤的基数估算错误，这很可能是导致次优计划（如错误选择连接方式或连接顺序）的根本原因。

4.3 并行执行监控

对于并行查询，V$SQL_PLAN_MONITOR 的强大之处在于它能为每个并行服务器进程（PX Server）在每个计划步骤上的工作都生成一条记录。

诊断模式：

-- 检查并行执行是否存在倾斜 (Parallel Execution Skew)
SELECT plan_operation, plan_options, process_name,
       output_rows, physical_read_bytes, elapsed_time
FROM v$sql_plan_monitor
WHERE sql_id = '&sql_id'
AND plan_operation = 'PX BLOCK ITERATOR' -- 或其他关键操作
ORDER BY plan_line_id, process_name;

如果同一个操作（PLAN_LINE_ID）下，不同 PROCESS_NAME（如p000, p001）的 OUTPUT_ROWS 或 ELAPSED_TIME 差异巨大，就表明发生了并行执行倾斜，某些进程干了大部分的活，而其他进程很空闲。这指向数据分布问题（例如，缺少分区）或并行配置问题。

5️⃣ 常用查询 SQL

以下是一些用于深度分析计划步骤性能的实用查询。

1. 查看一次SQL执行中耗时最多的计划步骤

   SELECT plan_line_id, plan_operation, plan_options, object_name,
          ROUND(elapsed_time / 1000000, 2) AS elapsed_sec,
          output_rows,
          ROUND(physical_read_bytes / 1024 / 1024, 2) AS physical_read_mb
   FROM v$sql_plan_monitor
   WHERE sql_id = '&sql_id'
     AND sql_exec_id = &sql_exec_id
   ORDER BY elapsed_time DESC;
   

2. 识别优化器估算错误最严重的步骤

   SELECT m.plan_line_id, m.plan_operation, m.plan_options, m.object_name,
          p.cardinality AS estimated,
          m.output_rows AS actual,
          CASE WHEN p.cardinality = 0 THEN NULL
               ELSE ROUND(m.output_rows / p.cardinality, 2)
          END AS est_actual_ratio
   FROM v$sql_plan_monitor m
   JOIN v$sql_plan p ON m.sql_id = p.sql_id AND m.plan_line_id = p.id
   WHERE m.sql_id = '&sql_id'
     AND p.child_number = &child_number
     AND p.cardinality > 0
   ORDER BY ABS(m.output_rows - p.cardinality) DESC;
   

3. 分析并行操作的执行倾斜情况

   SELECT plan_line_id, plan_operation, plan_options,
          process_name,
          output_rows,
          ROUND(elapsed_time / 1000000, 2) AS elapsed_sec
   FROM v$sql_plan_monitor
   WHERE sql_id = '&sql_id'
     AND sql_exec_id = &sql_exec_id
     AND process_name LIKE 'p%' -- 筛选出并行服务器进程
   ORDER BY plan_line_id, output_rows DESC;
   

6️⃣ 使用场景与总结

6.1 典型使用场景

1. 深度性能瓶颈分析：当 V$SQL_MONITOR 显示某条SQL总体很慢时，使用此视图快速定位到是执行计划中的哪一个具体操作（如一个巨大的哈希连接、一个全表扫描）消耗了绝大部分时间和资源。
2. 优化器估算问题诊断：这是解决“为什么优化器选择了这个糟糕计划”问题的黄金工具。通过对比估算和实际行数，直接证明统计信息陈旧、直方图缺失或复杂度导致的估算错误。
3. 并行查询调优：诊断和解决并行执行倾斜问题，确保并行查询能够充分利用所有硬件资源。
4. 验证优化效果：在对SQL进行优化（如添加索引、刷新统计信息）后，不仅比较总时间，更比较优化前后关键计划步骤的实际行数和I/O变化，获得更精确的优化验证。

6.2 重要总结与最佳实践

• V$SQL_PLAN_MONITOR 是 SQL Monitor 功能的“显微镜”，它将监控粒度从语句级细化到了执行计划步骤级。
• 其核心价值在于提供实际运行时统计信息（如 OUTPUT_ROWS），并与 V$SQL_PLAN 中的优化器估算进行对比，从而揭示优化器决策错误的根本原因。
• 它是诊断并行执行倾斜不可或缺的工具。
• 虽然可以直接查询此视图，但最高效的方式仍然是使用 DBMS_SQLTUNE.REPORT_SQL_MONITOR 生成HTML报告。该报告以图形化方式完美呈现了 V$SQL_PLAN_MONITOR 中的细节数据，并直接标注出估算与实际的差异。
• 数据存在于内存中，对于重要的分析结果，应及时通过报告或查询结果进行保存。

通过掌握 V$SQL_PLAN_MONITOR 视图，您将能从本质上理解SQL性能问题的根源，从而进行精准、高效的调优，从“猜测”走向“数据驱动的诊断”。

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