oracle cost分析,SQL优化系列--Cost赖以生存的基础-直方图

直方图是统计学上的工具，通常情况下表现为一种几何图形，这个图形是根据从实际环境所收集过来的被管理对象的质量分布情况的数据所绘制而成，通常会画成以数据类型为底边(X 轴)，数据频率作为高度(Y轴)，根据所绘制成的图形判断数据的分布和走势，从而判断产品的质量情况。表现为：

https://www.cndba.cn/arealman/article/397

之前有分析过一个COST计算的案例，里面有关行数总量(ROW CNT)的统计方法。总结一下其实就是目标统计列基于该列值域区间比来评估数据行数量占总数据的比例。该方法统计正确的前提是数据在整个值域区间(hight_value  - low_value)的分布是均衡的。但实际上是绝大多数情况数据是不会均衡分布的，该方法无法真实反映数据列值的分布情况。

https://www.cndba.cn/arealman/article/397

ORACLE后面的版本引进了数据直方图的概念，以直方图对数据质量分布情况进行描述。注意，目前的描述是基于ORACLE 10g基础上的，11g的情况则会有所不同。X轴是表示列唯一值(NDV)域，Y轴则是表示采样所的到的该唯一值出现的频率。根据列唯一值域的分布情况不同，可以分为两种类型的直方图。

https://www.cndba.cn/arealman/article/397

一. 等频直方图

当目标统计列上的唯一值总数量小于或等于254个的时候，ORACLE会优先创建等频直方图，否则创建等高直方图。

下面来做一些测试，探讨一下oracle创建等频直方图的过程

1.创建表

create table t(id number,name varchar2(200));

insert into t select ceil(level/100),'测试'||level from dual connect by level<=10000;

如此，往表里面插入了10000条记录，id列上的唯一值数量为100，分别为 1,2,3,4,5,6,7,8,9......100；

--select distinct id from t;

创建该列的索引

create index idx_t on t(id);

2.队该表进行采样收集，并对采样数据生成直方图

https://www.cndba.cn/arealman/article/397

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'RM_SHARE_V2',

tabname          => 'T',

estimate_percent => 100,

method_opt       => 'for columns id size 100',

degree           => DBMS_STATS.AUTO_DEGREE,

cascade          => TRUE);

END;

查看直方图数据

select histogram from dba_tab_col_statistics where table_name='T' and column_name='ID';

FREQUENCY  (FREQUENCY等频直方图，HEIGHT BALANCED 等高直方图)

https://www.cndba.cn/arealman/article/397

select * from user_histograms where table_name='T' and column_name='ID';

其中endpoint_value 表示X轴上的数据值分布，endpoint_number表示Y轴上对应该该X轴的值累计和，例如id=1的记录有100条，id<=2的记录为200条，可以算出id=2的数据行数为

200-100 = 100

可以看出ORACLE为ID列上的每一个唯一值都创建了一个桶，根据统计到的每个唯一值总出现的次数而会呈现出高度的不同(因为这里刚好举例的唯一值出现次数是一样的，所以这里的高度是同样的)

select * from t where id<10;

id < 10 其实就是前面 1,2，3....9 个桶的数据和，也就是900行。

对比一下oracle自己的执行计划结果

ORACLE自己估算的行数为 901行，基本吻合。

二. 等高直方图

绝大多数情况，列值唯一值的数量都会超过254个甚至远远超过，所以等频直方图很难能满足大多数的生产需求。

编头有提到之前分析COST估算的时候，利用的是值域区间比来估算目标数据行总数据行比。这种估算方式缺陷比较大，因为绝大多数情况下数据在值域内(列最大值和最小值之间的区间)的数据分布都不是均衡的，总会存在一些地方密度大，一些地方密度小的情况。

但是如果将整个值域区间分拆成多个大小相等的小区间，再来做估算，则数据则相对来说准确很多。这就是等高直方图的应用所在。这里大小相等的意思是各个区间内的数据行总数相等，而区间宽度则会因为数据分布的密度不同会有所不同。

什么叫做等高直方图？ 等高直方图就是根据列数据行总量NNV(NNV表示非空行的总数 number not null values)，指定的生成的直方图的桶数据(BKN)，唯一数据数NDV(number distinct values) ,每个唯一数据出现的次数NDV_CNT 而计算出来的直放图。计算规则如下：

1) NNV/BKN   算出直方图中每个桶的容量(其实是数据行的意思)，BKN表示直方图的桶的数量。

2) 将 NDV，和对应的NDV_CNT 按照 NDV从小到大排序，从直方图的第一个桶开始，用NDV_CNT不断地往桶里面填充，直到桶的容量填满为止，这时桶的编号endpoint_number=1，endpoint_value = 填充满时最后的NDV的值。

3)填满后，往下一个桶填充，直至所有桶都填完，这时候NDV_CNT也刚好用完。

用图描述过程：

下面我们看看ORACLE是如何生成等频直方图的：

create table t as select * from dba_objects;

create index idx_t on t(object_id);

select b.num_rows,

a.num_distinct,

a.num_nulls,

utl_raw.cast_to_number(high_value) high_value,

utl_raw.cast_to_number(low_value) low_value,

(b.num_rows - a.num_nulls) "NUM_ROWS-NUM_NULLS",

utl_raw.cast_to_number(high_value) - utl_raw.cast_to_number(low_value) "HIGH_VALUE-LOW_VALUE"

from dba_tab_col_statistics a, dba_tables b

where a.owner = b.owner

and a.table_name = b.table_name

and a.owner = 'RM_SHARE_V2'

and a.table_name = upper('T')

and a.column_name = 'OBJECT_ID';

NUM_ROWS                 61430  --总行数

NUM_DISTINCT             61412  --去重后的总行数 NDV

NUM_NULLS                 18    --空行数

HIGH_VALUE               379470 --最大值

LOW_VALUE                 2     --列最小值

NUM_ROWS-NUM_NULLS       61412  --非空值总数 NNV

HIGH_VALUE-LOW_VALUE     379468  --高度差 值区间

可以看出 NNV = 61412; NDV = 61412; 由于dba_objects的object_id列不会有重复值，因此NDV_CNT总是1;

那么我们来画一下直方图，然后再和ORACLE自己生成的直方图做一个对比，看是否一致。

我们指定直方图桶数量为254桶，那么每个桶的数据行总数为

NNV/BKN = 61412/254 = 241.79 ==> 242

每个桶的容量为242行，因此第一个桶为前242行数据，看看数据表

根据查询结果，第一桶的 endpoint_number = 1, endpoint_value = 247.

再来看看第二桶的情况：

第二桶应该是容纳 243 ~ 484 行的记录

故第二桶的endpoint_number = 2,endpoint_value = 507;

好了，现在去我们去看看ORACLE自己创建的等高直方图，看看是否与我们估算的一致：

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'RM_SHARE_V2',

tabname          => 'T',

estimate_percent => 100,

method_opt       => 'for columns object_id size 254',

degree           => DBMS_STATS.AUTO_DEGREE,

cascade          => TRUE);

END;

执行统计分析，由于列唯一值超过了254个，故默认创建等高直方图

https://www.cndba.cn/arealman/article/397

和我们计算的完全一致。

https://www.cndba.cn/arealman/article/397

原理：

ORACLE引用等高直方图统计方法，是基于以下的假设的，每个划分的区间里面的值分布是均衡的。

另外，由于每个区间的行数据量是一样，因此可以从自画图上看出，数据分布比较密的地方，区间宽度都是相对较窄的。https://www.cndba.cn/arealman/article/397

基于上面的例子做分析，dba_ojbect.object_id是前密后疏的，说图形上反映为前面的区间宽度窄，越后面就越宽。

现在我们来分析等高直方图是如何对执行计划里面的行数进行估算的：

select * from t where t.object_id < 1000;

https://www.cndba.cn/arealman/article/397

我们人工估算一下上面这条SQL的行数：

可以看到object_id=1000是分布在第4个区间里面(第4个桶)

估算的数据行数 = CEIL(NNV *(4-1)   /254   + NNV *  (1000 - 1 - 767)/((1014 - 767) * 254) ) = 953

这个公式估计不用解释都能明白，object_id=1000分布在第4个桶中，也就是说object_id<1000包含了前面123个桶所有数据和第4个桶的部分数据。

前面三个桶的数据占比为 (4-1)/254，而第4个桶，数据区间是(767,1014],目标统计区间是(767,1000),因此区间占比为(1000-1-767)/(1014-767)，前面已经对等高直方图做了假设，小区间内的数据分布是均衡的，所以数据在第4个桶中的数据占比也是(1000-1-767)/(1014-767)

和ORACLE自己计算的结果基本一致。

有关流行列唯一值的分析和计算：

什么叫流行列唯一值？ 有些情况，列唯一值的数据分布并不会那么理想的多个列值共用一个桶，如果某个列唯一值重复的次数(NDV_CNT)远远超出其他列唯一值的重复次数，以至于需要一个或者多个桶来容纳这个值的时候，这个列唯一值就叫做流行列唯一值。这是统计列上发生的数据分布不均衡的极端情况

发生这种情况的时候，oracle在画等高直方图的时候会有所区别，我们分析一下这种情况，oracle是怎么画直方图和进行数据行数估算的：

省略了，其实思路还是一样的，计算出桶内只有该流行列唯一值的桶的总数，除以桶的总数，便可以得到该值的数据量占比。当然，基于该理论，oracle统计出来的数据肯定是会比实际的偏小的，因为很有可能，除了桶内只有该流行列唯一值的桶，这些桶的前一个桶，后一个桶很有可能还会有部分该流行列唯一值的.但这些落到统计外的桶，并没有纳入到统计之内。

版权声明：本文为博主原创文章，未经博主允许不得转载。

SQL优化 直方图