Oracle查看真实执行计划方法总结

原创于 2025-08-26 12:25:28 发布 · 530 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

数据库同时被 2 个专栏收录

124 篇文章

订阅专栏

Oracle

46 篇文章

订阅专栏

在Oracle中，查看真实的执行计划（Actual Execution Plan）与查看预测的执行计划（Explained Plan）有本质区别。

预测的执行计划：基于统计信息，通过 EXPLAIN PLAN FOR 命令预估SQL会怎么执行。它没有真正运行SQL，因此不知道实际返回的行数、实际的消耗时间等真实数据。
真实的执行计划：SQL语句真正在数据库中执行后所产生的计划。它包含了每一步操作实际处理的行数（A-Rows）、实际消耗的时间、实际的物理读/逻辑读等关键信息，是性能调优最可靠的依据。

以下是查看真实执行计划的几种主流和推荐方法，按推荐顺序排列。

方法一：使用 DBMS_XPLAN.DISPLAY_CURSOR (最推荐、最准确)

这是从动态性能视图 V$SQL_PLAN 中获取刚执行过的SQL的真实计划的最佳方式。它提供了最详尽的信息。

步骤：

在当前会话中执行目标SQL（确保执行计划被缓存）。
```
SELECT /*+ TAG_MY_QUERY */ e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;
```
(注意：我加了一个注释 /*+ TAG_MY_QUERY */ 作为Hint，方便后面查找。你也可以用任何独特的方式标识你的SQL）
使用 DBMS_XPLAN.DISPLAY_CURSOR 查看刚执行的SQL的真实计划。
```
SELECT t.*
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST')) t;
```
参数解释：
- 第一个参数：sql_id。填 null 表示获取当前会话最后一条执行的SQL。
- 第二个参数：child_number。填 null 表示默认。
- 第三个参数：格式化参数。'ALLSTATS LAST' 是最关键的选项，它会显示出 A-Time (实际时间) 和 A-Rows (实际行数) 等核心信息。
  - 其他常用格式：'ALLSTATS LAST cost' (同时显示优化器Cost估值), 'ADVANCED' (显示所有信息，包括谓词过滤等)。

解读输出关键列：

Id: 执行计划的步骤ID。
Operation: 操作内容（如 TABLE ACCESS, HASH JOIN）。
Name: 操作的对象名（如表名、索引名）。
Starts: 该操作执行的次数。
E-Rows: 优化器预估返回的行数 (Estimated Rows)。
A-Rows: 实际返回的行数 (Actual Rows)。比较 A-Rows 和 E-Rows 是判断统计信息是否准确的核心。如果差异巨大，说明优化器基于错误的信息做出了判断。
A-Time: 该操作实际消耗的时间（格式是 HH:MM:SS.FF）。这是定位性能瓶颈的关键。
Buffers: 逻辑读（单位是Block）。
Reads: 物理读。

更精确的做法：指定 SQL_ID

如果系统很忙，担心找不到刚执行的SQL，可以先查询到确切的 sql_id 和 child_number。

执行SQL。

查询 V$SQL 视图找到你的SQL：

SELECT sql_id, child_number, sql_text
FROM v$sql
WHERE sql_text LIKE '%TAG_MY_QUERY%'; -- 使用你之前加的独特注释

使用查到的 sql_id 和 child_number 带入函数：

SELECT t.*
FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_number, 'ALLSTATS LAST')) t;

方法二：使用 SQL Monitor (适用于长时间运行的SQL)

对于运行时间较长（超过5秒）或消耗大量资源的SQL，Oracle会自动启用SQL Monitor功能。它提供的报告比 DISPLAY_CURSOR 更加图形化和详细，包括执行进度、并行执行等。

步骤：

执行一个长时间运行的SQL。

查找该SQL的 sql_id：

SELECT sql_id, status, sql_text
FROM v$sql_monitor
WHERE sql_text LIKE '%你的SQL内容%';

生成SQL Monitor报告：

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id => '你的sql_id', 
  type => 'TEXT', -- 可选 'HTML', 'ACTIVE' (HTML交互式报告，需支持HTML的工具如SQL Developer)
  report_level => 'ALL') 
AS report
FROM dual;

将输出结果复制出来查看，或者在SQL Developer中运行，如果使用 TYPE => 'ACTIVE' 可以看到非常酷炫的交互式流程图报告。

方法三：设置 AUTOTRACE (需要权限，兼具真实和预测)

SQL*Plus 或 SQLcl 中的 AUTOTRACE 功能可以在语句执行后自动显示其执行计划和统计信息。它实际上执行了SQL，所以看到的是真实计划。

步骤：

在SQL*Plus中打开AUTOTRACE：

-- 最简单格式，只显示计划
SET AUTOTRACE ON EXPLAIN;

-- 最常用格式，执行SQL并显示计划和高阶统计信息（逻辑读等）
SET AUTOTRACE ON STATISTICS;

-- 最详细格式，显示计划、统计信息和执行结果（会输出两遍结果，一遍是SQL结果，一遍是计划）
SET AUTOTRACE ON;

-- 关闭AUTOTRACE
SET AUTOTRACE OFF;

执行SQL：

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

执行后，会立即显示执行计划和统计信息。

注意： 使用 AUTOTRACE ON 可能会需要额外的权限（通常授予 PLUSTRACE 角色）。

方法四：查询历史执行计划 (AWR/STATSPACK)

如果SQL是过去执行的，你可以从AWR（自动工作负载仓库）或STATSPACK快照中获取其历史真实执行计划。

从AWR中查询：

找到SQL的 sql_id。

使用 DBMS_XPLAN.DISPLAY_AWR：

SELECT * FROM table(
  DBMS_XPLAN.DISPLAY_AWR('你的sql_id')
);

总结与推荐

方法	适用场景	优点	缺点
`DBMS_XPLAN.DISPLAY_CURSOR`	绝大多数情况	最准确、最详细、信息最全	需要SQL刚执行过，计划还在Library Cache中
SQL Monitor	长查询、并行查询、资源消耗大的SQL	信息极度详细，有交互式流程图	主要针对“大”SQL
AUTOTRACE	SQL*Plus/SQLcl环境下的快速检查	方便快捷，兼具真实结果和计划	需要额外权限，会真正执行SQL（可能产生副作用）
`EXPLAIN PLAN FOR`	仅用于预测，开发阶段初步分析	不会真正执行SQL，无风险	不是真实计划，可能不准确