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

最新推荐文章于 2025-11-24 15:52:13 发布

原创最新推荐文章于 2025-11-24 15:52:13 发布 · 901 阅读

23 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

在这里插入图片描述
好的，我将为您全面、深入地解析 Oracle 19C 数据库中的 V$SQL_PLAN_STATISTICS_ALL 动态性能视图。

这个视图是 Oracle SQL 性能诊断的"终极武器"之一，它在一个视图中集成了执行计划结构、优化器估算和实际运行时统计信息，为深度性能分析提供了前所未有的便利和深度。

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

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

V$SQL_PLAN_STATISTICS_ALL 动态性能视图是一个聚合视图，它无缝地合并了以下三个视图的核心信息：

V$SQL_PLAN：执行计划的结构信息（操作、对象、优化器估算）。
V$SQL_PLAN_STATISTICS：执行计划各步骤的实际运行时统计信息。
内存使用信息：操作步骤执行过程中的额外内存使用情况。

该视图的核心作用在于：

一站式性能分析：无需复杂的多表连接，在一个视图中即可获取从执行计划结构到运行时性能的完整画像。
精准定位估算错误：直接并排对比优化器的估算值（如 CARDINALITY）和实际运行值（如 LAST_OUTPUT_ROWS），这是绝大多数SQL性能问题的根源。
全面资源消耗分析：不仅分析I/O和时间，还能分析每个操作步骤（如排序、哈希连接）的内存使用情况，对于诊断内存密集型操作至关重要。
提升诊断效率：极大简化了获取完整执行计划统计信息的查询复杂度，让DBA能更快速地聚焦于问题本身。

2️⃣ 字段详细含义

V$SQL_PLAN_STATISTICS_ALL 包含了海量字段，我们可以将其分为几个逻辑组来理解。以下表格详细解释了其所有关键字段：

标识与关联字段

字段名称	数据类型	含义说明
SQL_ID	VARCHAR2(13)	SQL 语句的唯一标识符。与 `V$SQL.SQL_ID` 对应。
PLAN_HASH_VALUE	NUMBER	执行计划的哈希值。用于唯一标识一个执行计划。
CHILD_ADDRESS	RAW(8)	子游标在库缓存中的内存地址。
CHILD_NUMBER	NUMBER	子游标编号。与 `SQL_ID` 共同唯一标识一个具体的子游标执行环境。
ID	NUMBER	计划步骤的唯一标识符。与 `V$SQL_PLAN.ID` 完全一致。
PARENT_ID	NUMBER	父步骤的ID。用于构建执行计划的树形层次结构。

执行计划结构字段 (源自 `V$SQL_PLAN`)

字段名称	数据类型	含义说明
OPERATION	VARCHAR2(30)	计划步骤的操作类型。例如：`TABLE ACCESS`, `INDEX`, `HASH JOIN`, `SORT`。
OPTIONS	VARCHAR2(30)	对操作的补充说明。例如：`FULL`, `RANGE SCAN`, `UNIQUE`。
OBJECT#	NUMBER	操作所涉及的数据对象的对象号。
OBJECT_OWNER	VARCHAR2(30)	对象的所有者。
OBJECT_NAME	VARCHAR2(30)	对象的名称（表名、索引名）。
OBJECT_TYPE	VARCHAR2(30)	对象的类型。例如：`TABLE`, `INDEX`。

优化器估算字段 (源自 `V$SQL_PLAN`)

字段名称	数据类型	含义说明
CARDINALITY	NUMBER	优化器估算的该操作返回的行数。
BYTES	NUMBER	优化器估算的该操作返回数据的总字节数。
COST	NUMBER	优化器估算的该操作的相对成本值。
CPU_COST	NUMBER	优化器估算的该操作所需的CPU成本。
IO_COST	NUMBER	优化器估算的该操作所需的I/O成本。
TIME	NUMBER	优化器估算的该操作所需的时间（单位：十分之一秒）。

实际运行时统计字段 (源自 `V$SQL_PLAN_STATISTICS`)

字段名称	数据类型	含义说明
EXECUTIONS	NUMBER	该操作步骤被执行的总次数。
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_MEMORY_USED	NUMBER	该操作步骤在最后一次执行中使用的最大 PGA 内存量（字节）。对于 `SORT`, `HASH JOIN`, `BITMAP` 操作非常重要。
LAST_EXECUTION	VARCHAR2(10)	该操作步骤最后一次执行的状态。例如：`ALL`（执行完成）。
LAST_DEGREE	NUMBER	该操作步骤最后一次执行的并行度。
LAST_TEMPSEG_SIZE	NUMBER	该操作步骤在最后一次执行中使用的临时段大小（字节）。如果操作无法在内存中完成而溢写到磁盘，此值会很大。

注意：所有以 LAST_ 开头的字段都代表该操作步骤在最后一次执行中的统计信息。

3️⃣ 相关视图与基表

3.1 核心相关视图

V$SQL：提供SQL语句的总体执行上下文。通过 SQL_ID 和 CHILD_NUMBER 关联，可以获取SQL文本、总执行次数、总消耗时间等摘要信息，为深入分析计划步骤提供背景。

SELECT s.sql_text, s.executions, s.elaPSed_time, a.*
FROM v$sql s, v$sql_plan_statistics_all a
WHERE s.sql_id = a.sql_id
AND s.child_number = a.child_number
AND s.sql_id = '&sql_id';

V$SQL_PLAN：V$SQL_PLAN_STATISTICS_ALL 是此视图的超集。除非只关心纯计划结构，否则应优先使用 V$SQL_PLAN_STATISTICS_ALL。
V$SQL_PLAN_STATISTICS：此视图的所有字段都已包含在 V$SQL_PLAN_STATISTICS_ALL 中。V$SQL_PLAN_STATISTICS_ALL 是其功能的完全替代和扩展。
V$SQL_PLAN_MONITOR：功能相似但机制不同。V$SQL_PLAN_MONITOR 用于实时监控长时间运行或并行的SQL，且是自动触发的。而 V$SQL_PLAN_STATISTICS_ALL 需要显式启用统计收集（STATISTICS_LEVEL=ALL），并存储最后一次执行的最终结果。

3.2 底层基表与原理

V$SQL_PLAN_STATISTICS_ALL 是一个 V$ 视图，而不是一个基于X$表的底层视图。它的数据来源于对其他底层动态性能视图的查询和组合。

其底层实现可以理解为类似这样的逻辑：

CREATE VIEW v$sql_plan_statistics_all AS
SELECT 
    p.address, p.sql_id, p.child_number, p.id, p.operation, ..., -- V$SQL_PLAN 的字段
    s.last_output_rows, s.last_cr_buffer_gets, ...,             -- V$SQL_PLAN_STATISTICS 的字段
    m.last_memory_used, ...                                    -- 内存相关的字段
FROM 
    x$kglcursor_child_plan p -- 假设的底层X$表
LEFT JOIN 
    x$kglcursor_child_stat s ON (p.* = s.*) -- 假设的连接
LEFT JOIN 
    x$kglcursor_child_mem m ON (p.* = m.*); -- 假设的连接

重要说明：

它本身没有独立的基表，而是一个封装好的、方便的聚合视图。
其数据最终仍然来源于库缓存中游标对象的内存结构。
它的存在体现了Oracle旨在简化DBA工作的设计哲学：提供开箱即用的、功能强大的视图，避免用户编写复杂的多表连接查询。

4️⃣ 底层原理与机制

4.1 统计信息收集的开关

和 V$SQL_PLAN_STATISTICS 一样，V$SQL_PLAN_STATISTICS_ALL 视图中的数据填充依赖于统计信息收集的开启：

必要条件：初始化参数 STATISTICS_LEVEL 必须设置为 ALL。
会话级覆盖：可以在会话级别使用 ALTER SESSION SET statistics_level = ALL;。
语句级覆盖：可以在单条SQL语句中使用 /*+ GATHER_PLAN_STATISTICS */ Hint来强制收集。

如果不满足上述条件，该视图中所有的 LAST_* 字段将为 NULL 或 0，使其退化为一个普通的 V$SQL_PLAN 视图。

4.2 数据流与记录过程

解析与计划生成：发生硬解析，优化器生成执行计划（估算值填入 CARDINALITY, COST 等字段）。
执行与统计收集：SQL语句被执行。由于 STATISTICS_LEVEL=ALL，执行引擎为每个计划步骤注入监控代码，实时收集资源消耗数据。
游标关闭与更新：语句执行完毕，游标关闭时，各个步骤收集到的最终统计信息（最后一次执行的数据）被更新到游标在库缓存中的数据结构里。
视图查询：当查询 V$SQL_PLAN_STATISTICS_ALL 时，Oracle从库缓存中取出计划结构、统计信息和内存数据，合并后返回给用户。

4.3 与 SQL Monitor 的对比

理解这两种技术的适用场景至关重要：

特性	`V$SQL_PLAN_STATISTICS_ALL`	`V$SQL_PLAN_MONITOR` (SQL Monitor)
触发机制	显式（需设置 `STATISTICS_LEVEL=ALL`）。	自动（针对并行或长耗时SQL）。
开销	由用户控制，仅在需要时启用。	Oracle自动管理开销。
数据内容	最后一次执行的最终统计信息。	每次被监控的执行的详细信息。
实时性	执行完成后才可查看。	近实时，执行中即可查看。
最佳场景	在测试环境或会话中主动、针对性地深度诊断特定SQL。	自动监控和诊断生产环境中资源密集型的SQL。

5️⃣ 常用查询 SQL

以下是一些利用该视图强大功能的实用查询。

一站式性能诊断：对比估算与实际值，并查看资源消耗

SELECT id,
       lpad(' ', depth) || operation || ' ' || options AS operation,
       object_name,
       cardinality AS est_rows,
       last_output_rows AS act_rows,
       ROUND(last_output_rows / NULLIF(cardinality, 0), 2) AS ratio,
       last_cr_buffer_gets AS consistent_gets,
       last_disk_reads AS physical_reads,
       ROUND(last_elapsed_time / 1000000, 2) AS elapsed_sec
FROM v$sql_plan_statistics_all
WHERE sql_id = '&sql_id'
  AND child_number = &child_number
ORDER BY id;

查找优化器估算错误最严重的步骤（性能问题根源）

SELECT id, operation, options, object_name,
       cardinality AS estimated,
       last_output_rows AS actual,
       CASE WHEN cardinality = 0 THEN NULL
            ELSE ROUND(last_output_rows / cardinality, 2)
       END AS est_actual_ratio
FROM v$sql_plan_statistics_all
WHERE sql_id = '&sql_id'
  AND cardinality > 0
  AND last_output_rows > 0
ORDER BY ABS(last_output_rows - cardinality) DESC;

分析内存和临时空间使用最多的操作（如排序、哈希）

SELECT id, operation, options, object_name,
       ROUND(last_memory_used / 1024 / 1024, 2) AS mem_used_mb,
       ROUND(last_tempseg_size / 1024 / 1024, 2) AS temp_used_mb,
       last_disk_reads
FROM v$sql_plan_statistics_all
WHERE sql_id = '&sql_id'
  AND (last_memory_used > 0 OR last_tempseg_size > 0)
ORDER BY last_memory_used DESC;

6️⃣ 使用场景与总结

6.1 典型使用场景

深度SQL调优：在开发或测试环境中，对关键SQL启用统计收集，执行后立即分析 V$SQL_PLAN_STATISTICS_ALL，精准定位估算错误和资源消耗点，为创建索引、修改SQL等提供数据依据。
内存问题诊断：诊断 ORA-01555 快照过旧错误或临时表空间空间不足问题。通过查看 LAST_TEMPSEG_SIZE，可以发现哪些操作产生了大量临时磁盘I/O。
执行计划稳定性研究：比较同一SQL在不同版本Oracle或不同统计信息下的执行计划，不仅看计划结构是否变化，更关键的是看每一步的估算和实际值是否变得更准确。
自动化脚本开发：由于其所有数据都在一个视图中，简化了编写自动化脚本来扫描数据库中存在严重估算错误的SQL语句。

6.2 重要总结与最佳实践

V$SQL_PLAN_STATISTICS_ALL 是 Oracle提供的功能最集中的执行计划分析视图，是性能诊断的“瑞士军刀”。
使用前务必确认 STATISTICS_LEVEL=ALL 或使用了 GATHER_PLAN_STATISTICS Hint，否则无法获取实际运行数据。
LAST_OUTPUT_ROWS 与 CARDINALITY 的对比是使用此视图的最核心价值，直接揭示了优化器决策的准确性。
对于临时空间和内存使用分析（LAST_MEMORY_USED, LAST_TEMPSEG_SIZE），此视图提供了不可替代的价值。
最高效的使用方式是结合 DBMS_XPLAN.DISPLAY_CURSOR：
```
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_number, 'ALLSTATS LAST'));
```
此命令会生成一个格式清晰、包含所有关键信息的报告，是人工查询此视图的最佳替代方案。