Oracle SQL 执行计划详解

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

1. 概述

执行计划是 SQL 优化的基础[1]：

2. 查看

2.1 EXPLAIN PLAN

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

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));

2.2 游标

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));

2.3 AWR

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

2.4 Baseline

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

3. 操作

3.1 表访问

操作	说明
TABLE ACCESS FULL	全表扫描
TABLE ACCESS BY ROWID	ROWID 访问
TABLE ACCESS BY INDEX ROWID	索引回表

3.2 索引

操作	说明
INDEX UNIQUE SCAN	唯一索引
INDEX RANGE SCAN	范围扫描
INDEX FULL SCAN	全索引扫描
INDEX FAST FULL SCAN	快速全扫描
INDEX SKIP SCAN	跳跃扫描

3.3 JOIN

操作	说明
NESTED LOOPS	嵌套循环
HASH JOIN	哈希连接
MERGE JOIN	排序合并
BROADCAST	广播

3.4 排序

操作	说明
SORT AGGREGATE	聚合
SORT ORDER BY	排序
SORT GROUP BY	分组
SORT JOIN	连接排序
SORT UNIQUE	去重

3.5 其他

操作	说明
FILTER	过滤
VIEW	视图
UNION-ALL	并集
CONCATENATION	拼接
WINDOW	窗口
MAT_VIEW REWRITE ACCESS	物化视图重写

4. 统计

4.1 基数

- Rows：估计行数
- Bytes：字节
- Cost：成本

4.2 实际

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));
-- Starts：执行次数
- E-Rows：估计行
- A-Rows：实际行
- A-Time：实际时间
- Buffers：缓冲
- Reads：读

5. 关注点

5.1 性能问题

- TABLE ACCESS FULL：全表（大表差）
- 高 Cost
- 大量 Buffers
- 实际 vs 估计差异大

5.2 优化方向

- 索引
- JOIN 方法
- 排序
- 子查询

6. JOIN 选择

6.1 Nested Loops

- 小表驱动大表
- 索引利用
- 适合 OLTP

6.2 Hash Join

- 大表
- 等值连接
- 内存
- 适合仓库

6.3 Sort Merge

- 已排序
- 不等值
- 大数据

详细见：Oracle JOIN 连接方式。

7. 索引选择

7.1 UNIQUE SCAN

-- 唯一索引等值
SELECT * FROM employees WHERE id = 100;

7.2 RANGE SCAN

-- 范围
SELECT * FROM employees WHERE dept_id = 10;
SELECT * FROM employees WHERE id BETWEEN 1 AND 100;

7.3 FULL SCAN

-- 全索引
SELECT id FROM employees;
-- 索引覆盖

7.4 FAST FULL SCAN

-- 多块读
SELECT COUNT(*) FROM employees;

详细见：Oracle 索引优化策略详解。

8. 分区

8.1 裁剪

- PARTITION RANGE SINGLE
- PARTITION RANGE ITERATOR
- PARTITION RANGE ALL

8.2 验证

EXPLAIN PLAN FOR 
  SELECT * FROM sales WHERE sale_date = DATE '2025-07-21';
-- 应看到 PARTITION RANGE SINGLE

详细见：Oracle 表分区策略详解。

9. Hint

-- 索引
SELECT /*+ INDEX(e idx_emp_name) */ * FROM employees e WHERE name = 'Alice';

-- JOIN
SELECT /*+ USE_HASH(a b) */ * FROM a, b WHERE a.id = b.id;

-- 并行
SELECT /*+ PARALLEL(t 8) */ COUNT(*) FROM big_table t;

详细见：Oracle SQL Hint 详解。

10. 优化器

10.1 CBO

- 基于成本
- 统计信息
- 选择最低成本

10.2 RBO

- 基于规则
- 已废弃

详细见：Oracle 优化器 CBO 原理。

11. 统计信息

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES', cascade => TRUE);

-- 直方图
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES',
  method_opt => 'FOR ALL COLUMNS SIZE 254');

详细见：Oracle 直方图与统计信息。

12. 优化流程

12.1 识别

- AWR TOP SQL
- ASH
- 监控

12.2 分析

- 执行计划
- 统计信息
- 等待事件

12.3 优化

- 索引
- SQL 重写
- Hint
- Profile
- Baseline

12.4 验证

- 性能对比
- 测试

详细见：Oracle SQL 调优最佳实践。

13. 案例

13.1 全表扫描

-- 差
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';
-- TABLE ACCESS FULL

-- 好
CREATE INDEX idx_upper_name ON employees(UPPER(name));
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';
-- INDEX RANGE SCAN

13.2 JOIN 优化

-- 差（Nested Loop 大表）
SELECT /*+ USE_NL(a b) */ * FROM big_a a, big_b b WHERE a.id = b.id;

-- 好（Hash Join）
SELECT /*+ USE_HASH(a b) */ * FROM big_a a, big_b b WHERE a.id = b.id;

13.3 子查询

-- 差
SELECT * FROM employees WHERE dept_id IN (SELECT id FROM departments);

-- 好（EXISTS 或 JOIN）
SELECT e.* FROM employees e, departments d WHERE e.dept_id = d.id;

14. 监控

14.1 SQL Monitor

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id') FROM dual;

14.2 AWR

@?/rdbms/admin/awrrpt.sql

详细见：Oracle AWR 详解。

15. 常见坑与排错

15.1 统计旧

- 执行计划差
- 收集统计

15.2 Bind Peeking

- 绑定变量窥探
- 直方图
- Adaptive Cursor Sharing

15.3 Plan Instability

- 执行计划不稳定
- Baseline
- SQL Profile

详细见：Oracle SQL Plan Baseline 基线。

16. 最佳实践

EXPLAIN PLAN：先看
统计信息：更新
索引合理：覆盖
JOIN 选择：场景
分区裁剪：大表
Hint 谨慎：兜底
Baseline：稳定
监控：持续
测试：验证
文档：记录

17. 参考资料

[1] Oracle Database SQL Tuning Guide 19c, “EXPLAIN PLAN” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/explain-plan.html