Oracle 统计信息治理实战：从直方图失真到增量收集
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：摸清现状基线。
-- 哪些表的统计已过期（DML 超 10% 未收集）
SELECT owner, table_name, num_rows, last_analyzed, stale_stats
FROM dba_tab_statistics
WHERE owner = 'ERP' AND stale_stats = 'YES'
ORDER BY num_rows DESC;
-- 看大表的收集偏好
SELECT dbms_stats.get_prefs('INCREMENTAL', 'ERP', 'T_ORDER') FROM dual;
SELECT dbms_stats.get_prefs('ESTIMATE_PERCENT', 'ERP', 'T_ORDER') FROM dual;
-- 夜间自动任务窗口和上次执行情况
SELECT client_name, status, last_start_date
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';
步骤 1：复现估算偏差，拿到证据。
EXPLAIN PLAN FOR
SELECT count(*) FROM erp.t_order WHERE order_status = 'FAILED';
SELECT * FROM table(dbms_xplan.display);
-- 真实执行对比估算
SELECT /*+ gather_plan_statistics */ count(*)
FROM erp.t_order WHERE order_status = 'FAILED';
SELECT * FROM table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
输出里 E-Rows 是 5000 万、A-Rows 是 63 万，差两个数量级，证据就齐了。
步骤 2：给倾斜列建直方图并验证。
EXEC dbms_stats.gather_table_stats('ERP','T_ORDER', -
method_opt => 'FOR COLUMNS order_status SIZE 254', -
cascade => TRUE);
-- 验证直方图类型与桶数
SELECT column_name, histogram, num_buckets, density
FROM dba_tab_col_statistics
WHERE owner='ERP' AND table_name='T_ORDER'
AND column_name='ORDER_STATUS';
再跑一遍步骤 1 的验证查询，E-Rows 应该贴近 A-Rows。最后把收集方式恢复为 AUTO 策略，别让手工 method_opt 一直挂着：
EXEC dbms_stats.set_table_prefs('ERP','T_ORDER','METHOD_OPT','FOR ALL COLUMNS SIZE AUTO');
步骤 3：分区大表开增量收集。
EXEC dbms_stats.set_table_prefs('ERP','T_ORDER_LOG','INCREMENTAL','TRUE');
EXEC dbms_stats.set_table_prefs('ERP','T_ORDER_LOG','GRANULARITY','AUTO');
EXEC dbms_stats.gather_table_stats('ERP','T_ORDER_LOG', cascade => TRUE);
INCREMENTAL 生效后，每次只收集有改动的分区，synopsis 汇总成全局统计，不再全表扫描。4 亿行的表收集时间从 3 小时 20 分降到 26 分钟，就是这一步的收益。
步骤 4：批量作业窗口锁住统计。
-- 作业开始前
EXEC dbms_stats.lock_table_stats('ERP','T_MONTHLY_SETTLE');
-- 作业跑完，解锁并立即收集一次
EXEC dbms_stats.unlock_table_stats('ERP','T_MONTHLY_SETTLE');
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
统计信息治理没有高深的东西，就是三件事：倾斜列给直方图、大表走增量、批量窗口锁统计。把这三件做实，执行计划劣化的工单基本就绝迹了。
页:
[1]