一、开篇统计信息是CBO的“食材”某天DBA发现一条核心SQL的执行计划突然从索引扫描变成了全表扫描。font color#D32F2F根因排查后发现自动统计信息收集任务在大促前一天晚上运行收集了新的统计信息。由于采样比例太低新的统计信息不准确导致CBO对数据分布产生了错误判断。/fontfont color#1976D2统计信息是CBO优化器的“食材”——食材不新鲜做出来的菜执行计划必然难吃。准确的统计信息是良好执行计划的基石而DBMS_STATS包是管理统计信息的核心工具。/font本篇文章从统计信息的重要性、收集策略、直方图管理、扩展统计、分区统计到自动化维护全面覆盖Oracle统计信息管理的知识体系。二、统计信息全景架构图索引统计信息列统计信息表统计信息统计信息类别统计信息表统计信息索引统计信息列统计信息系统统计信息NUM_ROWS-行数BLOCKS-块数AVG_ROW_LEN-平均行长度NUM_DISTINCT-基数NUM_NULLS-空值数直方图-HistogramBLEVEL-层级LEAF_BLOCKS-叶块数CLUSTERING_FACTOR-集群因子关键解读font color#1976D2蓝色表统计信息/font 表级别的基础统计数据。font color#D32F2F红色直方图/font 列级别的数据分布统计解决数据倾斜问题。font color#F57C00橘色集群因子/font 索引排序与数据物理存储的相似度影响索引扫描效率。三、统计信息基础1. font color#1976D2查看统计信息/font-- 查看表统计信息 SELECT table_name, num_rows, blocks, avg_row_len, last_analyzed FROM dba_tables WHERE table_name ORDERS; -- 查看列统计信息 SELECT column_name, num_distinct, num_nulls, density, histogram, num_buckets FROM dba_tab_col_statistics WHERE table_name ORDERS AND column_name STATUS; -- 查看索引统计信息 SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows, last_analyzed FROM dba_indexes WHERE table_name ORDERS;2. font color#F57C00统计信息收集的基本方法/font-- 收集表的统计信息默认采样 EXEC DBMS_STATS.GATHER_TABLE_STATS(SCOTT, ORDERS); -- 100%采样最准确 EXEC DBMS_STATS.GATHER_TABLE_STATS(SCOTT, ORDERS, estimate_percent 100); -- 同时收集索引统计信息 EXEC DBMS_STATS.GATHER_TABLE_STATS(SCOTT, ORDERS, cascade TRUE); -- 收集全库统计信息 EXEC DBMS_STATS.GATHER_DATABASE_STATS;四、直方图管理1. font color#D32F2F直方图的作用/font精确描述直方图记录列中不同值的分布频率。font color#D32F2F当列的数据分布不均匀时直方图帮助CBO更准确地估算基数。没有直方图CBO会假设所有值均匀分布。/font2. font color#1976D2创建直方图/font-- 自动判断是否需要直方图 EXEC DBMS_STATS.GATHER_TABLE_STATS(SCOTT, ORDERS, method_opt FOR ALL COLUMNS SIZE AUTO); -- 为特定列创建直方图 EXEC DBMS_STATS.GATHER_TABLE_STATS(SCOTT, ORDERS, method_opt FOR COLUMNS status SIZE 254); -- 查看直方图类型 SELECT column_name, histogram, num_buckets FROM dba_tab_col_statistics WHERE table_name ORDERS AND column_name STATUS;3. font color#F57C00直方图使用建议/font应该创建直方图的列WHERE条件频繁使用、数据分布严重不均匀的列。不应该创建直方图的列主键列、不用于查询条件的列、数据均匀分布的列。五、扩展统计信息1. font color#1976D2解决列相关性问题/font-- 创建列组告诉CBO city和country是相关的 SELECT DBMS_STATS.CREATE_EXTENDED_STATS( ownname SCOTT, tabname CUSTOMERS, extension (city, country) ) AS extension_name FROM dual; -- 收集扩展统计 EXEC DBMS_STATS.GATHER_TABLE_STATS(SCOTT, CUSTOMERS, method_opt FOR ALL COLUMNS SIZE AUTO);六、统计信息自动化管理1. font color#1976D2查看自动收集任务状态/fontSELECT client_name, status, last_run_duration, next_run_time FROM dba_autotask_client WHERE client_name auto optimizer stats collection;2. font color#F57C00统计信息锁定/font-- 锁定表的统计信息防止自动任务覆盖 EXEC DBMS_STATS.LOCK_TABLE_STATS(SCOTT, ORDERS); -- 解锁 EXEC DBMS_STATS.UNLOCK_TABLE_STATS(SCOTT, ORDERS); -- 查看锁定状态 SELECT table_name, stattype_locked FROM dba_tables WHERE owner SCOTT;3. font color#388E3C统计信息导出与导入/font-- 创建统计信息备份表 EXEC DBMS_STATS.CREATE_STAT_TABLE(SCOTT, STATS_BACKUP, USERS); -- 导出统计信息 EXEC DBMS_STATS.EXPORT_TABLE_STATS(SCOTT, ORDERS, stattab STATS_BACKUP, statid BEFORE_UPGRADE); -- 导入统计信息 EXEC DBMS_STATS.IMPORT_TABLE_STATS(SCOTT, ORDERS, stattab STATS_BACKUP, statid BEFORE_UPGRADE);七、总结记住这个“食材仓库”类比就够了font color#1976D2【统计信息是食材CBO是大厨】/font表统计信息font color#1976D2食材的种类和数量表有多少行。/font直方图font color#D32F2F食材的质量分布80%的西红柿是烂的。/font扩展统计font color#F57C00食材的搭配关系西红柿和鸡蛋经常一起用。/font统计信息锁定font color#388E3C把食材清单封存防止别人乱改。/font统计信息导出font color#7B1FA2给食材清单拍照备份出问题可以恢复。/fontfont color#1976D2准确的统计信息是良好执行计划的基石。掌握DBMS_STATS的收集策略、直方图管理和统计信息导出是DBA进行SQL性能调优的核心技能。/font你在日常运维中是否遇到过因统计信息不准确导致的执行计划问题欢迎评论区分享你的经验。