Oracle Histogram 基础介绍

版本 修订人 修订日期 备注 v0.1 yangze.yz 2016-10-8 Oracle Histogram 基础介绍

直方图目的

解决数据倾斜情况：假设表t(c1)有1000的数据量，NDV：10，其中num:0数据量占910，其他1-9分别为10。在没有直方图的情况下，c1 = 0-9都会估计有100行。

什么时候创建直方图

• 手动模式

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => ‘SYS’, tabname => ‘T’, method_opt => ‘FOR COLUMNS C1 SIZE 200, C2 SIZE AUTO’);

指定了c1创建bucket num为200的histogram;c2 size auto,采用自动方式.如果仅写c1，那么c2不创建histogram。

• 自动模式

（1）参数METHOD_OPT 为SIZE_AUTO，默认值。

（2）用户query查询了表。

（3）数据库检查query中条件涉及的column，并更新SYS.COL_USAGE$表作为记录。

（4）DBMS_STATS 收集统计信息，通过SYS.COL_USAGE$来决定是否需要考虑建立直方图。

Oracle如何选择直方图类型

• NDV: number of distinct values
• n: buckets num。默认254。
• p: (1 - (1/n)) * 100。

Figure:Decision Tree for Histogram Creation

名词解释

Endpoint Numbers and values

• Endpoint Number 用来唯一标识一个桶。在频率(frequency)和混合(hybrid)直方图中，Endpoint number标识从前一个桶和当前桶所有值的累积频率。在高度直方图中，是从0或1开始的连续数字，表示第几个桶。频率直方图可以看成高度为1的高度直方图，将相同Endpoint Value的bucket压缩后的结果。

• Endpoint Value 表示桶中最大的那一个值。

Popular and Nonpopular Values

Popular value影响行数估计算法。

• popular value 多个bucket有同一个Endpoint Value，那么这个值就是popular value。优化器估算popular value的cardinality时候使用如下公式：

cardinality of popular value =     (num of rows in table) *      (num of endpoints spanned by this value / total num of endpoints)

• Nonpopular value 所有非popular的数据就是Nonpopular value。优化器估算popular value的cardinality时候使用如下公式：

cardinality of nonpopular value =  (num of rows in table) * density

density综合资料和实际应该是

density =(总的buckets数- 所有popular值的buckets数PopBktCnt)               /总的buckets数               / (NDV- popular值总的个数POPVALCNT)

频率直方图(Frequency Histograms)

每个column value对应着一个bucket。

从Oracle 12c起，当采样设置为AUTO_SAMPLE_SIZE的时候，database会通过全表扫描创建频率直方图。在早期版本中，会用一个较小的采样来创建，但这会使部分小频率的值没有在采样中出现，使用density计算选择率会偏大。

Top Frequency Histograms

Top Frequency Histograms是Frequency Histograms的一个变化，忽略统计上无关紧要的nonpopular values。如果一小部分数值覆盖了大部分行，那么创建就可以针对这一小部分数值创建频率直方图。

等高直方图(Height-Balanced Histograms)

Height-Balanced Histograms是将所有的行排序等分到每一个bucket中。

在12c之前，当NDV大于bucket数目的时候就会创建Height-Balanced Histogram，这种类型的直方图对于范围查询还有至少作为endpoint出现在两个bucket中的等值估计有效。

在升级12c之后，如果是AUTO_SAMPLE_SIZE，那么就不再创建Height-Balanced Histogram。

个人理解Height-Balanced Histogram对于部分很接近popular value的估算很不准确，hybrid histogram是它的一种改进。但由于hybrid histogram需要endpoint value的frequency准确，当指定采样率的时候会不能保证做到这一点。

Hybrid Histograms

上面提到了Height-Balanced Histograms对于接近popular value的数据估计会很不准确，比如一个值作为一个bucket的endpoint，但实际其覆盖了接近两个bucket。为了解决这个问题就引入了Hybrid Histogram，没有值会跨越两个bucket，同时记录endpoint的repeat count。这样优化器可以获取大部分popular value的准确选择率。

Hybrid Histograms会先按照Height-Balanced Histogram创建，创建之后再将跨越两个bucket的值移到同一个bucket作为endpoint，并记录repeat count。

优化器计算选择率的时候，对于endpoint value，使用其repeat count来计算选择率。对于非endpoint value就使用density。