弄明白 12c优化器之直方图

张勇,北京海天起点公司数据库管理员,Oracle一体机用户组成员,获有OCP证书,具有多年金融行业oracle, mysql数据库日常维护,故障排除,性能优化、数据迁移、容灾备份等方面的运营维护经验;具有丰富的数据库设计和架构搭建经验,擅长数据统计和分析,熟悉NoSql产品;

直方图的定义和作用

在介绍oracle直方图的特点之前我们先来了解来直方图的定义和作用;

直方图是一种统计报告图,由一系列高度不等的纵向条纹或线段表示数据的分布情况。在Oracle数据库中,直方图是一种统计信息用来告诉优化器,数据在一个列中如何分布,它影响着优化器的行为,决定着优化器对数据访问的方式以及表关联顺序等,很多时候我们会在条件谓词上建立索引,却发现数据库没有选择索引仍然使用全表扫描的方式访问数据,这种判定结果就是直方图起的作用。

类似在一个1000名学生的年级科目考试中,用直方图来表示学生成绩的分布情况,可以发现绝大多数学生的成绩都集中在70~90分之间,假设其中80分占比最大,当从数据库中检索得分为80分成绩的学生时,数据库将选择全表扫描的方式,因为这是一个非常高频的分值,对于其它低频分值,像60分,则倾向于使用索引查找。

直方图的类别和默认行为

Oracle 12c之前,只有两种类型的直方图:频率和等高直方图,现在多了两种额外的直方图:顶级频率直方图和混合直方图。

在缺省情况下,优化器假定在一个列中,数据行是跨越不同的值均匀分布的,在带有等值谓词查询中,基数的计算方法是将总行数除以等值谓词所用到的列中不同值的个数,但当数据倾斜时,这种计算方法可能发生严重偏差,而直方图的存在改变了优化器用来确定基数估算的公式,使得它生成更精确的估算值;也就是说直方图是用于在数据倾斜情况下,为优化器提供更精准的信息。

如果只使用默认收集方法对表收集统计信息而不查询表,数据库将不会对表列生成直方图,这种情况下也没必要生成,以免浪费资源;要使数据库自动创建直方图,必须运行一个或多个查询以填充SYS.COL_USAGE$中的列使用信息。

创建不同类别直方图的规则

Oracle数据库使用多个条件来确定要创建的直方图类别:频率,顶频,等高或混合直方图。

判断条件涉及以下变量:

NDV:一个列中不同值的个数;如果一个性别列中仅包含男和女,那NDV就为2

n直方图中桶数量,默认为254(桶可理解为用于存放分类的容器)

pp = (1–(1/n))* 100;如果 n = 254, p 算出来就是 99.6。(n<=254p最大99.6)。

ESTIMATE_PERCENTDBMS_STATS统计信息收集程序中定义的参数变量,默认为AUTO_SAMPLE_SIZE

下图显示了直方图创建的决策树:

    1. NDV <= n,创建频率直方图;
    2. 条件1不满足,若ESTIMATE_PERCENT非默认值(即手动指定采样比例),创建等高直方图(从这里看出,12c中不太推荐等高直方图)
    3. 条件2不满足,前n个高频值所占行比例 < p,创建混合直方图。
    4. 条件3不满足,创建顶频直方图。

直方图基数评估的算法

对于直方图,基数评估算法取决于几个方面:端点号和端点值,列值高频属性等:

端点号(Endpoint Number) 就是桶号,在频率直方图和混合直方图中,端点号是当前桶和前面所有桶的累计值。在等高直方图中,桶号是以顺序编号的,以01开始;无论什么情况,端点号都为桶号。
    • 端点值(Endpoint Value):是指一个桶所包含NDV值中的最大值(一个桶通可包含多个NDV)
    • 桶压缩:当多个连续的桶都包含单个相同的值时,为减少桶的总数,优化器会将多个桶压缩到最后一个桶中;这种被压缩保存的值称为高频值。 (单个桶存储行数

      总行数 / 总桶数)
    • 高频值(Popular Values):高频值为跨多个桶的存在的端点值,同常都被压缩了最后一个桶中。在频率直方图中用当前桶号减去前一桶号,大于1就是高频值(即桶发生跳号,桶号不连续);在混合直方图中因为已经存储了端点值的重复次数,大于1即为高频值。优化器使用以下公式计算高频值的基数估计值:高频值基数 = 表行数 * (值跨越的端点号 / 总端点号)
    • 非高频值(Nonpopular Values):不是高频值的就称为非高频值。

      优化器使用以下公式计算非高频值的基数估计值:非高频值基数 = 表行数 * 密度。

      优化器使用内部算法计算密度,密度用一个介于01之间的十进制数表示;值接近1表示优化程序期望在其谓词列表中引用此列的查询返回更多行,值接近0表示期望返回更少行。

各类别直方图介绍

无直方图情况

为方便演示我从DBA_OBJECTS中挑选了20个对象共57,554行数据创建了表MYTAB,同时在OBJECT_TYPE上建立索引,其数据分布见下图1

1

可以看出OBJECT_TYPE列数据分布严重倾斜,JAVA CLASSSYNONYM等值占比非常大,而其它占比又非常小;而此时表上没有任何直方图信息,见下图2

2

在缺省没有直方图情况下,优化器假定在一个列中数据行会跨越不同的值均匀分布;

我们查询OBJECT_TYPEJAVA CLASS记录,优化器错误的认为只有2878(约等于57,554 / 20),而实际上表中符合要求的记录为38,201行,出现了严重偏差。优化器发生了误判的同时也就使用了非最优访问方式,此处最优的访问方式应该为全表扫描而非索引查找,见下图3

3

下面为OBJECT_TYPE生成直方图,见图4:

4

因为NDV20小于默认值254,优化器生成了频率直方图;

现在我们再次执行同样的查询,结果见下图5:

5

这回优化器实现了精准评估,评估行数为38,201,与表中实际记录数完全一致;并且访问方式也变成了全表扫描;这些信息都是直方图告诉优化器的,这就是直方图的作用。

频率直方图

频率直方图创建的条件就是桶的数量大等于列中不同值的个数,这也是数据库首先考虑的直方图类型;因为桶的数据量足够多,列中每个不同值就都能够拥有自己的桶,可使得化器实现准确评估,上面无直方图例子中数据库创建的就是频率直方图(见图4)

下面我们观察频率直方图中列的统计信息,见下图6

6

其中桶号(ENDPOINT_NUMBERY) 列表示为当前值行数的累计值,比如JAVA CLASS,其桶号为38249,减去上一桶号48,得到38201就是当前值对应的表中的记录数;其它值对应的记录数也可以通过此方法进行计算,图中第三列(DIFF_ENDPOINT_NUMBERY) 已经将结果显示了出来。

优化器对于至少发生跳号的桶号(即当前桶号与上一桶号不连续) 才会被考虑为高频值,其基数计算公式为:高频值基数 = 表行数 * (值跨越的端点号 / 总端点号) [ 如:JAVA CLASScardinality (C) = 57554 * ((38249 – 48) / 57554) ]

对于非高频值,其基数计算公式基于密度。

顶频直方图

在实际项目中,列中不同值个数大多超过了254(桶个数最多为254),此时频率直方图就没法用了,因为不满足其创建规则;但是如果列上的数据分布又严重倾斜,那怎么办呢?(比如,Top N的值的行数占比超过了99.6%或更多)

12c中,这种情况下数据库会选择创建顶级频率直方图,顶频直方图是通过忽略统计上无关紧要的非高频值而差异化形成的频率直方图。当一个列中不同值个数超过请求的桶数时,并且小部分不同数值却占据着绝大多数的行 (即数据严重倾斜,绝大部分行值都相同,只有极小部分不同);这时传统的等高直方图并不能将最频繁的值捕获为多个桶的端点值,因此有些频繁值就会被当作非频繁值处理,从而导致不理想的执行计划被选中。在这种情况下,为了创建一个质量更佳的直方图,更好的方法是为构成了表中绝大部分行的主体(即那些极为频繁的值) 创建一个频率直方图,并且忽略那些非频繁值。

还是MYTAB的示例,OBJECT_TYPE列上只有20个不同值,为了创建顶频直方图,我们手工指定分配18个桶,此时Top 18=57,552/57,554=99.99%,而p = (1–(1/18))* 100=94.4%;因Top n > p ,所以顶频直方图创建,见图7

7

我们观察下频率直方图中列的统计信息,见图8

8

相对频率直方图统计信息(图6),在顶频直方图中RULE和DATABASE LINK这个两个无关紧要的非高频值就被忽略了,剩余TOP 18个值的统计信息。顶频直方图与标准频率直方图一样,端点号(ENDPOINT_NUMBER) 表示为值的累积频率,其与上一端点号的差值就代表着该值在表中的行数。

等高直方图

等高直方图在12c之前就存在,现在看来Oracle不再主推此直方图了,通过混合直方图和顶频直方图来替换。当然还是可通过手动将DBMS_STATS统计信息收集程序中ESTIMATE_PERCENT参数指定为非默认值AUTO_SAMPLE_SIZE,来创建一个等高直方图,见下图9。

9

我们观察下等高直方图中列的统计信息,见图10

10

优化器必需将57554行数据平均分配到18个桶中,因此平均每个桶大概包含3197行数据;但结果却只显示了5条记录,这是因为优化器会将端点值相同的桶进行压缩。例子中,JAVA CLASS就是将桶1~11压缩成一个桶,所有行被放到了桶11中。同时SYNONYMVIEW也都被进行了压缩;优化器认为这些值为高频值,而没压缩的为非高频值。

混合直方图

    相较于顶频直方图,混合直方图适合于列中数据分布不那么严重倾斜的情况,它结合了等高直方图和频率直方图的特点,这种”两全其美”方法使优化器能够实现在某些情况下获得更好的选择性估算。

    在等高直方图中,只有在两个或两个以上的桶中作为端点出现的值才会被认为是频繁值,这种判定规则有时候会导致对近似频繁值产生不准确的估算:例如,只在一个桶作为端点值出现的值,而实际上它却近乎占用了两个桶,这种应该算作高频值的情况,等高直方图无法进行区分误当作非高频值处理了。

    为了解决这个问题,在混合直方图中对于跨桶分布的端点值,通过将其重复值记录到新的ENDPOINT_REPEAT_COUNT字段上,所有端点值将不存在跨桶的情况,同时优化器对近似高频值也能获得准确的估算。

    MYTAB的示例,为创建混合直方图,现将表中原本占比最大的对象JAVA CLASS进行拆分,将其数据分给其它对象,拆分结果如下图11

11

因数据倾斜不如顶频直方图那么严重,出现Top n < p的情况,所以数据库创建混合直方图,查询直方图类型,见下图12:

12

观察下等混合方图中列的统计信息,见图13

13

ENDPOINT_REPEAT_COUNT最大值为SYNONYM,而排名第二的就是SCHEDULE了,而实际占比较大的VIEW却未出来在端点值中;查看执行计划,见下图14



14

分析图中可知,对象SYNONYM和VIEW实际行数差不多,但优化器却只能对端点值(SYNONYM) 进行较准确的评估,对于非端点值(VIEW) 其评估结果出现了重大偏差(评估行数2406,实际11022),由此可见混合直方图并非官方手册中描述的那么”两全其美”

直方图总结

直方图是用在列数据倾斜情况下,为优化器提供更精准的统计信息,以帮其做出正确的评估。如果数据本就分布均匀(如:主键/唯一列),那就没必要建立直方图了,因为默认优化器就认为数据是均匀分布的,所以我们甚至都感觉不到他的存在,但在实际优化分析中我们需要关注他,因为我们知道哪些数据会是倾斜的,会将需要直方图。不同类别的直方图是为适应不同数据倾斜的场景,由数据库自行判断创建;从12c中提供的直方图类别来看,频率直方图和顶频直方图都能对数据进行精确评估,等高直方图有着自己的弱点,而混合直方图也并非手册中描述的完美。

其实数据评估的精准度,关键在于提供的桶数量够不够 (也就是否有足够的分类容器),当桶足够时,列中的每个不同值都可以拥有自己的桶,而不够时,数据库就只能将高频值和非高频值放一起,自然也就不那么准确了。在12c中,桶的数量上限由之前的254扩大到了2048个见下图(15),这提高了频率直方图应用的可能性。

15

未经允许不得转载:Oracle一体机用户组 » 弄明白 12c优化器之直方图

相关推荐