Oracle和Mysql的真实执行计划

这是一个非常深入且实用的问题。真实执行计划（Actual Execution Plan）指的是 SQL 语句实际运行时所使用的计划，它包含了执行后才知道的真实数据，如实际返回行数（Actual Rows）、实际耗时等。这与静态的EXPLAIN PLAN（只是优化器的预测）有本质区别。

下面分别详细讲解如何获取 Oracle 和 MySQL 的真实执行计划。

---

Oracle 查看真实执行计划

Oracle 提供了多种强大的工具来捕获真实执行计划，其中最常用的是 DBMS_XPLAN.DISPLAY_CURSOR。

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

这个包可以查看刚刚执行过的 SQL 语句在库缓存（Library Cache）中的真实执行计划。

步骤：

1. 设置统计信息收集级别（必须步骤）：
   在执行你的 SQL 前，在当前会话中运行以下命令，确保 Oracle 收集详细的执行统计信息。

   ALTER SESSION SET STATISTICS_LEVEL = ALL;
   -- 或者至少设置为 TYPICAL (通常是默认值)，但 ALL 信息最全
   -- ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;
   

2. 执行你想要分析的 SQL 语句：

   SELECT e.last_name, d.department_name
   FROM employees e
   JOIN departments d ON e.department_id = d.department_id
   WHERE e.salary > 10000;
   

3. 立即查询该语句的真实执行计划：
   使用 DBMS_XPLAN.DISPLAY_CURSOR 并指定 FORMAT 参数为 'ALLSTATS LAST'。这是最关键的一步。

   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
   

   • NULL, NULL：表示获取当前会话中最后一条执行的 SQL 语句。
   • 'ALLSTATS LAST'：格式模式，它会输出实际行数（A-Rows）、预估行数（E-Rows）、实际物理读（Buffers）、实际耗时（Time） 等关键信息。

解读输出关键列：
在执行计划的结果中，你会看到比普通 EXPLAIN PLAN 多出以下几列，这才是精华所在：

列名	描述
E-Rows	预估行数 (Estimated Rows)：优化器事先预估的每一步操作返回的行数。
A-Rows	实际行数 (Actual Rows)：该步骤实际返回的行数。这是判断优化器预估是否准确的金标准。如果 A-Rows 和 E-Rows 差异巨大，说明统计信息可能有问题。
A-Time	实际耗时：该步骤实际执行所花费的时间（格式：HH:MM:SS.FF）。可以清晰看到时间消耗在哪个操作上。
Buffers	逻辑读：该步骤实际发生的逻辑 I/O 次数（访问内存中数据块的次数）。是衡量资源消耗的重要指标。
Reads	物理读：该步骤实际从磁盘读取的数据块次数。如果这个值很高，说明可能缺少合适索引或需要优化内存。

示例输出片段：

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |      1 |        |     10 |00:00:00.01 |     100 |     20 |
|*  1 |  HASH JOIN          |             |      1 |    100 |     10 |00:00:00.01 |     100 |     20 |
|*  2 |   TABLE ACCESS FULL | EMPLOYEES   |      1 |    100 |      5 |00:00:00.01 |      50 |     10 |
|   3 |   TABLE ACCESS FULL | DEPARTMENTS |      1 |     27 |     27 |00:00:00.01 |      50 |     10 |
--------------------------------------------------------------------------------------------------

诊断：从上面可以看出，优化器预估 ID=2 的全表扫描会返回 100 行（E-Rows），但实际只返回了 5 行（A-Rows）。这说明统计信息过时，优化器高估了符合 salary > 10000 条件的员工数量，可能导致它选择了错误的连接方式。

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

对于并行查询或长时间运行的复杂 SQL，SQL Monitor 提供了近乎实时的、非常详细的报告。

-- 首先找到SQL的SQL_ID
SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE '%your_sql_text%';

-- 然后生成SQL Monitor报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id', type => 'TEXT') FROM dual;

这个报告包含每个执行步骤的时间线、并行从服务器处理情况等极其详细的信息。

---

MySQL 查看真实执行计划

MySQL 8.0 之前，查看“真实”计划比较麻烦。从 MySQL 8.0 开始，官方提供了 EXPLAIN ANALYZE 功能，这是最直接的方法。

方法一：使用 EXPLAIN ANALYZE (MySQL 8.0+，最推荐)

EXPLAIN ANALYZE 会实际执行 SQL 语句，并输出带有真实执行数据的执行计划。

语法：

EXPLAIN ANALYZE
SELECT e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_no = d.dept_no
WHERE e.salary > 10000;

解读输出：
它的输出不是表格，而是一棵树状结构，包含了每个迭代器的实际执行时间。

示例输出：

-> Nested loop inner join  (cost=... rows=...) (actual time=0.125..0.345 rows=10 loops=1)
    -> Filter: (e.salary > 10000)  (cost=... rows=...) (actual time=0.095..0.210 rows=5 loops=1)
        -> Table scan on e  (cost=... rows=1000) (actual time=0.050..0.120 rows=1000 loops=1)
    -> Single-row index lookup on d using PRIMARY (dept_no=e.dept_no)  (cost=... rows=1) (actual time=0.020..0.025 rows=2 loops=5)

关键信息解读：

• actual time=0.125..0.345：单位是毫秒。第一个数字是该操作获取第一行的平均时间，第二个数字是该操作获取所有行的平均时间。
• rows=10：该操作实际返回的行数。
• loops=5：该操作被执行了多少次。例如，在嵌套循环连接中，内表（d）被扫描了 5 次（因为外表 e 有 5 行符合条件）。

通过分析 actual time，你可以非常精确地定位到哪个操作消耗了最多的时间。

方法二：使用 Performance Schema (MySQL 5.6+)

对于更底层的分析，可以启用 Performance Schema 来跟踪语句执行的详细阶段和等待事件。这比 EXPLAIN ANALYZE 更复杂，但信息也更底层。

1. 确保 performance_schema=ON。
2. 执行你的 SQL。
3. 查询 performance_schema.events_statements_history_long 等表来查看详细的性能剖析数据。

方法三：使用 EXPLAIN FORMAT=JSON 后再分析

EXPLAIN FORMAT=JSON 输出的信息量远大于传统表格格式，包含更多优化器决策的细节。虽然它不包含“实际”数据，但对于复杂查询，它是分析预测计划的最佳工具。

---

总结对比

特性	Oracle	MySQL (8.0+)
核心命令	SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));	EXPLAIN ANALYZE ...
前置要求	ALTER SESSION SET STATISTICS_LEVEL = ALL;	无，直接使用
关键输出	A-Rows (实际行数) vs E-Rows (预估行数) A-Time (实际时间) Buffers (逻辑读)	actual time (实际时间，毫秒) rows (实际行数) loops (循环次数)
优点	信息非常全面（逻辑/物理读、时间、行数），行业标杆	简单直观，直接集成，树形结构清晰显示时间消耗
最佳实践	始终对比 A-Rows 和 E-Rows，这是判断优化器是否选错计划的最重要依据。	重点关注 actual time 最大的那个操作节点，那就是性能瓶颈。

无论是 Oracle 还是 MySQL，真实执行计划都是进行 SQL 性能调优不可或缺的利器，因为它用真实数据告诉你瓶颈究竟在哪里。