直方图

  Oracle 直方图(Histogram)是一种特殊的统计信息，用于描述表中列的数据分布情况。它的主要作用是解决当数据分布不均匀时，基于成本的优化器(CBO)可能做出错误执行计划的问题。
  在没有直方图的情况下，CBO会默认认为目标列的数据在其最小值(LOW_VALUE)和最大值(HIGH_VALUE)之间是均匀分布的，并按照这个均匀分布原则来计算对目标列施加WHERE查询条件后的可选择率以及结果集的Cardinality(基数)。然而在实际系统中，很多表的列数据分布是不均匀的，甚至是极度倾斜的。直方图通过记录列值的实际分布频率，帮助优化器更准确地评估查询条件的选择性。
  直方图存储在数据字典基表SYS.HISTGRM$中，可以通过DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS等数据字典视图查看。

注：当Oracle做直方图分析时，会将要分析的列上的数据分成很多数量相同的部分，每一部分称为一个bucket(桶)，这种数据的分布将作为一个非常重要的因素纳入到执行成本的计算当中。

直方图类型

• 1、频率直方图(Frequency Histogram)‌：每个不同的列值对应一个桶，适用于唯一值数量较少的列(≤254)

• 2、‌高度均衡直方图(Height Balanced Histogram)‌：将数据按值排序后均匀分配到固定数量的桶中(11g及之前版本)

• 3、高频率直方图(Top Frequency Histogram)‌：只记录高频出现的值及其频率(12c引入)

• 4、‌混合直方图(Hybrid Histogram)‌：结合频率和高度均衡的特点(12c引入)

创建方法

直方图通过DBMS_STATS包的GATHER_TABLE_STATS过程创建，主要使用method_opt参数控制：

-- 手动指定列和桶数
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCHEMA_NAME',
    tabname => 'TABLE_NAME',
    method_opt => 'FOR COLUMNS COLUMN_NAME SIZE 100'
);

-- 自动决定直方图创建
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCHEMA_NAME',
    tabname => 'TABLE_NAME',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);

其中SIZE指定桶的数量，常用选项包括：

• 具体数字(如100)：创建指定桶数的直方图
• AUTO：由Oracle自动决定
• SKEWONLY：仅对数据倾斜的列创建

直方图对查询优化的影响

直方图通过以下方式显著影响Oracle查询优化器的决策：

• 1、‌Cardinality评估准确性‌：
  提供更精确的行数估计，避免均匀分布假设导致的评估错误。
• 2、访问路径选择‌：
  帮助优化器判断使用索引还是全表扫描更高效。
  例如，当查询条件值占表中大部分数据时，可能选择全表扫描而非索引。
• 3、连接方法选择‌：
  影响嵌套循环、哈希连接或排序合并连接的选择，基于更准确的行数估计选择最优连接方式
• 4、‌分区裁剪效率
  对分区表查询时，帮助识别不需要访问的分区，减少I/O操作，提高查询速度。

直方图使用最佳实践

适用场景

• 1、数据分布严重倾斜的列‌：
  某些值出现频率远高于其他值，例如状态标志、类型代码等。
• 2、经常在WHERE条件中使用的列‌：
  查询性能对条件选择性敏感的列， 特别是等值查询(=)或IN列表查询。
• ‌3、多列相关查询‌：
  当多列组合分布与单列分布差异大时，需要扩展统计信息配合直方图使用。

配置建议

• 1、桶数选择‌：
  一般75-100个桶足够捕获数据分布特征，对极倾斜数据可增加至200-254。
• 2、收集策略‌：

-- 推荐收集方法
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCHEMA_NAME',
    tabname => 'TABLE_NAME',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE
);

• 3、维护时机‌：
  数据加载或大规模DML操作后，定期维护(如每周/每月)，应用变更导致数据分布变化时。

注意事项

• 1、避免过度使用‌：
  不应对所有列都收集直方图，唯一值多且分布均匀的列不需要直方图。
• 2、‌存储开销‌：
  直方图会占用SYSAUX表空间，大量直方图可能影响统计信息收集性能。
• 3、‌潜在风险‌：
  不准确的直方图可能导致更差的执行计划，测试环境验证后再在生产环境应用。

常见问题排查

• 1、直方图未生效‌：
  检查统计信息是否最新，确认OPTIMIZER_FEATURES_ENABLE参数设置适当，验证SQL使用了CBO而非RBO。
• 2、直方图导致性能下降‌：
  删除问题直方图，重新收集更精确的统计信息。

-- 删除直方图
EXEC DBMS_STATS.DELETE_COLUMN_STATS(
    ownname => 'SCHEMA_NAME',
    tabname => 'TABLE_NAME',
    colname => 'COLUMN_NAME',
    col_stat_type => 'HISTOGRAM'
);

• 3、监控直方图使用‌：
  通过V$SQL_PLAN_STATISTICS_ALL查看执行计划中的基数估计，比较估计行数和实际行数的差异。
• 4、USER_HISTOGRAMS视图

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

我们创建一张表，它的id字段数值严重倾斜，除了有一条记录为99之外，其他记录都全部是1，默认情况下，dbms_stats包会对所有的列做直方图分析。

• 数据准备

SQL> create table t as select 1 id,object_name from dba_objects;   -- 创建一张id为1的表
SQL> select count(*) from t;		 
 COUNT(*)
----------
90440
-- 更新其中一条记录为99
SQL> update t set id = 99 where rownum=1; 
SQL> commit;
-- id 列创建索引
SQL> create index ind_t on t(id);
-- 新表未分析直方图
SQL> select table_name,column_name,endpoint_number,endpoint_value from user_histograms where table_name='T';
no rows selected

• 对表做分析
  
• id=99 记录只有一行，所以它选择正常的扫描方式：INDEX RANGE SCAN
  
• id=1 记录与全表记录几乎一样，所以它选择正常的扫描方式：TABLE ACCESS FULL
  
• 删除ID 列的直方图信息，但保留表及索引的分析信息

SYS@paydb:1918> exec dbms_stats.delete_column_stats(user,'t','id');
PL/SQL procedure successfully completed.

• ID字段的直方图信息已经清除
  
• 查看表统计信息
  
• 查看索引统计信息
  
• 查看id=99 及 id=1 扫描方式均选择：INDEX RANGE SCAN
  
• 分析总结：
    CBO在id=1时，估算出返回的结果是904（执行计划：Rows）行，比较全部表的记录数90440行,CBO认为选择索引是合适的，但实际上id=1的记录数基本行于表的全部记录数。
    同理，在id=99时，CBO依然估算出返回值是904行，在这种情况下，CBO没有数据的具体分析情况，所以做出了错误的判断。
    一般在收集统计信息时都是一条语句搞定：exec dbms_stats.gather_table_stats(user,‘T’,cascade=>true);但Oracle对数据分析是需要消耗资源的，特别是对于一些很大的段对象，分析的时候尤其长，所以在收集信息时分会根据需要按时间段（业务低峰）进行按类型分批收集。