DB / Oracle / 性能诊断

Oracle 执行计划详解

2026.09.06

Oracle 执行计划详解

适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

执行计划（Execution Plan） 是 SQL 执行的步骤[1]：

核心内容：

  • 访问路径
  • JOIN 方法
  • 操作顺序
  • 成本估算

2. 查看执行计划

2.1 EXPLAIN PLAN

EXPLAIN PLAN FOR 
SELECT * FROM employees WHERE dept_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2.2 AUTOTRACE

SET AUTOTRACE ON;       -- 显示结果+计划+统计
SET AUTOTRACE TRACEONLY; -- 显示计划+统计（不显示结果）
SET AUTOTRACE ON EXPLAIN; -- 仅计划
SET AUTOTRACE OFF;

2.3 V$SQL_PLAN

SELECT * FROM v$sql_plan 
WHERE sql_id = '&sql_id'
ORDER BY child_number, id;

2.4 DBMS_XPLAN

-- 从游标缓存
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

-- 从 AWR
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

-- 从 SQL 调优集
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQLSET('&sqlset_name', '&sql_id'));

3. 执行计划字段

| Id | Operation          | Name     | Rows | Bytes | Cost | Time  |
| 0  | SELECT STATEMENT  |          | 10   | 200   | 3    | 00:01 |
| 1  | TABLE ACCESS FULL | EMPLOYEES| 10   | 200   | 3    | 00:01 |

3.1 字段说明

字段说明
Id步骤编号
Operation操作
Name对象名
Rows估算行数
Bytes估算字节
Cost成本
Time估算时间

3.2 缩进表示父子关系

0 SELECT STATEMENT
  1 HASH JOIN
    2 TABLE ACCESS FULL (dept)
    3 TABLE ACCESS FULL (emp)

4. 访问路径

4.1 全表扫描

TABLE ACCESS FULL
  • 扫描整表
  • 适合小表或大查询
  • 多块读

4.2 索引扫描

INDEX UNIQUE SCAN      -- 唯一索引等值
INDEX RANGE SCAN       -- 范围扫描
INDEX FULL SCAN        -- 全索引扫描
INDEX FAST FULL SCAN   -- 快速全扫描
INDEX SKIP SCAN        -- 跳跃扫描

4.3 ROWID 访问

TABLE ACCESS BY USER ROWID
TABLE ACCESS BY INDEX ROWID

5. JOIN 方法

5.1 Nested Loop Join

NESTED LOOPS
  外表（小）
  内表（索引）
  • 适合小表驱动大表
  • 索引访问内表
  • OLTP

5.2 Hash Join

HASH JOIN
  构建表（小）
  探测表（大）
  • 适合大表等值连接
  • 内存消耗
  • OLAP

5.3 Sort Merge Join

MERGE JOIN
  SORT
  SORT
  • 适合已排序数据
  • 不等值连接
  • 排序开销

5.4 Cartesian Join

MERGE JOIN CARTESIAN
  • 笛卡尔积
  • 通常错误

6. 操作详解

6.1 SORT

SORT AGGREGATE      -- 聚合
SORT ORDER BY       -- 排序
SORT GROUP BY       -- 分组
SORT UNIQUE         -- 去重
SORT JOIN           -- JOIN 排序

6.2 VIEW

VIEW              -- 内联视图
HASH GROUP BY     -- 哈希分组

6.3 FILTER

FILTER   -- 过滤

6.4 UNION

UNION-ALL
UNION
INTERSECTION
MINUS

7. 统计信息

7.1 关键统计

统计说明
recursive calls递归调用
db block gets当前块读
consistent gets一致性读
physical reads物理读
redo sizeredo 大小
bytes sent发送字节
bytes received接收字节
sorts (memory)内存排序
sorts (disk)磁盘排序

7.2 关注点

  • consistent gets 高：逻辑读多
  • physical reads 高：物理 I/O 多
  • sorts (disk) > 0：磁盘排序

8. HINT

8.1 优化器 HINT

/*+ ALL_ROWS */       -- 优化吞吐
/*+ FIRST_ROWS(10) */ -- 优化响应
/*+ CHOOSE */         -- 自动选择

8.2 访问路径

/*+ FULL(table) */        -- 全表扫描
/*+ INDEX(table idx) */   -- 使用索引
/*+ INDEX_FFS(table idx) */ -- 快速全扫描
/*+ NO_INDEX(table idx) */ -- 不用索引

8.3 JOIN 方法

/*+ USE_NL(table1 table2) */   -- Nested Loop
/*+ USE_HASH(table1 table2) */ -- Hash Join
/*+ USE_MERGE(table1 table2) */ -- Sort Merge
/*+ LEADING(table) */          -- 驱动表
/*+ ORDERED */                 -- 按顺序 JOIN

8.4 并行

/*+ PARALLEL(table 4) */
/*+ NOPARALLEL(table) */
/*+ PQ_DISTRIBUTE */

9. 绑定变量窥视

9.1 9i+ 默认

-- 第一次执行窥视绑定变量值
-- 生成执行计划
-- 后续使用相同计划

9.2 问题

  • 数据倾斜时可能错误
  • 不同值需要不同计划

9.3 自适应游标共享（11g+）

-- 自动生成多个子游标
-- 根据绑定值选择

-- 查看
SELECT sql_id, child_number, bind_data 
FROM v$sql WHERE sql_id = '&sql_id';

10. 执行计划稳定性

10.1 SQL Profile

-- SQL Tuning Advisor 生成
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
  task_name => 'my_task',
  name => 'my_profile'
);

10.2 SQL Plan Baseline

-- 捕获
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

-- 查看
SELECT * FROM dba_sql_plan_baselines;

-- 固化计划
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&sql_id');

10.3 SQL Patch

-- 加 HINT
EXEC DBMS_SQLDIAG.CREATE_SQL_PATCH(
  sql_text => 'SELECT ...',
  hint_text => 'INDEX(emp idx_emp)',
  name => 'my_patch'
);

11. 优化器统计

11.1 收集

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

11.2 参数

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCOTT',
  tabname => 'EMPLOYEES',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE,
  degree => 4
);

11.3 直方图

-- 数据倾斜
method_opt => 'FOR COLUMNS size 254 dept_id'

12. 常见坑与排错

12.1 执行计划不稳定

-- 1. 统计信息过期
EXEC DBMS_STATS.GATHER_TABLE_STATS(...);
-- 2. 绑定变量窥视
-- 3. 使用 SQL Plan Baseline

12.2 全表扫描

-- 1. 索引是否存在
-- 2. 统计信息
-- 3. WHERE 条件
-- 4. 数据量

12.3 CBO 选错计划

-- 1. 加 HINT
-- 2. SQL Profile
-- 3. SQL Plan Baseline

12.4 ORA-00942: 表不存在

-- 检查权限
-- 检查表名

13. 最佳实践

  1. 定期收集统计信息：CBO 准确
  2. 查看执行计划：验证
  3. 关注逻辑读：consistent gets
  4. 避免磁盘排序：sorts (disk)
  5. 合理使用 HINT：仅必要时
  6. SQL Plan Baseline：稳定计划
  7. 直方图数据倾斜：精确
  8. 绑定变量：减少硬解析
  9. 测试不同计划：选择最优
  10. 监控性能：持续优化

14. 参考资料

[1] Oracle Database SQL Tuning Guide 19c, “Examining Execution Plans” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/examining-execution-plans.html