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

原创于 2025-09-04 12:34:33 发布 · 783 阅读

26 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

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

584 篇文章

订阅专栏

在这里插入图片描述好的，我将为您全面、深入地解析 Oracle 19C 数据库中的 V$SQL_PLAN 动态性能视图。这是 Oracle SQL 性能调优中最核心、最常用的视图之一，它提供了存储在库缓存（Library Cache）中的 SQL 语句的执行计划信息。

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

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

V$SQL_PLAN 动态性能视图提供了存储在共享池（Library Cache）中的所有 SQL 游标的执行计划信息。它包含了优化器（CBO）为每条 SQL 语句生成的执行计划的详细步骤。

您可以将其视为 EXPLAIN PLAN FOR 命令的动态版本。但与 EXPLAIN PLAN 只是预测不同，V$SQL_PLAN 显示的是实际被编译、存储在共享池中并被执行的真实计划。

该视图的核心作用在于：

诊断当前性能问题：查看正在消耗资源的 SQL 的实际执行计划，判断其是否高效（如是否全表扫描、是否使用了错误的索引）。
分析历史执行计划：无需重现SQL即可查看其过去使用的执行计划。
比较计划变更：当SQL性能突然下降时，比较不同子游标（CHILD_NUMBER）的执行计划，定位计划回归（Plan Regression）问题。
理解优化器行为：深入研究复杂SQL的执行路径，理解优化器选择的连接顺序、连接方法和访问路径。

2️⃣ 字段详细含义

V$SQL_PLAN 包含大量字段，完整描述了执行计划的每一个步骤（操作）。以下表格对其关键字段进行了分类和详解：

标识与关系字段

字段名称	数据类型	含义说明
ADDRESS	RAW(8)	父游标在库缓存中的内存地址。与 `V$SQL.ADDRESS` 关联，是标识游标的关键字段之一。
HASH_VALUE	NUMBER	父游标的哈希值。旧版本中用于标识游标，在10g及以后，与 `SQL_ID` 和 `ADDRESS` 共同用于精确标识。
SQL_ID	VARCHAR2(13)	SQL 语句的唯一标识符。这是与现代Oracle视图（如`V$SQL`）关联的主要键。
PLAN_HASH_VALUE	NUMBER	执行计划的哈希值。唯一标识一个执行计划。同一`SQL_ID`的不同`CHILD_NUMBER`可能拥有相同的`PLAN_HASH_VALUE`，意味着它们共享同一计划。
CHILD_ADDRESS	RAW(8)	子游标在库缓存中的内存地址。与 `V$SQL.CHILD_ADDRESS` 关联。
CHILD_NUMBER	NUMBER	子游标编号。与 `SQL_ID` 共同唯一标识一个具体的子游标。不同子游标可能对应不同的执行计划。
ID	NUMBER	计划步骤的唯一标识符。在执行计划中，`ID` 是唯一的。
PARENT_ID	NUMBER	父步骤的ID。此字段建立了执行计划的树形层次结构。根操作（通常是`SELECT STATEMENT`）的 `PARENT_ID` 为 `NULL`。

操作信息字段

字段名称	数据类型	含义说明
OPERATION	VARCHAR2(30)	执行计划步骤的操作类型。这是描述该步骤做什么的核心字段。例如：`TABLE ACCESS`, `INDEX`, `HASH JOIN`, `SORT`。
OPTIONS	VARCHAR2(30)	对操作的补充说明。描述操作如何做。例如：`FULL`（全表扫描）, `RANGE SCAN`（索引范围扫描）, `UNIQUE`（唯一扫描）。
OBJECT#	NUMBER	操作所涉及的数据对象的对象号。可与 `DBA_OBJECTS.OBJECT_ID` 关联，以获取对象名。
OBJECT_OWNER	VARCHAR2(30)	对象的所有者。
OBJECT_NAME	VARCHAR2(30)	对象的名称。例如：表名、索引名。对于非对象操作（如`HASH JOIN`），此字段为 `NULL`。
OBJECT_TYPE	VARCHAR2(30)	对象的类型。例如：`TABLE`, `INDEX`。

优化器估算字段（可能不准确）

字段名称	数据类型	含义说明
CARDINALITY	NUMBER	优化器估算的该操作返回的行数。这是评估优化器估算准确性的关键字段。如果与实际行数差异巨大，往往是性能问题的根源。
BYTES	NUMBER	优化器估算的该操作返回数据的总字节数。
COST	NUMBER	优化器估算的该操作的相对成本值。用于比较不同计划的成本。
CPU_COST	NUMBER	优化器估算的该操作所需的CPU成本。
IO_COST	NUMBER	优化器估算的该操作所需的I/O成本。
TIME	NUMBER	优化器估算的该操作所需的时间（单位：十分之一秒）。

其他信息字段

字段名称	数据类型	含义说明
DEPTH	NUMBER	该步骤在计划树中的深度。根节点深度为0。
POSITION	NUMBER	在同一父步骤下的兄弟步骤中的执行顺序。
ACCESS_PREDICATES	VARCHAR2(4000)	访问谓词。用于从存储结构中定位和提取数据的条件（例如，索引访问中的 `WHERE id = 123`）。
FILTER_PREDICATES	VARCHAR2(4000)	过滤谓词。在数据被访问后，用于过滤数据的条件（例如，在表访问后检查 `WHERE name LIKE 'A%'`）。
PARTITION_START	VARCHAR2(64)	分区修剪的起始分区。
PARTITION_STOP	VARCHAR2(64)	分区修剪的结束分区。
CON_ID	NUMBER	容器ID。在多租户环境（CDB）中，标识该行信息所属的容器。

3️⃣ 相关视图与基表

3.1 核心相关视图

V$SQL / V$SQLAREA：这是最直接相关的视图。V$SQL_PLAN 中的 SQL_ID 和 CHILD_NUMBER 直接来源于此。它提供了SQL语句的文本、执行统计信息等。关联查询是标准做法。

SELECT s.sql_text, p.operation, p.options, p.object_name, p.cost
FROM v$sql s, v$sql_plan p
WHERE s.sql_id = p.sql_id
AND s.child_number = p.child_number
AND s.sql_id = '&sql_id';

V$SQL_PLAN_STATISTICS：极其重要。如果设置了 STATISTICS_LEVEL=ALL，此视图会提供执行计划中每一步的实际运行时统计信息（实际行数、实际时间等），与 V$SQL_PLAN 中的估算值形成对比。通过 DBMS_XPLAN.DISPLAY_CURSOR 可以完美地将两者结合展示。
V$SQL_PLAN_STATISTICS_ALL：结合了 V$SQL_PLAN 的估算信息和 V$SQL_PLAN_STATISTICS 的实际信息，并增加了内存使用情况，是信息最全的视图。
V$SQL_OPTIMIZER_ENV：提供了生成该执行计划时优化器所使用的参数环境（如 optimizer_mode），对于理解优化器为何选择此计划至关重要。

3.2 底层基表与原理

V$SQL_PLAN 的数据直接来源于 库缓存（Library Cache） 中存储的游标对象。

游标与执行计划：当一个SQL语句被硬解析后，其执行计划被编译并存储在库缓存中，作为游标（Cursor）对象的一部分。
内存结构投影：V$SQL_PLAN 视图本质上是这些库缓存中游标对象内部结构的外部只读投影。它通过底层X $表（如 ‘ X$ KGLCURSOR_CHILD和存储计划信息的X$KGLCURSOR_CHILD_SUBHEAP`）来访问这些数据。
动态性：视图的内容是动态的，随着游标被老化（aged out）出共享池而消失。
历史数据：对于历史分析，需要查询AWR表中的相应视图：DBA_HIST_SQL_PLAN。该表是 V$SQL_PLAN 的AWR快照版本。

因此，V$SQL_PLAN 是库缓存中游标执行计划信息的外部接口。

4️⃣ 底层原理与机制

4.1 执行计划的存储与检索

解析与生成：当发生硬解析时，优化器生成执行计划。这个计划是一个复杂的树形结构，每个节点代表一个操作（OPERATION）。
游标创建：该计划与SQL文本、执行环境等信息一起被打包成一个子游标（Child Cursor） 对象，并存储在库缓存中。
视图访问：当查询 V$SQL_PLAN 时，Oracle 数据库引擎会访问库缓存中的游标结构，将其中的计划信息提取出来，并以表格形式呈现。ID 和 PARENT_ID 字段用于在二维表中重建这个树形结构。

4.2 执行计划的树形结构

理解执行计划的关键是理解其树形结构和执行顺序：

树形结构：计划是从下往上、从内往外阅读的。最内层、最底层的操作（叶子节点）先执行，其输出（结果集）传递给父操作，依此类推，直到根操作（通常是 SELECT STATEMENT）产生最终结果。
执行顺序：通过 ID 和 PARENT_ID 可以构建出这棵树。兄弟操作（拥有相同 PARENT_ID）的执行顺序由 POSITION 决定（通常从左到右）。

示例计划树：

ID | OPERATION
--------------------
0 | SELECT STATEMENT
1 |  HASH JOIN
2 |   TABLE ACCESS FULL EMPLOYEES
3 |   TABLE ACCESS FULL DEPARTMENTS

ID=2 和 ID=3 是 ID=1 的子节点。
执行顺序：先执行 ID=2（全表扫描EMPLOYEES），然后执行 ID=3（全表扫描DEPARTMENTS），最后将两者的结果传递给 ID=1 进行哈希连接。

4.3 子游标与计划版本化

同一个 SQL_ID 可以有多个 CHILD_NUMBER，每个子游标都可以有一个不同的执行计划（不同的 PLAN_HASH_VALUE）。这称为计划版本化。发生的原因包括：

绑定变量窥视（Bind Peeking）导致不同的值产生不同的计划。
系统统计信息或对象统计信息发生变化。
会话级别的优化器参数被修改。
SQL计划基线（SQL Plan Baselines）或SQL配置文件（SQL Profiles）被添加。

V$SQL_PLAN 允许你查看每一个子游标所对应的具体计划。

5️⃣ 常用查询 SQL

以下是一些用于分析执行计划的实用查询。

查看特定SQL语句的完整执行计划（经典树形显示）

SELECT id, parent_id, 
       LPAD(' ', depth*2, ' ') || operation || ' ' || options AS operation,
       object_name, cardinality, cost
FROM v$sql_plan
WHERE sql_id = '&sql_id'
AND child_number = &child_number
START WITH id = 0
CONNECT BY PRIOR id = parent_id;

查找执行全表扫描的SQL语句

SELECT s.sql_id, s.sql_text, p.object_name
FROM v$sql s, v$sql_plan p
WHERE s.sql_id = p.sql_id
AND s.child_number = p.child_number
AND p.operation = 'TABLE ACCESS'
AND p.options = 'FULL'
AND p.object_owner = '&schema_name';

比较两个子游标的执行计划差异

-- 查看同一个SQL不同子游标的计划是否相同
SELECT PLAN_HASH_VALUE, CHILD_NUMBER, COUNT(*)
FROM V$SQL_PLAN
WHERE SQL_ID = '&sql_id'
GROUP BY PLAN_HASH_VALUE, CHILD_NUMBER
ORDER BY CHILD_NUMBER;

获取执行计划的详细信息（结合V$SQL）

SELECT p.operation, p.options, p.object_name, p.cost, p.cardinality,
       s.executions, s.elapsed_time, s.buffer_gets
FROM v$sql_plan p, v$sql s
WHERE p.sql_id = s.sql_id
AND p.child_number = s.child_number
AND p.sql_id = '&sql_id'
AND p.id = 0; -- ID=0 通常是SELECT STATEMENT，关联SQL的总体信息

6️⃣ 使用场景与总结

6.1 典型使用场景

即时性能诊断：当应用报告性能缓慢时，通过 V$SQL 找到高负载的 SQL_ID，然后立即在 V$SQL_PLAN 中查看其执行计划，判断是否存在低效操作（如全表扫描、错误的连接方式）。
计划稳定性监控：定期检查关键业务SQL的 PLAN_HASH_VALUE 是否发生变化，预防因计划变更导致的性能波动。
索引优化：通过检查执行计划的访问路径，判断是否缺少索引或现有索引未被使用。
连接顺序分析：对于多表关联的复杂SQL，分析优化器选择的连接顺序和连接方法是否最优。

6.2 重要总结与最佳实践

V$SQL_PLAN 是 分析真实执行计划的权威来源，它显示的是库缓存中实际存在的计划。
始终与 V$SQL 关联查询以获取SQL文本和执行统计信息。
理解树形结构是解读执行计划的基础。使用 CONNECT BY 查询或 DBMS_XPLAN.DISPLAY_CURSOR 来格式化输出。
牢记 CARDINALITY 等字段是估算值。要获取实际值，需要与 V$SQL_PLAN_STATISTICS 对比或使用 STATISTICS_LEVEL=ALL。
对于历史分析，请查询 DBA_HIST_SQL_PLAN。
官方推荐：使用 DBMS_XPLAN.DISPLAY_CURSOR('sql_id', child_number) 来获取格式良好、信息最全的执行计划显示，它自动关联了多个V$视图。