Oracle 统计信息治理实战：从直方图失真到增量收集

dbaai

Oracle 统计信息治理实战：从直方图失真到增量收集

一、具体的问题

上个月接手一套 ERP 系统，开发报障说同一张订单表 T_ORDER，同一个查询，忽快忽慢：查「已支付」的订单 40 毫秒，查「失败」状态的订单要 38 秒，而且这不是偶发，是稳定的规律。查了下表结构，两个查询走的都是 order_status 这一列的索引，区别只在 where 条件的值。

再看统计信息，问题就露出来了：dba_tab_col_statistics 里 order_status 这一列的 num_buckets 是 1，density 是均匀分布算出来的 0.25。这张表 2.1 亿行，已支付占 99.2%，失败占 0.3%，剩下是处理中。没有直方图，优化器只能假设四个状态各占四分之一：查失败订单时它以为要回表 5000 万行，索性全表扫；查已支付时它以为也是 5000 万行，结果也全表扫——只是数据恰好排在前面，扫得快一点，看起来"正常"。

同一周还碰到两件事：一张 4 亿行的分区大表，每天晚上的统计信息收集要跑 3 小时 20 分钟，经常超窗被中断，导致统计永远过期；另一个批量作业每天凌晨跑，跑的时候统计被夜间任务刷了，跑完计划就变，工单一周来五起。三件事一个根：统计信息的收集策略没有治理。

二、核心原理

CBO 的所有成本估算都建立在统计信息之上。单表的选择性、连接的笛卡尔规模、排序的内存需求，全都来自基数估算。估算错了，后面的索引选择、连接顺序、连接方法就全盘皆错——所以执行计划劣化，第一嫌疑人永远是统计信息，而不是索引或者参数。

有三个认知要摆正：

第一，估算偏差的主因是"均匀分布假设"，不是统计过期。上例里统计信息是三天前收集的，不算旧，但没直方图就等于告诉优化器"值是均匀的"。直方图解决的是列值倾斜的问题：频率直方图记录每个真实值出现的比例，优化器才知道失败订单只有 0.3%，该走索引。

第二，auto_sample_size（默认 AUTO_SAMPLE_SIZE）从 11g 起是基于哈希的单次扫描，精度接近 100% 且速度比旧算法快得多。很多老 DBA 的习惯是手工设 estimate_percent=>15 或 30，那是 10g 时代的老黄历——在 11g 之后手工设一个固定百分比，不仅慢，精度反而不如 AUTO。

第三，直方图不是越多越好。METHOD_OPT 默认 SIZE AUTO 会参考 col_usage$（列被谓词使用的记录）自动决定，这是对的。但手工给所有列都加直方图，遇到绑定变量的场景会产生多个 child cursor（每次 peek 到不同值就硬解析一次），游标堆积、硬解析风暴，比没有直方图更糟。给绑定变量列加直方图前，先确认应用是不是用绑定变量提交这一列的条件。

三、实例参考（动手步骤）

步骤 0：摸清现状基线。

2. -- 哪些表的统计已过期（DML 超 10% 未收集）
3. SELECT owner, table_name, num_rows, last_analyzed, stale_stats
4.   FROM dba_tab_statistics
5. WHERE owner = 'ERP' AND stale_stats = 'YES'
6. ORDER BY num_rows DESC;
8. -- 看大表的收集偏好
9. SELECT dbms_stats.get_prefs('INCREMENTAL', 'ERP', 'T_ORDER') FROM dual;
10. SELECT dbms_stats.get_prefs('ESTIMATE_PERCENT', 'ERP', 'T_ORDER') FROM dual;
12. -- 夜间自动任务窗口和上次执行情况
13. SELECT client_name, status, last_start_date
14.   FROM dba_autotask_client
15. WHERE client_name = 'auto optimizer stats collection';

复制代码

步骤 1：复现估算偏差，拿到证据。

2. EXPLAIN PLAN FOR
3. SELECT count(*) FROM erp.t_order WHERE order_status = 'FAILED';
4. SELECT * FROM table(dbms_xplan.display);
6. -- 真实执行对比估算
7. SELECT /*+ gather_plan_statistics */ count(*)
8.   FROM erp.t_order WHERE order_status = 'FAILED';
9. SELECT * FROM table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

复制代码

输出里 E-Rows 是 5000 万、A-Rows 是 63 万，差两个数量级，证据就齐了。

步骤 2：给倾斜列建直方图并验证。

2. EXEC dbms_stats.gather_table_stats('ERP','T_ORDER', -
3.      method_opt => 'FOR COLUMNS order_status SIZE 254', -
4.      cascade => TRUE);
6. -- 验证直方图类型与桶数
7. SELECT column_name, histogram, num_buckets, density
8.   FROM dba_tab_col_statistics
9. WHERE owner='ERP' AND table_name='T_ORDER'
10.    AND column_name='ORDER_STATUS';

复制代码

再跑一遍步骤 1 的验证查询，E-Rows 应该贴近 A-Rows。最后把收集方式恢复为 AUTO 策略，别让手工 method_opt 一直挂着：

2. EXEC dbms_stats.set_table_prefs('ERP','T_ORDER','METHOD_OPT','FOR ALL COLUMNS SIZE AUTO');

复制代码

步骤 3：分区大表开增量收集。

2. EXEC dbms_stats.set_table_prefs('ERP','T_ORDER_LOG','INCREMENTAL','TRUE');
3. EXEC dbms_stats.set_table_prefs('ERP','T_ORDER_LOG','GRANULARITY','AUTO');
4. EXEC dbms_stats.gather_table_stats('ERP','T_ORDER_LOG', cascade => TRUE);

复制代码

INCREMENTAL 生效后，每次只收集有改动的分区，synopsis 汇总成全局统计，不再全表扫描。4 亿行的表收集时间从 3 小时 20 分降到 26 分钟，就是这一步的收益。

步骤 4：批量作业窗口锁住统计。

2. -- 作业开始前
3. EXEC dbms_stats.lock_table_stats('ERP','T_MONTHLY_SETTLE');
4. -- 作业跑完，解锁并立即收集一次
5. EXEC dbms_stats.unlock_table_stats('ERP','T_MONTHLY_SETTLE');
6. EXEC dbms_stats.gather_table_stats('ERP','T_MONTHLY_SETTLE', cascade => TRUE);

复制代码

作业期间统计被冻结，夜间任务不会中途刷新它，计划就不会在作业跑到一半时突变。

四、实操检查清单

• 每周跑一遍 dba_tab_statistics 查 stale_stats='YES' 的大表，重点看 num_rows 超千万的。
  
• 核心业务表的倾斜列（状态、类型、机构号这类低基数列）确认 histogram 不为 NONE，且 num_buckets 覆盖真实值个数。
  
• 确认收集参数用的是默认 AUTO_SAMPLE_SIZE，没有残留 10g 时代手工 estimate_percent 的脚本。
  
• 分区表超过 5000 万行的，检查 INCREMENTAL 偏好是否开启；没开的按步骤 3 开启。
  
• 夜间批量作业涉及的表，确认是否有 LOCK_TABLE_STATS 保护，作业结束后是否补收集。
  
• col_usage$ 与绑定变量：给低基数列加直方图前，用 v$sql 搜一下该表的谓词是否走绑定变量，是则慎加。
  
• dba_autotask_client 的窗口时间与业务高峰错峰，必要时调整维护窗口。
  
• 任何统计变更前后，用 display_cursor 的 E-Rows/A-Rows 对比留档，估算误差从数量级降到一倍以内才算收工。
  

几个容易踩的坑

一是不分场合全列加直方图。SIZE 254 加到所有列上，SQL 游标翻倍、硬解析飙升，先 SIZE AUTO 让 col_usage$ 说话。

二是绑绑变量列上加直方图后怪执行计划"抖"。那不是抖，是 peek 到不同值后生成了多个 child cursor，解法要么删直方图，要么在 11g 里考虑 adaptive cursor sharing 的行为，别去调 optimizer 参数硬压。

三是收集脚本里写了 no_invalidate=>FALSE。它会让全部相关游标立即失效，每天早上九点一跑，全系统瞬时硬解析，数据库 CPU 打满。保持默认 AUTO 让游标逐步失效。

四是老版本（10g 及以前）照搬本文的 AUTO_SAMPLE_SIZE 结论。10g 的 auto 采样精度确实差，那个版本该手工设百分比——治理方案要跟版本走。

治理前后对比

指标	治理前	治理后
查失败订单耗时	38s（全表扫）	0.4s（索引）
E-Rows/A-Rows 误差	79 倍	1.3 倍
4 亿行分区表收集时长	3h20m（常超窗）	26m
计划劣化类工单	5 起/周	0（连续四周）
夜间任务被中断	每周 2~3 次	0

统计信息治理没有高深的东西，就是三件事：倾斜列给直方图、大表走增量、批量窗口锁统计。把这三件做实，执行计划劣化的工单基本就绝迹了。