Oracle执行计划详解与优化

为您详细讲解 Oracle 数据库查询计划（Execution Plan）的看法。这与 MySQL 的思路类似，但工具、命令和部分细节有所不同。

看懂执行计划是 Oracle SQL 性能调优的基石。它揭示了 Oracle 优化器（Optimizer）为了执行您的 SQL 语句所选择的路径和方法。

---

1. 如何获取执行计划？

获取 Oracle 执行计划主要有以下三种方式，推荐使用第一种。

方法一：使用 EXPLAIN PLAN FOR (最常用，适用于开发测试)

这是最直接的方法，用于在会话中快速解释一条 SQL 语句，但不真正执行它。

步骤：

1. 生成执行计划并存入 PLAN_TABLE：

   EXPLAIN PLAN FOR
   SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.last_name = 'King';
   

2. 查询计划表查看结果。最常用的是使用 DBMS_XPLAN.DISPLAY 这个包，它提供了清晰易读的格式：

   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
   

   你也可以指定表名和语句 ID（如果你没有手动指定）：

   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'BASIC'));
   

方法二：使用 SQL*Plus 或 SQLcl 的 AUTOTRACE 功能

这种方式可以实际执行语句，并同时显示执行计划和统计信息（逻辑读、物理读等），非常实用。

步骤：

1. 在 SQL*Plus 或 SQLcl 中连接数据库。
2. 开启 AUTOTRACE：

   SET AUTOTRACE ON;
   

   然后执行你的 SQL 语句，它会返回结果，并紧接着显示执行计划和统计信息。
   其他常用选项：
   • SET AUTOTRACE TRACEONLY; – 不显示查询结果，只显示计划和统计信息（结果集很大时非常有用）。
   • SET AUTOTRACE TRACEONLY EXPLAIN; – 只显示计划，不执行语句也不显示统计信息。
   • SET AUTOTRACE OFF; – 关闭。

方法三：从动态性能视图中查询（用于分析已执行的SQL）

对于正在运行或刚刚运行过的 SQL，可以从 V$SQL_PLAN 等动态性能视图中提取其真实的执行计划。这通常需要 DBA 权限。

SELECT t.*
FROM v$sql s, TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
WHERE s.sql_text LIKE '%你的SQL关键字%';

---

2. 执行计划结果解读（核心）

使用 DBMS_XPLAN.DISPLAY() 后，你会看到一个格式化的输出，主要关注以下几点：

输出结构示例：

Plan hash value: 123456789

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    10 |   800 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |    10 |   800 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |     1 |    51 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_NAME_IX  |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |    10 |   290 |     2   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK   |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."LAST_NAME"='King')
   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

关键列解读：

列名	描述	解读与关注点
Id	步骤序号	标识执行计划中每个操作的顺序号。缩进表示层级关系，子操作为父操作提供数据。
Operation	操作内容（最重要）	表示数据库在该步骤执行的操作。这是理解执行逻辑的核心。例如： - TABLE ACCESS FULL：全表扫描（警惕！） - INDEX RANGE SCAN：索引范围扫描 - INDEX UNIQUE SCAN：索引唯一扫描 - NESTED LOOPS：嵌套循环连接 - HASH JOIN：哈希连接 - SORT AGGREGATE：排序聚合（如 COUNT） - SORT ORDER BY：排序（警惕 Using filesort 类似物）
Name	操作对象	显示操作针对的索引、表、视图等对象的名称。
Rows	基数（Cardinality）	优化器预估该操作将返回的行数。这是判断优化器评估是否准确的关键。如果预估行数和实际行数差距巨大（通常差几个数量级），说明统计信息可能过期，需要收集。
Cost (%CPU)	成本	优化器根据 CPU、I/O 等因素计算出的该操作的相对成本值。这是优化器选择计划的依据。用于比较不同计划的优劣，值越小越好。
Time	预估时间	优化器预估该操作将花费的时间。
Predicate Information	谓词信息	极其重要！它解释了 Operation 列中带 * 号的操作的具体过滤/连接条件。 - access：表示使用索引来定位数据。（access("E"."LAST_NAME"='King')） - filter：表示对所有访问到的数据再进行过滤。如果 filter 操作处理的行数很多，可能是性能瓶颈。

---

3. 核心性能指标速查与优化方向

1. 看 Operation 列的开头：

   • 警惕 TABLE ACCESS FULL（全表扫描）。对于大表，这通常是性能杀手。优化方向：在 WHERE 子句条件列上建立索引。
   • 理想的操作包括 INDEX RANGE SCAN, INDEX UNIQUE SCAN 等。

2. 看 Rows (基数) 列：

   • 对比每一步预估的行数和下一步操作的行数。如果某一步预估返回 1000 行，但下一步操作只预估 1 行，说明优化器可能严重误判。优化方向：对相关表运行 DBMS_STATS.GATHER_TABLE_STATS 更新统计信息。

3. 看连接方式 (Operation 列)：

   • NESTED LOOPS：适合驱动表（返回行数少的表）和小表连接。
   • HASH JOIN：适合大数据集的等值连接，通常效率很高。
   • MERGE JOIN：适合非等值连接或已经排好序的数据。
   • 如果连接方式不合理（例如两个大表用了嵌套循环），可能是统计信息不准或缺少索引。

4. 看 Predicate Information：

   • 确认索引是否被正确用于 access，而不是仅仅用于 filter。

---

4. 实战分析示例

问题SQL：

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

假设一个很差的执行计划：

----------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost | Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   100 |  4700 |   50 | 00:00:01 |
|*  1 |  HASH JOIN          |             |   100 |  4700 |   50 | 00:00:01 |
|*  2 |   TABLE ACCESS FULL | EMPLOYEES   |   100 |  2600 |   25 | 00:00:01 |
|   3 |   TABLE ACCESS FULL | DEPARTMENTS |    27 |   567 |   24 | 00:00:01 |
----------------------------------------------------------------------------

Predicate Information:
----------------------
   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   2 - filter("E"."SALARY">10000)

诊断：

1. Id=2：对 EMPLOYEES 表进行了 TABLE ACCESS FULL（全表扫描），并且用 filter 来筛选 salary > 10000。这是主要性能瓶颈。
2. Id=3：对 DEPARTMENTS 表也进行了全表扫描。虽然它很小，问题不大，但也可以优化。

优化方案：

1. 在 EMPLOYEES 表的 salary 字段上建立索引，让查询能直接通过索引找到高薪员工，而不是全表扫描。

   CREATE INDEX emp_salary_ix ON employees (salary);
   

2. （可选）由于查询使用了 department_id 进行连接，可以考虑建立复合索引 (salary, department_id)，实现覆盖索引，避免回表。

优化后期望的计划：
期望看到 EMPLOYEES 表的访问方式从 TABLE ACCESS FULL 变为 TABLE ACCESS BY INDEX ROWID，其上游是一个 INDEX RANGE SCAN，成本 Cost 和预估行数 Rows 都会显著下降。

总结

1. 工具：掌握 EXPLAIN PLAN FOR 和 DBMS_XPLAN.DISPLAY() 的组合，以及 SQL*Plus 的 AUTOTRACE。
2. 核心：紧盯 Operation（操作类型）、Rows（预估行数）和 Predicate Information（访问/过滤条件）。
3. 目标：消除不必要的全表扫描（FULL SCAN），确保索引被有效使用（INDEX SCAN），保证统计信息准确以使优化器做出正确判断。
4. 进阶：理解不同的表连接方式（Nested Loops, Hash Join, Merge Join）及其适用场景。

通过不断练习查看和分析执行计划，您会逐渐培养出快速定位 SQL 性能问题的直觉。