Oracle 优化器 CBO 原理与统计信息

适用版本：Oracle Database 19c / 23ai 阅读基础：了解 SQL 执行过程、共享池、Library Cache 文档版本：v1.0 / 2026-07

1. 概述：CBO 是什么

CBO（Cost-Based Optimizer，基于成本的优化器） 是 Oracle 默认的 SQL 优化器，负责为每条 SQL 生成执行计划[1]。

核心思想：

收集数据库对象的统计信息
对每种可能的执行计划估算成本（Cost）
选择成本最低的执行计划

与 RBO 对比[1][4]：

维度	RBO（Rule-Based）	CBO（Cost-Based）
决策依据	固定规则（15 条优先级）	统计信息估算的成本
数据敏感性	不考虑数据分布	考虑表大小、数据分布
索引使用	倾向索引	根据选择性决定
新特性	不支持分区、并行、物化视图	全部支持
适用场景	老系统兼容	现代系统（默认）
状态	10g 起废弃	默认优化器

2. RBO vs CBO 演进

RBO 访问路径优先级[1]（共 15 级，从 1 到 15 优先级递减）：

路径	优先级
Single Row by Rowid	1（最高）
Single Row by Cluster Join	2
Single Row by Hash Cluster Key with Unique or Primary Key	3
Single Row by Unique or Primary Key	4
Clustered Join	5
Hash Cluster Key	6
Indexed Cluster Key	7
Composite Index	8
Single-Column Indexes	9
Bounded Range Search on Indexed Columns	10
Unbounded Range Search on Indexed Columns	11
Sort Merge Join	12
MAX or MIN of Indexed Column	13
ORDER BY on Indexed Column	14
Full Table Scan	15（最低）

RBO 的缺陷：

永远优先索引，即使全表扫描更优
不考虑数据量
不支持分区表、并行查询、物化视图
无法适应现代硬件（CPU vs IO 平衡）

强制使用 CBO 的场景[4]：

使用并行查询或并行 DML
使用分区表
使用物化视图
使用索引组织表
使用函数索引
使用哈希连接
使用星型连接

10g 起 RBO 已废弃，但仍可通过 /*+ RULE */ hint 临时使用。

3. CBO 工作原理

CBO 由三大组件构成[1]：

┌────────────────────────────────────────────────┐
│              Cost-Based Optimizer               │
├────────────────────────────────────────────────┤
│  Query Transformer（查询转换器）                │
│    - View Merging                              │
│    - Subquery Unnesting                        │
│    - Predicate Pushing                         │
│    - Query Rewrite with MV                     │
│    - OR Expansion                              │
├────────────────────────────────────────────────┤
│  Estimator（估算器）                            │
│    - Selectivity（选择率）                      │
│    - Cardinality（基数）                        │
│    - Cost（成本）                               │
├────────────────────────────────────────────────┤
│  Plan Generator（计划生成器）                   │
│    - 生成多种执行计划                           │
│    - 估算每个计划成本                           │
│    - 选择最低成本计划                           │
└────────────────────────────────────────────────┘

3.1 查询转换器 Query Transformer

决定是否重写用户的 SQL，生成更好的执行计划[1]。

3.1.1 View Merging 视图合并

-- 原始
SELECT * FROM (
    SELECT empno, ename FROM emp WHERE deptno = 10
) WHERE ename LIKE 'S%';

-- 合并后
SELECT empno, ename FROM emp 
WHERE deptno = 10 AND ename LIKE 'S%';

3.1.2 Subquery Unnesting 子查询反嵌套

-- 原始
SELECT * FROM emp 
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'CHICAGO');

-- 反嵌套为 JOIN
SELECT emp.* FROM emp, dept
WHERE emp.deptno = dept.deptno AND dept.loc = 'CHICAGO';

3.1.3 Predicate Pushing 谓词推入

-- 把外层谓词推入视图
SELECT * FROM (
    SELECT * FROM orders
) o JOIN customers c ON o.cust_id = c.cust_id
WHERE c.region = 'WEST';

-- 推入后
SELECT * FROM (
    SELECT * FROM orders WHERE /* c.region='WEST' 不能直接推入 */
) o JOIN customers c ON o.cust_id = c.cust_id
WHERE c.region = 'WEST';

3.1.4 Query Rewrite with Materialized View

-- 原始
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

-- 如果有物化视图 mv_dept_count(deptno, cnt)
-- CBO 自动重写为：
SELECT deptno, cnt FROM mv_dept_count;

3.2 估算器 Estimator

使用统计信息估算选择率、基数、成本[1][4]。

3.2.1 Selectivity 选择率

含义：满足条件的行数占总行数的比例（0 ~ 1）。

等值条件（无直方图）：

Selectivity = 1 / NUM_DISTINCT

例：deptno 列有 10 个不同值
Selectivity = 1/10 = 0.1（10%）

范围条件（无直方图）：

Selectivity = (HIGH_VALUE - LOW_VALUE_pred) / (HIGH_VALUE - LOW_VALUE)
            + 1 / NUM_DISTINCT

例：sal BETWEEN 1000 AND 3000
   sal 范围 [800, 5000]，NUM_DISTINCT=12
Selectivity = (3000 - 1000) / (5000 - 800) + 1/12
            = 0.476 + 0.083 = 0.56

绑定变量（无窥探）：

Selectivity = 1 / NUM_DISTINCT  -- 默认假设

绑定变量（有窥探）：

Selectivity = 1 / NUM_DISTINCT  -- 仍按等值计算
但 Cardinality = Selectivity × NUM_ROWS

3.2.2 Cardinality 基数

含义：满足条件的行数估算值。

Cardinality = Selectivity × NUM_ROWS

例：emp 表 100 万行，deptno 选择率 10%
Cardinality = 0.1 × 100万 = 10万

多表 JOIN 后的 Cardinality：

JOIN Cardinality = (Card_A × Card_B) / MAX(Card_A.join_col, Card_B.join_col)

3.2.3 Cost 成本

简化公式：

Cost = IO_Cost + CPU_Cost

IO_Cost = (单块读次数 × sreadtim + 多块读次数 × mreadtim) / sreadtim
CPU_Cost = (CPU 周期数 / CPUSPEED) / (sreadtim × 1000)

参数说明：

参数	含义	来源
`sreadtim`	单块读平均时间（ms）	系统统计
`mreadtim`	多块读平均时间（ms）	系统统计
`CPUSPEED`	CPU 速度（百万指令/秒）	系统统计
`MBRC`	多块读平均块数	系统统计

典型成本对比：

操作	单块读	多块读	CPU	总成本
索引唯一扫描 + 回表	2-4	0	少	低（5-10）
索引范围扫描 + 回表	视返回行数	0	少	中（10-100）
全表扫描	0	表块数 / MBRC	多	高（百-千）
Hash Join	0	视双方	高	视数据量

3.3 计划生成器 Plan Generator

生成多种候选计划，估算成本，择优[1]。

优化器模式：

SHOW PARAMETER optimizer_mode

模式	目标
`ALL_ROWS`（默认）	总成本最低（吞吐量优先）
`FIRST_ROWS_n`	返回前 n 行成本最低（响应时间优先）
`FIRST_ROWS`	旧版兼容

改模式：

-- 系统级
ALTER SYSTEM SET optimizer_mode = ALL_ROWS SCOPE=BOTH;

-- 会话级
ALTER SESSION SET optimizer_mode = FIRST_ROWS_10;

-- 语句级（hint）
SELECT /*+ FIRST_ROWS(10) */ * FROM emp WHERE deptno = 10;

坑 1：FIRST_ROWS_n 模式可能让 CBO 选择索引扫描，即使全表扫描总成本更低。适合 OLTP 点查，不适合批量。

4. 成本模型

CBO 把成本分为三部分[1]：

Total Cost = IO Cost + CPU Cost + Network Cost

IO Cost：

单块读：db file sequential read，每次读 1 个块，访问索引时使用
多块读：db file scattered read，每次读 MBRC 个块，全表扫描使用

CPU Cost：

比较、计算、转换的 CPU 周期数

Network Cost（分布式查询）：

网络传输字节数 / 网络带宽

单块读 vs 多块读成本对比：

假设：
  sreadtim = 10 ms（单块读时间）
  mreadtim = 30 ms（多块读时间）
  MBRC = 8（多块读块数）

单块读成本/块 = 10/10 = 1
多块读成本/块 = 30/10/8 = 0.375

→ 多块读每块成本更低
→ 大范围扫描倾向全表扫描
→ 小范围扫描倾向索引扫描

Clustering Factor（聚集因子） 是索引成本的关键：

Clustering Factor：
  索引项按顺序指向数据块的"连续性"
  - 接近表块数 → 索引有序，扫描成本低
  - 接近行数 → 索引无序，扫描成本高

例：
  emp 表 1000 块，100 万行
  idx_empno（empno 唯一，CF=1000）→ 索引扫描成本低
  idx_status（status 重复多，CF=99万）→ 索引扫描成本高，倾向全表

5. 统计信息类型

5.1 表统计信息

-- 查看
SELECT table_name, num_rows, blocks, avg_row_len, last_analyzed
FROM user_tables
WHERE table_name = 'EMP';

-- 关键字段
NUM_ROWS       -- 行数
BLOCKS         -- 块数（高水位线下）
EMPTY_BLOCKS   -- 高水位线上空块
AVG_ROW_LEN    -- 平均行长
LAST_ANALYZED  -- 最后统计时间
SAMPLE_SIZE    -- 采样大小

5.2 列统计信息

SELECT column_name, num_distinct, density, num_nulls,
       low_value, high_value, histogram, last_analyzed
FROM user_tab_col_statistics
WHERE table_name = 'EMP';

-- low_value / high_value 是 RAW 类型，需转换
SELECT column_name,
       UTL_RAW.CAST_TO_NUMBER(low_value) AS low_val,
       UTL_RAW.CAST_TO_NUMBER(high_value) AS high_val
FROM user_tab_col_statistics
WHERE table_name = 'EMP' AND column_name = 'SAL';

关键字段[3]：

字段	含义	CBO 用途
`NUM_DISTINCT`	不同值数量	选择率 = 1/NUM_DISTINCT
`DENSITY`	密度	选择率（带直方图时不同）
`NUM_NULLS`	NULL 数量	估算 IS NULL / IS NOT NULL
`LOW_VALUE` / `HIGH_VALUE`	最小/最大值	范围查询选择率
`AVG_COL_LEN`	平均长度	I/O 成本
`HISTOGRAM`	直方图类型	数据分布描述

5.3 直方图

作用：描述数据分布不均的列，让 CBO 做精确选择率估算[4][5]。

4 种类型[5]：

类型	适用场景	桶数限制
Frequency（频率）	NDV ≤ 254	每个值一个桶
Top Frequency（高频）	NDV > 254，少数值占绝大多数	254 个桶
Height Balanced（高度平衡，11g 及之前）	NDV > 254，分布相对均匀	254 个桶
Hybrid（混合，12c+）	NDV > 254，分布不均	254 个桶

何时自动创建[5]：

METHOD_OPT = 'FOR ALL COLUMNS SIZE AUTO'（默认）
列被查询过（记录在 SYS.COL_USAGE$）
数据分布倾斜
下次收集统计信息时自动创建

手动创建：

-- 指定列和桶数
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    method_opt => 'FOR COLUMNS salary SIZE 100'
);

-- 只对倾斜列
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    method_opt => 'FOR COLUMNS SIZE SKEWONLY salary'
);

查看直方图：

SELECT column_name, endpoint_number, endpoint_value
FROM user_tab_histograms
WHERE table_name = 'EMP' AND column_name = 'SAL'
ORDER BY endpoint_number;

示例：直方图对执行计划的影响

-- 假设 emp.status：
-- 90% = 'ACTIVE'，10% = 其他

-- 无直方图
SELECT * FROM emp WHERE status = 'ACTIVE';
-- CBO 估算：1/2 = 50% 选择率 → 选择全表扫描

-- 有直方图
SELECT * FROM emp WHERE status = 'ACTIVE';
-- CBO 估算：90% 选择率 → 选择全表扫描（正确）

SELECT * FROM emp WHERE status = 'INACTIVE';
-- CBO 估算：10% 选择率 → 选择索引扫描（正确）

5.4 索引统计信息

SELECT index_name, blevel, leaf_blocks, distinct_keys,
       clustering_factor, num_rows, last_analyzed
FROM user_indexes
WHERE table_name = 'EMP';

关键字段：

字段	含义	CBO 用途
`BLEVEL`	B-Tree 高度	索引扫描成本（每行加 BLEVEL 次单块读）
`LEAF_BLOCKS`	叶子块数	索引扫描成本
`DISTINCT_KEYS`	不同键数	选择率
`CLUSTERING_FACTOR`	聚集因子	决定索引扫描 vs 全表扫描
`NUM_ROWS`	索引行数	基数估算

Clustering Factor 解读：

CF 接近表块数 → 索引有序，索引扫描成本低
CF 接近行数 → 索引无序，索引扫描成本高（每行都触发单独 I/O）
CF > 表块数 × 3 → 通常 CBO 会选全表扫描

5.5 系统统计信息

描述硬件性能，让 CBO 校准成本模型[2]。

SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN';

PNAME                      PVAL1
-------------------------  ------
CPUSPEEDNW                 3107   -- CPU 速度（非工作负载）
IOTFRSPEED                 4096   -- IO 传输速度（字节/ms）
IOSEEKTIM                  10     -- IO 寻道时间（ms）
MBRC                       8      -- 多块读块数（默认）
MAXTHR                     0
SLAVETHR                   0

-- 工作负载统计（如果已收集）
CPUSPEED                   -- CPU 速度（工作负载）
SREADTIM                   -- 单块读时间（ms）
MREADTIM                   -- 多块读时间（ms）

收集系统统计：

-- 非工作负载统计（轻量级，无需真实负载）
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'NOWORKLOAD');

-- 工作负载统计（在典型负载下收集，更精确）
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'START');
-- 等待 1-2 小时典型负载
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'STOP');

坑 2：工作负载统计一旦收集，CBO 会优先使用。如果收集时负载不典型，可能导致后续所有 SQL 计划变差。

5.6 多列统计信息（扩展统计）

描述多列间的关联，解决”列独立性假设”问题[2]。

-- 假设：country='中国' → city='北京' 概率 80%
-- CBO 默认假设两列独立，会严重低估选择性

-- 创建扩展统计
DECLARE
    cg_name VARCHAR2(30);
BEGIN
    cg_name := DBMS_STATS.CREATE_EXTENDED_STATS(
        ownname  => 'SCOTT',
        tabname  => 'CUSTOMERS',
        extension => '(country, city)'
    );
END;
/

-- 收集统计（包含扩展）
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'CUSTOMERS',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);

-- 查看扩展统计
SELECT extension_name, extension
FROM user_stat_extensions
WHERE table_name = 'CUSTOMERS';

6. 统计信息收集：DBMS_STATS

6.1 常用过程

-- 表级
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE,  -- 同时收集索引
    degree => 4
);

-- Schema 级
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname => 'SCOTT',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE,
    degree => 4
);

-- 数据库级
EXEC DBMS_STATS.GATHER_DATABASE_STATS(
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE
);

-- 数据字典统计
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

-- 固定对象统计
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

-- 系统统计
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');

6.2 关键参数详解

参数	含义	推荐值
`ESTIMATE_PERCENT`	采样比例	`DBMS_STATS.AUTO_SAMPLE_SIZE`（11g 起自动）
`METHOD_OPT`	列统计策略	`'FOR ALL COLUMNS SIZE AUTO'`（默认）
`CASCADE`	是否收集索引统计	`TRUE`
`DEGREE`	并行度	4-8
`GRANULARITY`	分区表粒度	`'ALL'`（默认，含分区和子分区）
`OPTIONS`	收集选项	`'GATHER'`（全部）/ `'GATHER EMPTY'` / `'GATHER STALE'`
`FORCE`	强制收集（即使锁定）	`FALSE`

6.3 收集策略

DBA 视角的最佳实践[2]：

策略优先：为核心应用制定独立收集策略
DBMS_STATS 是唯一选择（不用 ANALYZE）
因”地”制宜：
- 数据倾斜列：建直方图
- 列关联：建多列统计
- 分区表：增量收集
主动管理：自定义 Job 控制
懂得锁定：特殊表用 LOCK_TABLE_STATS

-- 锁定表统计
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT', 'CRITICAL_TABLE');

-- 解锁
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCOTT', 'CRITICAL_TABLE');

7. 自动统计信息收集

Oracle 11g 起默认开启自动统计信息收集任务[2]。

-- 查看自动任务状态
SELECT client_name, status, attributes
FROM dba_autotask_client
WHERE client_name LIKE '%stats%';

-- 查看维护窗口
SELECT window_name, repeat_interval, duration
FROM dba_scheduler_windows
WHERE enabled = 'TRUE';

-- 禁用自动收集
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL
);

-- 启用
EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(...);

自动收集触发条件：

表无统计信息
表行数变化 > 10%（基于 DBA_TAB_MODIFICATIONS）
在维护窗口（默认 22:00-02:00）

坑 3：自动收集的局限[2]：

一刀切策略无法满足所有表
运行时间可能与批量任务冲突
关键业务不能接受 24 小时延迟

生产推荐：

保留自动收集作为”保底”
核心表自定义 Job 在低峰期收集
监控 LAST_ANALYZED 是否符合预期

8. 统计信息锁定与历史

8.1 统计信息锁定

-- 锁定
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT', 'EMP');

-- 锁定后 GATHER_*_STATS 会跳过该表
-- 强制收集（不推荐）
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP', FORCE => TRUE);

-- 解锁
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCOTT', 'EMP');

适用场景：

全局临时表
容易产生差执行计划的表
需要稳定执行计划的关键表

8.2 统计信息历史

Oracle 自动保留 31 天的统计信息历史，可回滚[2]。

-- 查看历史
SELECT table_name, stats_update_time
FROM dba_tab_stats_history
WHERE table_name = 'EMP'
ORDER BY stats_update_time DESC;

-- 恢复到指定时间
EXEC DBMS_STATS.RESTORE_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    as_of_timestamp => TO_TIMESTAMP('2026-07-20 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
);

-- 查看保留时间
SELECT stats_history_availability FROM dba_optstat_operation_params;
-- 默认 31 天

-- 修改保留时间
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60);  -- 60 天

8.3 待定统计信息（Pending Stats）

-- 设置发布模式为 pending
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'EMP', 'PUBLISH', 'FALSE');

-- 收集后不立即生效，存入 pending
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP');

-- 测试 pending 统计
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
SELECT /*+ TEST */ * FROM emp;  -- 使用 pending 统计

-- 满意后发布
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SCOTT', 'EMP');

适用场景：在测试环境验证统计信息对执行计划的影响。

9. 统计信息查看与诊断

9.1 查看对象统计

-- 表统计
SELECT table_name, num_rows, blocks, last_analyzed, stale_stats
FROM dba_tab_statistics
WHERE owner = 'SCOTT';

-- 列统计
SELECT table_name, column_name, num_distinct, density, histogram, last_analyzed
FROM dba_tab_col_statistics
WHERE owner = 'SCOTT' AND table_name = 'EMP';

-- 索引统计
SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor, last_analyzed
FROM dba_ind_statistics
WHERE table_owner = 'SCOTT' AND table_name = 'EMP';

-- 直方图详情
SELECT column_name, endpoint_number, endpoint_value
FROM dba_tab_histograms
WHERE owner = 'SCOTT' AND table_name = 'EMP' AND column_name = 'SAL'
ORDER BY endpoint_number;

-- 扩展统计
SELECT table_name, extension_name, extension
FROM dba_stat_extensions
WHERE owner = 'SCOTT';

9.2 查看陈旧统计

-- 查找陈旧统计（变化 > 10%）
SELECT table_name, num_rows, stale_stats, last_analyzed
FROM dba_tab_statistics
WHERE owner = 'SCOTT' AND stale_stats = 'YES';

-- 查看修改计数
SELECT table_name, inserts, updates, deletes, timestamp
FROM dba_tab_modifications
WHERE table_owner = 'SCOTT';

9.3 收集操作历史

-- 查看统计信息收集历史
SELECT operation, target, start_time, end_time
FROM dba_optstat_operations
ORDER BY start_time DESC
FETCH FIRST 20 ROWS ONLY;

9.4 10053 事件诊断

当怀疑 CBO 选错执行计划时，用 10053 跟踪[4]。

-- 启用 10053 跟踪
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

-- 执行 SQL（必须是硬解析）
SELECT * FROM emp WHERE empno = 7900;

-- 关闭
ALTER SESSION SET EVENTS '10053 trace name context off';

-- 查找 trace 文件
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

10053 trace 内容[4]：

优化器参数
SQL 文本和重写
基本统计信息
各访问路径的成本计算
各连接方式的成本计算
最终选择的执行计划

10. 常见坑与最佳实践

坑 1：统计信息过期导致计划翻转

现象：白天 SQL 正常，凌晨自动收集统计信息后变慢。

解决：

检查 LAST_ANALYZED 时间
用 RESTORE_TABLE_STATS 恢复旧统计
分析新统计与旧统计差异
锁定表统计，或用 SPM 固化执行计划

坑 2：直方图导致执行计划不稳定

现象：相同 SQL 不同绑定变量值，执行计划不同。

原因：直方图 + ACS 共同作用，产生多个子游标。

解决：

-- 删除直方图
EXEC DBMS_STATS.DELETE_COLUMN_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    colname => 'STATUS',
    col_stat_type => 'HISTOGRAM'
);

-- 锁定表统计
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT', 'EMP');

坑 3：自动收集在批量任务期间运行

现象：批量任务变慢，alert log 显示统计信息收集。

解决：

-- 1. 调整维护窗口
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'MONDAY_WINDOW',
    attribute => 'REPEAT_INTERVAL',
    value => 'freq=daily;byday=MON;byhour=2;byminute=0;bysecond=0'
);

-- 2. 关闭特定窗口的自动收集
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => 'MONDAY_WINDOW'
);

坑 4：分区表统计不全

现象：分区表查询计划差，全局统计和分区统计不一致。

解决：

-- 增量收集（仅变更分区）
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'SALES_PARTITIONED',
    partname => 'SALES_2026_07',  -- 指定分区
    granularity => 'ALL',
    cascade => TRUE
);

-- 配置增量偏好
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'SALES_PARTITIONED', 'INCREMENTAL', 'TRUE');

坑 5：系统统计收集时机不当

现象：收集系统统计后大量 SQL 计划变差。

解决：

-- 删除系统统计，恢复默认
EXEC DBMS_STATS.DELETE_SYSTEM_STATS;

-- 重新在典型负载下收集
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');
-- 等待典型负载时段
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

最佳实践总结

使用 DBMS_STATS，禁用 ANALYZE（老工具，不收集所有统计）
AUTO_SAMPLE_SIZE：11g 起自动采样已足够精确
直方图仅对倾斜列：不要对均匀分布列建直方图
多列统计：列关联明显时使用
系统统计：仅在典型负载下收集
锁定关键表：避免意外变化
统计历史：定期监控并测试
Pending Stats：测试环境验证
增量收集：分区表首选
SPM 固化：关键 SQL 用 SQL Plan Baseline 锁定

11. 参考资料

[1] Oracle Database 19c 概念文档，第 13 章 The Query Optimizer： https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/optimizer.html

[2] 青年数据库学习互助会，《DBA 性能调优内功心法（十五）：从多列到系统统计，构建性能优化的全局视野》，墨天轮： https://www.modb.pro/db/1955462628176834560

[3] michaelliu，《深入解析 DBA_TAB_COL_STATISTICS 视图》，墨天轮： https://www.modb.pro/db/2019977994408910848

[4] 墨天轮，《Oracle 调优之 Trace 方法及相关工具总结 02》： https://www.modb.pro/db/1777223226882625536

[5] 布衣，《性能优化-直方图对 CBO 的影响》，墨天轮： https://www.modb.pro/db/1953312423843213312

[6] Oracle Database 19c SQL Tuning Guide： https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/

[7] Oracle Database 19c Database Performance Tuning Guide： https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/

[8] Oracle Database 12.2 文档，Histograms： https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/histograms.html

相关文章

统计信息类型（表/列/索引/系统）

DBMS_STATS 收集统计信息

直方图类型与选择

扩展统计信息（Extended Statistics）

执行计划读取与分析

10053 事件优化器跟踪

Adaptive Query Optimization

Oracle 优化器 CBO 原理与统计信息

Oracle 优化器 CBO 原理与统计信息

目录

1. 概述：CBO 是什么

2. RBO vs CBO 演进

3. CBO 工作原理

3.1 查询转换器 Query Transformer

3.1.1 View Merging 视图合并

3.1.2 Subquery Unnesting 子查询反嵌套

3.1.3 Predicate Pushing 谓词推入

3.1.4 Query Rewrite with Materialized View

3.2 估算器 Estimator

3.2.1 Selectivity 选择率

3.2.2 Cardinality 基数

3.2.3 Cost 成本

3.3 计划生成器 Plan Generator

4. 成本模型

5. 统计信息类型

5.1 表统计信息

5.2 列统计信息

5.3 直方图

5.4 索引统计信息

5.5 系统统计信息

5.6 多列统计信息（扩展统计）

6. 统计信息收集：DBMS_STATS

6.1 常用过程

6.2 关键参数详解

6.3 收集策略

7. 自动统计信息收集

8. 统计信息锁定与历史

8.1 统计信息锁定

8.2 统计信息历史

8.3 待定统计信息（Pending Stats）

9. 统计信息查看与诊断

9.1 查看对象统计

9.2 查看陈旧统计

9.3 收集操作历史

9.4 10053 事件诊断

10. 常见坑与最佳实践

坑 1：统计信息过期导致计划翻转

坑 2：直方图导致执行计划不稳定

坑 3：自动收集在批量任务期间运行

坑 4：分区表统计不全

坑 5：系统统计收集时机不当

最佳实践总结

11. 参考资料