DB / Oracle / SQL 开发

Oracle SQL 优化器原理

2026.09.06

Oracle SQL 优化器原理

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

1. 概述

SQL 优化器（CBO）是执行计划生成器[1]：

类型：

  • RBO（Rule-Based Optimizer）：废弃
  • CBO（Cost-Based Optimizer）：默认

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

2. 优化过程

2.1 步骤

1. 解析（Parse）
2. 优化（Optimize）
3. 行源生成（Row Source Generation）
4. 执行（Execute）
5. 取数据（Fetch）

2.2 解析

  • 语法检查
  • 语义检查
  • 共享池检查

2.3 优化

  • 逻辑转换
  • 物理选择
  • 成本计算

3. CBO 成本

3.1 成本组成

Cost = CPU Cost + I/O Cost

3.2 影响因素

  • 表统计
  • 索引统计
  • 列统计
  • 系统统计
  • 参数

3.3 查看

EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

4. 逻辑转换

4.1 视图合并

SELECT * FROM (SELECT * FROM employees WHERE dept_id = 10) v
WHERE v.salary > 5000;
-- 转换为
SELECT * FROM employees WHERE dept_id = 10 AND salary > 5000;

4.2 子查询展开

SELECT * FROM employees 
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NY')
-- 转换为 JOIN

4.3 谓词推进

SELECT * FROM (
  SELECT * FROM employees
) v
WHERE v.dept_id = 10
-- 谓词推入

4.4 OR 展开

SELECT * FROM t WHERE id = 1 OR id = 2
-- 转换
SELECT * FROM t WHERE id = 1
UNION ALL
SELECT * FROM t WHERE id = 2

5. 统计信息

5.1 表统计

  • num_rows
  • blocks
  • avg_row_len

5.2 列统计

  • num_distinct
  • density
  • num_nulls
  • histogram

5.3 索引统计

  • blevel
  • leaf_blocks
  • distinct_keys
  • clustering_factor

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

6. 基数估算

6.1 公式

单表基数 = num_rows * selectivity
selectivity = 1 / num_distinct (无直方图)

6.2 直方图

  • Frequency
  • Height Balanced
  • Hybrid（12c+）

6.3 影响

  • JOIN 方法
  • 索引选择
  • 排序

7. JOIN 方法

7.1 Nested Loop

  • 适合小结果集
  • 索引友好
  • 顺序 I/O

7.2 Hash Join

  • 适合大结果集
  • 内存要求
  • 等值 JOIN

7.3 Sort Merge Join

  • 适合大数据
  • 非等值 JOIN
  • 排序开销

详细见：Oracle JOIN 连接方式。

8. 访问路径

8.1 全表扫描

  • 多块读
  • 适合大比例

8.2 索引扫描

  • 单块读
  • 适合小比例
  • 索引唯一/范围/扫描

8.3 快速全索引扫描

  • 多块读
  • 索引覆盖

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

9. 优化器参数

9.1 optimizer_mode

ALTER SYSTEM SET optimizer_mode = ALL_ROWS;
-- ALL_ROWS / FIRST_ROWS_n

9.2 cursor_sharing

ALTER SYSTEM SET cursor_sharing = EXACT;

9.3 optimizer_features_enable

ALTER SYSTEM SET optimizer_features_enable = '19.1.0';

详细见：Oracle 数据库参数调优。

10. 自适应优化

10.1 Adaptive Plans（12c+）

  • 执行时调整
  • NL ↔ Hash

10.2 Adaptive Statistics

  • 12.1 默认启用
  • 12.2 默认禁用

10.3 Adaptive Cursor Sharing

  • 绑定变量窥视
  • 多计划

详细见：Oracle Adaptive Features。

11. Hint

/*+ INDEX(table idx) */     -- 索引
/*+ FULL(table) */          -- 全表
/*+ USE_HASH(a b) */        -- Hash Join
/*+ LEADING(a b) */         -- 顺序
/*+ PARALLEL(table 8) */    -- 并行

详细见：Oracle 优化器 Hint 详解。

12. 执行计划

12.1 查看

EXPLAIN PLAN FOR ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));

-- 实际
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

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

详细见：Oracle 执行计划详解。

12.2 关注

  • Cost
  • Cardinality
  • 估算 vs 实际
  • I/O

13. 10053 事件

13.1 启用

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
EXPLAIN PLAN FOR ...;
ALTER SESSION SET EVENTS '10053 trace name context off';

13.2 分析

# trace 文件
# 优化器决策过程
# 成本计算

14. 常见坑与排错

14.1 选错计划

-- 1. 统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(...);

-- 2. 直方图
-- 3. SQL Profile
-- 4. SQL Plan Baseline

14.2 计划不稳定

-- 1. SQL Plan Baseline
-- 2. 绑定变量窥视
-- 3. Adaptive Features

15. 最佳实践

  1. 统计信息：基础
  2. 直方图：数据倾斜
  3. SQL Plan Baseline：稳定
  4. HINT 谨慎：必要时
  5. 执行计划分析：根因
  6. 10053 深度：诊断
  7. Adaptive 谨慎：12c+
  8. 绑定变量：减少解析
  9. 测试验证：效果
  10. 文档化：记录

16. 参考资料

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