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

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

这个视图是 SQL 性能调优的"真相之源"，它揭示了 SQL 语句执行过程中，执行计划里每一个操作步骤（如 HASH JOIN, TABLE ACCESS）的实际运行时表现，使我们能够将优化器的估算与残酷的现实进行对比。

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

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

V$SQL_PLAN_STATISTICS 动态性能视图提供了存储在库缓存（Library Cache）中的 SQL 游标的执行计划统计信息。最关键的是，它提供了执行计划中每个操作步骤的实际运行时统计信息（如果统计信息收集被启用的话）。

您可以将其视为 V$SQL_PLAN（存储估算值） 和 V$SQL_PLAN_MONITOR（提供监控数据） 的中间形态。它比 V$SQL_PLAN 更接近现实，但不像 V$SQL_PLAN_MONITOR 那样需要满足特定条件才自动触发。

该视图的核心作用在于：

1. 验证优化器估算：这是其最重要的功能。将优化器估算的成本（Cost）、返回行数（Cardinality）与实际执行的成本、返回行数进行对比，精准定位优化器估算错误。
2. 识别性能瓶颈：确定复杂执行计划中哪个具体操作步骤消耗了最多的 I/O、CPU 或时间。
3. 提供调优证据：为创建索引、刷新统计信息、使用 Hint 等调优手段提供数据支撑，证明为何需要调整。
4. 历史分析：与 V$SQL_PLAN 一样，它存储于共享池中，可用于分析近期执行过的SQL的性能。

2️⃣ 字段详细含义

V$SQL_PLAN_STATISTICS 的字段结构是 V$SQL_PLAN 的子集，但包含了关键的实际值字段。以下表格详细解释了其所有字段：

标识与关联字段

字段名称	数据类型	含义说明
ADDRESS	RAW(8)	父游标在库缓存中的内存地址。与 V$SQL.ADDRESS 关联。
HASH_VALUE	NUMBER	父游标的哈希值。用于标识游标。
SQL_ID	VARCHAR2(13)	SQL 语句的唯一标识符。这是与现代Oracle视图关联的主要键。
PLAN_HASH_VALUE	NUMBER	执行计划的哈希值。
CHILD_ADDRESS	RAW(8)	子游标在库缓存中的内存地址。
CHILD_NUMBER	NUMBER	子游标编号。与 SQL_ID 共同唯一标识一个具体的子游标。
ID	NUMBER	计划步骤的唯一标识符。与 V$SQL_PLAN.ID 完全一致，是连接这两个视图的键。

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

字段名称	数据类型	含义说明
EXECUTIONS	NUMBER	该操作步骤被执行的总次数。对于非迭代式操作，通常为1。对于循环内的嵌套循环（NESTED LOOPS）访问，次数会很多。
LAST_OUTPUT_ROWS	NUMBER	该操作步骤在最后一次执行中实际输出的行数。
LAST_CR_BUFFER_GETS	NUMBER	该操作步骤在最后一次执行中完成的一致性读（逻辑读）次数。
LAST_CU_BUFFER_GETS	NUMBER	该操作步骤在最后一次执行中完成的当前读（逻辑读）次数。
LAST_DISK_READS	NUMBER	该操作步骤在最后一次执行中导致的物理读次数。
LAST_DISK_WRITES	NUMBER	该操作步骤在最后一次执行中导致的物理写次数。
LAST_ELAPSED_TIME	NUMBER	该操作步骤在最后一次执行中消耗的总时间（微秒）。

注意：所有以 LAST_ 开头的字段都代表该操作步骤在最后一次执行中的统计信息。如果游标被多次执行，这些值不会被累计，它们只反映最后一次执行的情况。

3️⃣ 相关视图与基表

3.1 核心相关视图

• V$SQL_PLAN：这是最必须关联的视图。V$SQL_PLAN_STATISTICS 通过 (ADDRESS, HASH_VALUE, CHILD_ADDRESS, ID) 或 (SQL_ID, CHILD_NUMBER, ID) 与 V$SQL_PLAN 关联。关联后才能获得操作类型、对象名、以及优化器的估算值，从而进行对比。

  -- 标准关联查询：获取计划结构 + 实际执行统计
  SELECT p.id, p.operation, p.options, p.object_name,
         p.cardinality AS estimated_rows, -- 优化器估算的行数
         s.last_output_rows AS actual_rows, -- 实际输出的行数
         p.cost AS estimated_cost, -- 优化器估算的成本
         s.last_elapsed_time AS actual_time -- 实际消耗的时间
  FROM v$sql_plan p, v$sql_plan_statistics s
  WHERE p.sql_id = s.sql_id
  AND p.child_number = s.child_number
  AND p.id = s.id
  AND p.sql_id = '&sql_id';
  

• V$SQL_PLAN_STATISTICS_ALL：这是一个扩展视图，它包含了 V$SQL_PLAN、V$SQL_PLAN_STATISTICS 的所有字段，还增加了内存使用情况（如 LAST_MEMORY_USED）。在大多数情况下，直接查询此视图比分别关联 V$SQL_PLAN 和 V$SQL_PLAN_STATISTICS 更为方便和高效。

  -- 使用ALL视图更方便地获取所有信息
  SELECT id, operation, options, object_name,
         cardinality AS est_rows,
         last_output_rows AS act_rows,
         last_cr_buffer_gets AS consistent_gets,
         last_disk_reads AS phys_reads
  FROM v$sql_plan_statistics_all
  WHERE sql_id = '&sql_id';
  

• V$SQL：提供SQL语句的总体执行统计信息。通过 SQL_ID 和 CHILD_NUMBER 关联，可以获取SQL文本、总执行次数等信息。

3.2 底层基表与原理

V$SQL_PLAN_STATISTICS 的数据和 V$SQL_PLAN 一样，来源于 库缓存（Library Cache） 中存储的游标对象。

1. 游标结构与执行统计：当一条SQL语句被执行且启用了统计信息收集时，其执行计划中每个操作步骤的运行时统计信息会被记录下来，并存储在游标结构中。
2. 内存结构投影：V$SQL_PLAN_STATISTICS 视图是这些库缓存中游标结构内部统计信息部分的外部只读投影。它通过底层X$表（如‘X$KGLCURSOR_CHILD_STAT`）来访问这些数据。
3. 动态性与开销：收集这些详细的步骤级统计信息会产生一定的开销。因此，它需要显式启用（见下文原理部分）。数据也是动态的，随游标被老化出共享池而消失。

4️⃣ 底层原理与机制

4.1 统计信息收集的开关：STATISTICS_LEVEL

V$SQL_PLAN_STATISTICS 中的数据是否被填充，完全取决于初始化参数 STATISTICS_LEVEL 的设置：

• STATISTICS_LEVEL = ALL：启用所有统计信息的收集，包括计划执行统计信息。这是获取 V$SQL_PLAN_STATISTICS 数据的必要条件。
• STATISTICS_LEVEL = TYPICAL：默认设置。启用大多数对性能诊断关键的管理功能，但不包括收集详细的计划执行统计信息。在此设置下，V$SQL_PLAN_STATISTICS 中的字段通常为 NULL 或 0。
• STATISTICS_LEVEL = BASIC：禁用几乎所有统计信息收集。

因此，要使用此视图，必须在系统级或会话级将 STATISTICS_LEVEL 设置为 ALL。

ALTER SESSION SET statistics_level = ALL;
-- 然后执行你的SQL语句
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM my_big_table WHERE ...;

GATHER_PLAN_STATISTICS Hint 可以在语句级别覆盖 STATISTICS_LEVEL 设置，强制为该条SQL收集详细的计划统计信息。

4.2 数据收集机制

一旦启用，执行引擎会为计划中的每个步骤进行仪器化（Instrumentation）：

1. 步骤执行开始时：记录开始时间戳，初始化计数器。
2. 步骤执行过程中：累计各种资源消耗（逻辑读、物理读、处理行数）。
3. 步骤执行结束时：记录结束时间，计算总耗时，并将最终统计信息更新到游标的内存结构中。
4. 游标关闭时：LAST_* 字段被更新为最后一次执行的统计信息。

4.3 与 SQL Monitor 的区别

这是一个常见的困惑点：

特性	V$SQL_PLAN_STATISTICS	V$SQL_PLAN_MONITOR
触发机制	需要显式启用（STATISTICS_LEVEL=ALL）。	自动触发（并行/长耗时SQL）。
开销	相对较低，但需人为启用。	可控，Oracle自动管理。
数据粒度	仅存储最后一次执行的统计信息。	存储每次被监控的执行的详细信息。
实时性	执行完成后才可查看。	近实时，执行中即可查看。
主要用途	主动、针对性地对特定SQL进行深度诊断。	自动捕获和诊断资源密集型SQL。

5️⃣ 常用查询 SQL

以下是一些用于分析计划统计信息的实用查询。

1. 核心诊断：查找优化器估算与实际差异最大的步骤

   SELECT p.id,
          p.operation || ' ' || p.options AS operation,
          p.object_name,
          p.cardinality AS estimated_rows,
          s.last_output_rows AS actual_rows,
          ROUND(s.last_output_rows / NULLIF(p.cardinality, 0), 2) AS ratio,
          s.last_elapsed_time / 1000000 AS actual_sec
   FROM v$sql_plan p, v$sql_plan_statistics s
   WHERE p.sql_id = s.sql_id
   AND p.child_number = s.child_number
   AND p.id = s.id
   AND p.sql_id = '&sql_id'
   AND p.cardinality > 0
   ORDER BY ABS(s.last_output_rows - p.cardinality) DESC;
   

2. 查看执行计划中各步骤的资源消耗（逻辑读、物理读）

   SELECT p.id,
          LPAD(' ', p.depth*2) || p.operation AS operation,
          p.object_name,
          s.last_cr_buffer_gets AS consistent_gets,
          s.last_disk_reads AS physical_reads,
          s.last_elapsed_time / 1000000 AS elapsed_sec
   FROM v$sql_plan p, v$sql_plan_statistics s
   WHERE p.sql_id = s.sql_id
   AND p.child_number = s.child_number
   AND p.id = s.id
   AND p.sql_id = '&sql_id'
   ORDER BY p.id;
   

3. 使用更方便的 V$SQL_PLAN_STATISTICS_ALL 视图

   SELECT id, operation, options, object_name,
          cardinality AS est_rows,
          last_output_rows AS act_rows,
          last_cr_buffer_gets AS cons_gets,
          last_disk_reads AS phys_reads,
          last_elapsed_time / 1000000 AS elapsed_sec
   FROM v$sql_plan_statistics_all
   WHERE sql_id = '&sql_id'
   AND child_number = &child_num
   ORDER BY id;
   

6️⃣ 使用场景与总结

6.1 典型使用场景

1. 主动性能调查：对某个已知的慢SQL，在测试环境中设置 STATISTICS_LEVEL=ALL 并执行，然后立即查询此视图，精确分析每个步骤的实际情况与估算的差异。
2. 索引优化论证：通过证明某个 TABLE ACCESS FULL 步骤的实际行数远高于估算，且消耗大量逻辑读，来强有力的论证创建索引的必要性。
3. 连接方法评估：判断优化器选择的连接方法（如HASH JOIN）是否真的高效，通过检查LAST_OUTPUT_ROWS和连接本身消耗的LAST_ELAPSED_TIME。
4. 子游标比较：比较同一个 SQL_ID 下不同 CHILD_NUMBER 的执行计划和实际统计信息，分析计划变更是否带来了性能提升。

6.2 重要总结与最佳实践

• V$SQL_PLAN_STATISTICS 是 连接优化器估算（V$SQL_PLAN）和实际运行时性能的桥梁。
• 使用此视图的前提是：STATISTICS_LEVEL=ALL 或使用了 GATHER_PLAN_STATISTICS Hint。
• V$SQL_PLAN_STATISTICS_ALL 视图通常比关联查询 V$SQL_PLAN 和 V$SQL_PLAN_STATISTICS 更实用。
• 对于一次性分析，最快捷的方式是使用 DBMS_XPLAN.DISPLAY_CURSOR 并指定 STATS 格式：

  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_num, 'ALLSTATS LAST'));
  

  该命令会格式化输出，并清晰地将估算值与实际值并排显示，是使用此视图功能的最高效方式。
• 数据存在于内存中，对于重要的分析结果，应及时保存查询输出或执行计划报告。

通过掌握 V$SQL_PLAN_STATISTICS 视图，您将能从“为什么优化器会选这个计划”的猜测，上升到“优化器的估算在哪里出了错，并导致了性能问题”的数据驱动诊断阶段，这是SQL调优工作走向专业化的标志。

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