印刻数据库

外观

Oracle执行计划分析 ​

执行计划是Oracle数据库优化器为SQL语句生成的执行方案，它描述了Oracle将如何执行SQL语句，包括表访问顺序、访问方法、连接方法等。理解和分析执行计划是Oracle性能优化的核心技能，本文将详细介绍Oracle执行计划的组成、查看方法、解读技巧和优化策略。

执行计划概述 ​

什么是执行计划？ ​

执行计划是Oracle优化器基于SQL语句的结构、表的统计信息、索引信息和系统参数等，生成的一种执行方案。它包含了Oracle执行SQL语句的详细步骤，如：

  • 表的访问顺序
  • 表的访问方法（全表扫描、索引扫描等）
  • 表之间的连接方法（嵌套循环、哈希连接、排序合并连接等）
  • 排序操作
  • 聚合操作
  • 过滤条件

执行计划的重要性 ​

  • 帮助理解SQL语句的执行过程
  • 识别性能瓶颈
  • 指导SQL优化
  • 验证优化效果
  • 预测SQL语句的性能

优化器类型 ​

Oracle有两种优化器类型：

1. 基于规则的优化器（RBO） ​

基于一组固定的规则生成执行计划，不考虑表的统计信息和数据分布。RBO已在Oracle 10g中被弃用，不再推荐使用。

2. 基于成本的优化器（CBO） ​

基于表的统计信息、索引信息和系统参数等，计算不同执行计划的成本，选择成本最低的执行计划。CBO是Oracle当前推荐使用的优化器。

执行计划的组成 ​

1. 操作类型 ​

执行计划中的每个节点代表一个操作，常见的操作类型包括：

操作类型描述
TABLE ACCESS FULL全表扫描
TABLE ACCESS BY INDEX ROWID通过索引ROWID访问表
INDEX FULL SCAN全索引扫描
INDEX RANGE SCAN索引范围扫描
INDEX UNIQUE SCAN索引唯一扫描
INDEX SKIP SCAN索引跳跃扫描
NESTED LOOPS嵌套循环连接
HASH JOIN哈希连接
SORT MERGE JOIN排序合并连接
SORT排序操作
AGGREGATE聚合操作
FILTER过滤操作
VIEW视图操作

2. 访问路径 ​

访问路径是Oracle访问表中数据的方法，常见的访问路径包括：

全表扫描（TABLE ACCESS FULL） ​

Oracle扫描表中的所有行，适合以下情况：

  • 表较小
  • 查询需要访问表中大部分行
  • 没有合适的索引
  • 表的统计信息不准确

索引唯一扫描（INDEX UNIQUE SCAN） ​

Oracle使用唯一索引访问单行数据，适合以下情况：

  • 查询条件包含唯一索引的所有列
  • 查询条件使用等值比较

索引范围扫描（INDEX RANGE SCAN） ​

Oracle使用索引访问多行数据，适合以下情况：

  • 查询条件包含索引的前导列
  • 查询条件使用范围比较（<, >, BETWEEN, LIKE等）

索引全扫描（INDEX FULL SCAN） ​

Oracle扫描整个索引，但不访问表，适合以下情况：

  • 查询只包含索引列
  • 索引包含查询所需的所有列（覆盖索引）

索引跳跃扫描（INDEX SKIP SCAN） ​

Oracle跳过索引的前导列，直接扫描后续列，适合以下情况：

  • 索引的前导列基数较低
  • 查询条件包含索引的非前导列

3. 连接方法 ​

连接方法是Oracle连接多个表的方法，常见的连接方法包括：

嵌套循环连接（NESTED LOOPS） ​

Oracle先扫描驱动表，然后对于驱动表中的每一行，扫描连接表。适合以下情况：

  • 驱动表较小
  • 连接表有合适的索引
  • 查询返回少量行

哈希连接（HASH JOIN） ​

Oracle先对驱动表进行哈希运算，然后扫描连接表，进行哈希匹配。适合以下情况：

  • 两个表都较大
  • 至少有一个表较小，可以放入内存
  • 查询返回大量行

排序合并连接（SORT MERGE JOIN） ​

Oracle先对两个表进行排序，然后合并排序后的结果。适合以下情况：

  • 两个表都需要排序
  • 连接条件使用范围比较
  • 没有合适的索引

4. 成本和Cardinality ​

执行计划中的成本（Cost）表示Oracle估计的执行操作所需的资源量，Cardinality表示Oracle估计的返回行数。

  • Cost：成本越低，执行效率越高
  • Cardinality：Cardinality越接近实际行数，执行计划越准确

如何查看执行计划 ​

1. 使用EXPLAIN PLAN FOR ​

这是最基本的查看执行计划的方法，适用于所有Oracle版本。

sql
-- 生成执行计划
EXPLAIN PLAN FOR
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 查看更详细的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

2. 使用DBMS_XPLAN.DISPLAY_CURSOR ​

这是查看已执行SQL语句执行计划的方法，适用于Oracle 10g及以上版本。

sql
-- 执行SQL语句
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;

-- 查看最近执行的SQL语句的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALL'));

-- 使用SQL_ID和CHILD_NUMBER查看特定SQL语句的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', child_number, 'ALL'));

3. 使用SQL Developer ​

SQL Developer是Oracle官方提供的免费工具，可以直观地查看执行计划。

步骤：

  1. 打开SQL Developer
  2. 连接到数据库
  3. 编写SQL语句
  4. 点击"执行计划"按钮（或按F10）
  5. 查看可视化的执行计划

4. 使用SQL*Plus AUTOTRACE ​

SQL*Plus的AUTOTRACE功能可以在执行SQL语句的同时显示执行计划。

sql
-- 启用AUTOTRACE
SET AUTOTRACE ON;

-- 执行SQL语句
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;

-- 只显示执行计划，不显示查询结果
SET AUTOTRACE TRACEONLY;

5. 使用V$SQL_PLAN视图 ​

V$SQL_PLAN视图包含了所有已执行SQL语句的执行计划。

sql
-- 查找SQL_ID
SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%SELECT e.first_name, e.last_name%';

-- 查看执行计划
SELECT *
FROM v$sql_plan
WHERE sql_id = 'sql_id'
ORDER BY child_number, id;

执行计划的解读 ​

1. 执行计划的结构 ​

执行计划通常以树状结构显示，每个节点代表一个操作，父节点依赖于子节点。执行顺序是从最内层的叶子节点开始，向外层的父节点执行。

示例执行计划：

Plan hash value: 3956160932

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    30 |  2100 |     6   (0)| 00:00:01 |
|   1 |  MERGE JOIN                 |                   |    30 |  2100 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |    27 |   351 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN          | DEPT_ID_PK        |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                 |                   |    30 |  1050 |     4   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL        | EMPLOYEES         |    30 |  1050 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   5 - filter("E"."SALARY">10000)

2. 解读步骤 ​

  1. 查看执行计划的ID：ID越大的操作，执行顺序越靠前
  2. 查看操作类型：了解Oracle使用的访问方法和连接方法
  3. 查看行数（Rows）：了解Oracle估计的返回行数
  4. 查看成本（Cost）：了解Oracle估计的执行成本
  5. 查看谓词信息（Predicate Information）：了解Oracle使用的过滤条件和连接条件
  6. 查看表访问顺序：了解Oracle访问表的顺序

3. 执行顺序 ​

执行计划的执行顺序是从最内层的叶子节点开始，向外层的父节点执行。在示例执行计划中，执行顺序为：

  1. 执行ID=3：对DEPT_ID_PK索引进行全索引扫描
  2. 执行ID=2：通过索引ROWID访问DEPARTMENTS表
  3. 执行ID=5：对EMPLOYEES表进行全表扫描，过滤SALARY>10000的行
  4. 执行ID=4：对EMPLOYEES表的结果进行排序
  5. 执行ID=1：使用排序合并连接连接EMPLOYEES和DEPARTMENTS表
  6. 执行ID=0：执行SELECT语句，返回结果

常见执行计划问题 ​

1. 全表扫描 ​

问题：Oracle对大表进行全表扫描，导致执行时间过长。

原因：

  • 没有合适的索引
  • 查询条件不包含索引的前导列
  • 索引统计信息不准确
  • 表的统计信息不准确

解决方案：

  • 添加合适的索引
  • 重写SQL，包含索引的前导列
  • 更新表和索引的统计信息
  • 调整优化器参数

2. 索引失效 ​

问题：Oracle没有使用预期的索引，导致执行效率低下。

原因：

  • 查询条件使用了函数或表达式
  • 查询条件使用了不等于（!=, <>, NOT）
  • 查询条件使用了IS NULL或IS NOT NULL
  • 索引的统计信息不准确
  • 表的统计信息不准确

解决方案：

  • 避免在查询条件中使用函数或表达式
  • 重写SQL，使用等值比较
  • 添加函数索引
  • 更新表和索引的统计信息

3. 嵌套循环连接性能问题 ​

问题：Oracle使用嵌套循环连接连接两个大表，导致执行时间过长。

原因：

  • 驱动表太大
  • 连接表没有合适的索引
  • 优化器选择了错误的驱动表

解决方案：

  • 添加合适的索引
  • 强制使用哈希连接或排序合并连接
  • 重写SQL，调整表的连接顺序
  • 更新表的统计信息

4. 排序操作 ​

问题：Oracle执行了大量的排序操作，导致执行时间过长。

原因：

  • 查询使用了ORDER BY或GROUP BY
  • 查询使用了DISTINCT
  • 查询使用了UNION或INTERSECT
  • 连接方法使用了排序合并连接

解决方案：

  • 添加合适的索引，避免排序
  • 重写SQL，减少排序操作
  • 使用并行查询
  • 调整SORT_AREA_SIZE参数

如何优化执行计划 ​

1. 添加合适的索引 ​

索引是优化执行计划的最有效方法之一，添加合适的索引可以显著提高查询性能。

sql
-- 添加索引
CREATE INDEX emp_salary_dept_idx ON employees(salary, department_id);

2. 更新统计信息 ​

统计信息是Oracle优化器生成执行计划的重要依据，不准确的统计信息会导致优化器生成糟糕的执行计划。

sql
-- 更新表的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', CASCADE => TRUE);

-- 更新索引的统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_SALARY_DEPT_IDX');

-- 更新模式的统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR', CASCADE => TRUE);

3. 重写SQL语句 ​

重写SQL语句可以改变优化器的执行计划，提高查询性能。

避免在查询条件中使用函数 ​

sql
-- 不好的写法：在查询条件中使用函数
SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY') = '2023';

-- 好的写法：避免使用函数
SELECT * FROM employees WHERE hire_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD');

使用绑定变量 ​

sql
-- 不好的写法：使用常量
SELECT * FROM employees WHERE department_id = 60;

-- 好的写法：使用绑定变量
SELECT * FROM employees WHERE department_id = :dept_id;

避免使用SELECT * ​

sql
-- 不好的写法：使用SELECT *
SELECT * FROM employees WHERE department_id = 60;

-- 好的写法：只选择需要的列
SELECT first_name, last_name, salary FROM employees WHERE department_id = 60;

4. 调整优化器参数 ​

调整优化器参数可以改变优化器的行为，生成更优的执行计划。

sql
-- 设置优化器模式
ALTER SESSION SET optimizer_mode = ALL_ROWS;

-- 设置直方图收集
ALTER TABLE employees MODIFY (salary) INVISIBLE;
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', METHOD_OPT => 'FOR COLUMNS salary SIZE 254');

5. 使用并行查询 ​

对于大型查询，可以使用并行查询提高执行效率。

sql
-- 启用并行查询
SELECT /*+ PARALLEL(4) */ * FROM employees WHERE department_id = 60;

-- 设置表的并行度
ALTER TABLE employees PARALLEL 4;

执行计划的高级分析 ​

1. 查看实际执行计划 ​

使用DBMS_XPLAN.DISPLAY_CURSOR可以查看SQL语句的实际执行计划，包括实际返回行数、实际执行时间等。

sql
-- 查看实际执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

2. 比较执行计划 ​

使用DBMS_XPLAN.DISPLAY_CURSOR可以比较不同SQL语句的执行计划，或同一SQL语句的不同执行计划。

sql
-- 比较执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id1', child_number1, 'ALL'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id2', child_number2, 'ALL'));

3. 查看执行计划的历史记录 ​

使用AWR报告可以查看SQL语句的执行计划历史记录，了解执行计划的变化。

sql
-- 生成AWR报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

版本差异 ​

Oracle 12c ​

  • 引入了自适应执行计划
  • 增强了DBMS_XPLAN功能
  • 支持实时执行计划监控

Oracle 18c ​

  • 增强了自适应执行计划的功能
  • 引入了SQL计划管理
  • 支持自动执行计划捕获

Oracle 19c ​

  • 长期支持版本
  • 增强了SQL计划管理的功能
  • 支持自动执行计划演进

Oracle 21c ​

  • 支持执行计划的AI优化
  • 增强了自适应执行计划的功能
  • 支持执行计划的云原生监控

常见问题（FAQ） ​

Q: 如何确定执行计划的执行顺序？ ​

A: 执行计划的执行顺序是从最内层的叶子节点开始，向外层的父节点执行。ID越大的操作，执行顺序越靠前。

Q: 为什么Oracle选择了全表扫描而不是索引扫描？ ​

A: 可能的原因包括：

  • 表较小，全表扫描比索引扫描更高效
  • 查询需要访问表中大部分行
  • 没有合适的索引
  • 表的统计信息不准确
  • 索引的选择性较低

Q: 如何查看SQL语句的实际执行时间？ ​

A: 可以使用以下方法：

  • 使用SQL*Plus的SET TIMING ON命令
  • 使用DBMS_XPLAN.DISPLAY_CURSOR查看实际执行时间
  • 使用AWR报告查看SQL语句的执行时间

Q: 如何优化嵌套循环连接？ ​

A: 可以通过以下方法优化：

  • 确保连接表有合适的索引
  • 选择较小的表作为驱动表
  • 考虑使用哈希连接或排序合并连接
  • 更新表的统计信息

Q: 如何查看优化器生成执行计划的过程？ ​

A: 可以使用10053事件查看优化器生成执行计划的过程。

sql
-- 启用10053事件
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

-- 执行SQL语句
SELECT * FROM employees WHERE department_id = 60;

-- 禁用10053事件
ALTER SESSION SET EVENTS '10053 trace name context off';

Q: 如何确保执行计划的稳定性？ ​

A: 可以通过以下方法确保执行计划的稳定性：

  • 使用SQL计划基线
  • 使用存储大纲
  • 使用绑定变量
  • 定期更新统计信息
  • 避免在查询条件中使用函数

总结 ​

执行计划是Oracle性能优化的核心，理解和分析执行计划可以帮助开发人员和DBA识别性能瓶颈，优化SQL语句，提高数据库性能。本文介绍了执行计划的组成、查看方法、解读技巧和优化策略，包括常见的访问路径、连接方法、执行计划问题和解决方案。

在实际应用中，应结合实际情况选择合适的优化方法，如添加索引、更新统计信息、重写SQL语句、调整优化器参数等。同时，应定期监控执行计划的变化，确保SQL语句始终使用最优的执行计划。

随着Oracle版本的更新，执行计划的功能不断增强，如自适应执行计划、SQL计划管理、AI优化等，开发人员和DBA应关注新版本的特性，提高性能优化的效率和效果。