DB / Oracle / 性能诊断

Oracle 优化器（CBO）原理

2026.09.06

Oracle 优化器（CBO）原理

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

1. 概述

优化器（Optimizer） 选择最优执行计划[1]：

类型说明
RBO基于规则（已废弃）
CBO基于成本（默认）

2. CBO 基础

2.1 成本模型

Cost = IO Cost + CPU Cost

2.2 基数估算

Cardinality = 表行数 × 选择率

2.3 选择率

  • 等值：1 / NDV
  • 范围：1 / 3
  • BETWEEN：1 / 12

3. 统计信息

3.1 表统计

SELECT table_name, num_rows, blocks, avg_row_len
FROM user_tables WHERE table_name = 'EMPLOYEES';

3.2 列统计

SELECT column_name, num_distinct, density, num_nulls, low_value, high_value
FROM user_tab_col_statistics
WHERE table_name = 'EMPLOYEES';

3.3 索引统计

SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

3.4 直方图

SELECT table_name, column_name, histogram
FROM user_tab_col_statistics
WHERE histogram != 'NONE';

4. 聚簇因子（Clustering Factor）

4.1 含义

  • 索引行与表行的物理顺序相似度
  • 低：索引有效
  • 高：索引低效

4.2 查看

SELECT index_name, clustering_factor
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

4.3 优化

  • 排序数据加载
  • 重建表
  • 分区

5. 直方图

5.1 类型

类型说明
FREQUENCY频率（< 254 NDV）
HEIGHT BALANCED高度平衡（≥ 254 NDV）

5.2 适用

  • 数据倾斜
  • 等值查询

5.3 创建

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  'SCOTT', 'EMPLOYEES',
  method_opt => 'FOR COLUMNS dept_id SIZE 254'
);

6. 绑定变量窥视

6.1 9i+ 默认

  • 第一次执行窥视绑定值
  • 生成执行计划

6.2 问题

  • 数据倾斜：不同值需要不同计划

6.3 自适应游标（11g+）

-- 查看绑定敏感性
SELECT sql_id, child_number, bind_sensitive, bind_aware
FROM v$sql
WHERE sql_id = '&sql_id';

7. 优化器参数

7.1 OPTIMIZER_MODE

ALTER SYSTEM SET optimizer_mode = ALL_ROWS SCOPE=SPFILE;
-- ALL_ROWS: 优化吞吐
-- FIRST_ROWS_n: 优化响应（1/10/100/1000）
-- FIRST_ROWS: 向后兼容

7.2 OPTIMIZER_FEATURES_ENABLE

ALTER SYSTEM SET optimizer_features_enable = '19.1.0';

7.3 OPTIMIZER_INDEX_COST_ADJ

-- 索引成本调整（默认 100）
ALTER SESSION SET optimizer_index_cost_adj = 50;
-- 越小越倾向用索引

7.4 OPTIMIZER_INDEX_CACHING

-- 索引缓存率（默认 0）
ALTER SESSION SET optimizer_index_caching = 90;

7.5 DB_FILE_MULTIBLOCK_READ_COUNT

ALTER SYSTEM SET db_file_multiblock_read_count = 16;

8. 动态采样

8.1 启用

ALTER SESSION SET optimizer_dynamic_sampling = 2;
-- 0: 关闭
-- 2: 默认
-- 11: 最高

8.2 应用

  • 缺少统计信息
  • 临时表
  • 复杂查询

8.3 HINT

SELECT /*+ DYNAMIC_SAMPLING(e 4) */ * FROM employees e;

9. HINT 详解

9.1 优化器

/*+ ALL_ROWS */
/*+ FIRST_ROWS(10) */
/*+ RULE */  -- RBO

9.2 访问路径

/*+ FULL(table) */
/*+ INDEX(table idx_name) */
/*+ INDEX_FFS(table idx_name) */
/*+ NO_INDEX(table) */
/*+ INDEX_DESC(table idx_name) */

9.3 JOIN

/*+ USE_NL(t1 t2) */
/*+ USE_HASH(t1 t2) */
/*+ USE_MERGE(t1 t2) */
/*+ LEADING(t1) */
/*+ ORDERED */
/*+ SWAP_JOIN_INPUTS(t2) */

9.4 其他

/*+ PARALLEL(t 4) */
/*+ APPEND */          -- 直接路径插入
/*+ CARDINALITY(t 1000) */
/*+ DYNAMIC_SAMPLING(t 4) */
/*+ MONITOR */

10. SQL Profile

10.1 生成

DECLARE
  v_task VARCHAR2(100);
BEGIN
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_task);
END;
/

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM dual;

10.2 接受

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
  task_name => '&task_name',
  name => 'my_profile'
);

10.3 管理

-- 查看
SELECT * FROM dba_sql_profiles;

-- 删除
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('my_profile');

11. SQL Plan Baseline

11.1 捕获

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

11.2 查看

SELECT * FROM dba_sql_plan_baselines;

11.3 演进

-- 测试新计划
EXEC DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => '&handle');

12. 10053 事件

12.1 优化器决策

ALTER SESSION SET events '10053 trace name context forever, level 1';

EXPLAIN PLAN FOR SELECT ...;

ALTER SESSION SET events '10053 trace name context off';

12.2 内容

  • 表统计
  • 索引统计
  • 成本计算
  • 计划选择

13. 常见坑与排错

13.1 CBO 选错计划

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

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

13.2 统计信息过期

-- 自动收集
SELECT * FROM dba_autotask_client;

-- 手动
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

13.3 数据倾斜

-- 直方图
method_opt => 'FOR COLUMNS col SIZE 254'

14. 最佳实践

  1. 定期收集统计信息：CBO 准确
  2. 直方图处理倾斜：精确
  3. 绑定变量：减少硬解析
  4. HINT 谨慎：仅必要时
  5. SQL Profile：自动优化
  6. SQL Plan Baseline：稳定计划
  7. 10053 诊断：决策分析
  8. 动态采样：临时表
  9. 测试不同计划：选择最优
  10. 监控性能：持续优化

15. 参考资料

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