楼主: newkid

[翻译]Jonathan Lewis 关于直方图的系列文章(33楼更新至第三篇)

[复制链接]
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2013-10-4 02:07 | 显示全部楼层
本帖最后由 newkid 于 2014-1-28 01:09 编辑


直方图系列文章(二)

Jonathan Lewis 发表于2013年10月1号

原文链接:
http://allthingsoracle.com/histograms-pt-2/
(经作者同意发布中文版,如有转载请注明出处)

在这个系列的第一篇中,我们讨论了为什么我们可能需要创建直方图来帮助优化器处理倾斜分布的数据。我们用了一张订单表(orders)中的一个简单的状态列(status)作为例子来展示这个规律,特别讨论了频度直方图,并且着重指出了几个直方图相关的问题。

在第二篇中,我们将会花些时间来讨论截至Oracle版本11g为止唯一的另一种直方图:等高直方图。等高直方图可能在几种数据形态下会有用,我们将会在第三篇文章中讨论。但我们将会以一个例子作为开始,在这例子中我们的数据不允许Oracle创建频度直方图,并且等高直方图可能成为一种障碍而不是便利,我们将会描述一种策略(你在12c中将不再需要)来绕过这个限制。在这个过程中我们会查看一个很简单的例子,它展示了Oracle用于产生等高直方图的算法。

一个简单的例子

这里是关于一些重要的客户数据的报表(当然,已经屏蔽处理过了),显示了一个特别重要的列的值的不均匀分布:

select
         specifier, count(*)
from
         messages
group by
         specifier
order by
         count(*) desc
;    -- 表中有 10M (一千万)行数据

SPECIFIER  COUNT(*)
BVGFJB          1,851,177
LYYVLH            719,582
MTVMIE            672,823
YETSDP            659,661
DAJYGS            504,641
...
KDCFVJ             75,328
JITCRI             74,104
DNRYKC             70,029
BEWPEQ             68,681
...
JXXXRE                  1
OHMNVU                  1
YGOBWQ                  1
UBBWQH                  1

352 rows selected.

显然,在这里我的值分布是很不均匀的,所以直方图可能就很有用。不幸的是总共有352个不同的值,这已经超出Oracle的限制(到12c之前),所以如果我创建了一个直方图,那么它就必须是一个等高直方图。

为了创建等高直方图,Oracle开始先对数据排序,然后选出第一行和每个第N行(这里N=“列中非空值的个数”除以“要求的桶(bucket)的个数”)。在我们的样本数据中,我们在排序后的数据中差不多每隔40,000行选出一行(10M/254)。(译者注:12c之前最大桶数为254, 即数据最多可被分成254组)

每个选中的数被称作端点值,每个数字都被标上编号(称作端点编号),从0至“桶的个数”。使用这个算法,一个值如果在数据中出现的频度足够大,那么它就会被选中很多次,这就允许Oracle将其识别为“频繁”值。在存储直方图选择值的时候,Oracle并不重复保存端点值——如果一个值在列表中出现许多次,它只会被存储一次,取最高的端点值标号。

为了展示涉及到的机制,以及随后的基数计算,我将采用下列(已经排序)的20个值并且建立一个五个桶的直方图:
5, 6, 6, 6, 9, 11, 11, 12, 12, 12, 12, 12, 13, 13, 13, 13, 13, 16, 17, 17

取出第一个,以及每4个取一次 (20 / 5 = 4)
5, 6, 6, 6, 9, 11, 11, 12, 12, 12, 12, 12, 13, 13, 13, 13, 13, 16, 17, 17

保存起来,从0开始标号,因为我们已经包含了列表中最小的原始值:
0    5
1    6
2    12
3    12
4    13
5    17

把重复值中早出现的去掉:
0    5
1    6
3    12
4    13
5    17

这就是Oracle为原始数据集所存储的等高直方图。从中可推断出12是个常见的值(它出现了不止一次);但是,即使我们能看出12和13的个数是一样多的,Oracle也不能推断出13是个常见的值。你可以考虑将其中的一个11改为16——如果你这么做,12就不再作为频繁值出现,而13变成了频繁值;反之,如果你把一个16变成11, 那么12和13都不会作为频繁值出现。

至于基数计算,Oracle对于频繁值采用“计算桶数”——在我们这个小小的例子中,我们选择了每个第四行,我们的“桶”的大小为4。我们知道在整个直方图中12出现了两次,所以Oracle将谓词“n1=12”的基数计算为8 (也即:2个桶*每桶4行)。对于那些非频繁值,优化器计算基数的方法一直在改变——在版本11.2.0.3中,有一种计算方法是很简单的:非频繁的行数/非频繁值的个数


在我们的例子中,计算如下:



频繁值:1个        (12)
非频繁值:7个        (5,6,9,11,13,16,17)
频繁值总行数:8        (2 个桶,每桶4行)
非频繁值总行数:12        (20 – 8)
非频繁值基数 = 12/7 = 1.714, 进位为2。

当然,计算有复杂的变种,特别是当Oracle使用了采样数据来建立直方图,优化器就必须将它的计算乘以一个系数加以放大,以反映表中的相关行数以及直方图所捕获到的行数之间的差异。

记住这个小小的例子,然后我们来考虑我客户的一千万行和352个值的数据。

精确性很重要

我们有一千万行数据——并且我们为了创建等高直方图所做的第一件事就是对数据排序,所以第一步是相当耗费资源的。如果我们对数据采样,这会减少排序的数据量——但是这个采样可能会漏掉很多值,以至于Oracle以为能够创建一个频度直方图——那样就会使得优化器对某些实际上有几千行的值做出过低的估算。

对数据排序之后,当我们每隔40,000取一行,我们的“常见”值将有多少个可能会被视为常见?我们最常见的值有1.8M行,这相当于46个桶,接下来的三个值占去了63个桶,总共才剩下155桶——既然识别一个频繁值需要两个桶,我们最多只能识别另外72个频繁值——剩下的东西将一概被处理为“平常值”。

实际上,这个频度直方图和等高直方图之间的灰色地带是最令人头疼的特性之一。频度直方图可以处理254个频繁值,但如果Oracle不得不切换到等高直方图,它最多只能识别127(即 254/2)个频繁值——如果你的数据超出这个频度限制一点点,你的精确度可能急剧下降。

稳定性是另一个问题——在我的列表中间,我报了几个数值,每个差不多有70,000行。这些会被捕获到直方图中吗?既然一个桶代表40,000行,一个有40,000行的值就可能被我们选中两次,另一方面一个具有79,999行的值却可能无法出现两次。你只需每天增加或者删除几行,这些值就会在你收集直方图的时候“随机地”出现或者消失。当我具体分析数据集时,我得出了下列结论——基于100%的取样:

有25个值每天都一定会被捕获(每个值多于80,000行)
有35个值可能这一天被捕获,下一天就消失了(40,000至80,000行)

等高直方图可能很不稳定——如果这35个值的任何一个是用户真正感兴趣的,那么它们的执行计划可能在每次收集直方图的时候剧烈地变化。取样的随机性使得事情雪上加霜,如果我们不用100%的数据来采集直方图的话。

但是详细分析数据也揭示了另一方面:
最频繁的140个值占据了数据的99%。
最频繁的210个值占据了数据的99.9%。
最频繁的250个值占据了数据的99.98%。
最后的102个值占据了10M行数据中的大约2,000行

(顺便请记住,我们无论如何最多只能够捕获81(77+3+1)个频繁值,因为最频繁的那些值占用了多数的桶。)

我们在表中有352个不同的值,但是其中的102个基本上是看不到的。如果我们能够在最频繁的250个值上创建频度直方图(必要的话把这列的最高、最低值也加入),并且告诉Oracle如何假设其他任何一个值平均只有20行,岂不是一件很美妙的事?顺便说一下,那正是12c的Top-N直方图所干的事,如果我们觉得这确实重要,只需要几个dbms_stats包的调用就可以做到。

伪造频度数据

让我们回顾一下前一篇文章所使用的数据集,我们用它来展示如何建立一个简单的频度直方图,当我们对想使用的值很了解的时候。记得我们有一个状态值的列,它的分布看起来是这样子的:
S       COUNT(*)
C        529,100
P            300
R            300
S            300
X        500,000

既然这些值是一个很小的集合,我们可以高兴地说,不管什么时候我们需要优化器来运行我们的关键查询,这个集合的数字已经是数据的一个“足够好”的模型,每当Oracle常规的统计信息收集机制在这张表上创建了新的统计信息,很容易就可以写一个过程来重新创建频度直方图。

代码依赖于包中三个关键的过程:get_column_stats(), prepare_column_stats() 和 set_column_stats(), 并且假设表的统计信息(某些方面的描述信息)已经被收集了,因为它需要将表的当前统计信息读取到本地变量中,修改变量然后把它们写回到数据字典中。

declare

     m_distcnt       number;         -- num_distinct
     m_density       number;         -- density
     m_nullcnt       number;         -- num_nulls
     m_avgclen       number;         -- avg_col_len
     srec            dbms_stats.statrec;         -- stats record
     c_array         dbms_stats.chararray;       -- varchar2 array

begin

     dbms_stats.get_column_stats(
          ownname         => user,
          tabname         => 'orders',
          colname         => 'status',
          distcnt         => m_distcnt,
          density         => m_density,
          nullcnt         => m_nullcnt,
          srec           => srec,
          avgclen         => m_avgclen
     );

     m_distcnt  := 5;                 -- 设置我自己的 num_distinct 值
     m_density  := 0.00001;           -- 用于那些“被漏掉的值”
     srec.epc   := 5;                 -- 直方图的桶数

     c_array    := dbms_stats.chararray(
                     rpad('C',15),
                     rpad('P',15),
                     rpad('R',15),
                     rpad('S',15),
                     rpad('X',15)
                 );                    -- 依次列出所有的值
     srec.bkvals     := dbms_stats.numarray(
                          529100,
                             300,
                             300,
                             300,
                          500000
                 );                    -- 对应的频度

     dbms_stats.prepare_column_values(srec, c_array);

     dbms_stats.set_column_stats(
          ownname         => user,
          tabname         => 'orders',
          colname         => 'status',
          distcnt         => m_distcnt,
          density         => m_density,
          nullcnt         => m_nullcnt,
          srec            => srec,
          avgclen         => m_avgclen
     );
end;
/

有几个值得注意的要点。首先,在前一篇文章中,我说过Oracle对那些包含了直方图中缺失的值的谓词使用的是“最不常见的频度的一半”;似乎是这样,当你使用set_column_sets()来生成统计信息时,Oracle使用density(密度)参数来计算缺失值的基数——这就是我为什么在这个代码中设置了density参数,因为我的决定是,如果你查询一个缺失的值,它的基数应该是10。(如果你检查user_tab_columns试图,自动收集和手动设置的统计信息之间的唯一区别是,当你设置统计信息时user_stats列被置为YES, 如果是自动收集,则为NO)

其次,你会注意到,在我的列表中,我把每个值用空格向右补足到15个字符。我的表将状态值(status)定义为CHAR()型的列,Oracle在将字符串转化为数值时,对CHAR()和VARCHAR2()的处理稍有不同——对于CHAR()列你必须像我那样rpad()到15个字符,对于VARCHAR2()列你不能补齐。如果你把这个弄错了,计算就会出错。

最后,我用了一个dbms_stats.chararray类型的数组;dbms_stats保重还定义了其他几个数组类型(例如numarray, datearray),你必须为你要修改的列选择合适的类型。

选择你的统计信息

如果你知道使用哪些值及其频度,你可以很容易地设立一个假的频度直方图。如果你对数据不是很了解,你可能想要从数据中推导出直方图信息。我对直方图的观点是这样,你真正需要有的直方图只有少数几个,它们通常是频度直方图,你通常需要对数据很了解,所以我从未写过一个通用的(因而也是复杂的)脚本来处理所有的选项;我有一个自己使用的框架,为每个需要它的列创建一个定制的过程。这个过程的核心基本上是一个简单的SQL语句,用于识别最大、最小值和最常出现的N个值,连同它们的频度,还有一些高层次的汇总信息,这是一个例子及其输出的样本:

select
     sample_size           sample,
     sum(ct) over()        hist_size,
     count(ct) over()      buckets,
     min_n1,
     max_n1,
     n1,
     ct
from (
     select
           sum(ct) over ()            sample_size,
           min(n1) over()  min_n1,
           max(n1) over()  max_n1,
           n1,
           ct
     from (
           select
                n1,
                count(*)   ct
           from
                t1         -- 这里可能使用SAMPLE子句
           group by
                n1
           )
     order by
          ct desc
     )
where
     rownum <= 20
or   min_n1 = n1
or   max_n1 = n1
order by
     n1
;

   SAMPLE  HIST_SIZE    BUCKETS     MIN_N1     MAX_N1         N1         CT
---------- ---------- ---------- ---------- ---------- ---------- ----------
   10000       7062         22        162        239        162          1
   10000       7062         22        162        239        190        233
   10000       7062         22        162        239        191        259
   10000       7062         22        162        239        192        268
   10000       7062         22        162        239        193        300
   10000       7062         22        162        239        194        317
   10000       7062         22        162        239        195        312
   10000       7062         22        162        239        196        394
   10000       7062         22        162        239        197        415
   10000       7062         22        162        239        198        389
   10000       7062         22        162        239        199        406
   10000       7062         22        162        239        200        777
   10000       7062         22        162        239        201        400
   10000       7062         22        162        239        202        405
   10000       7062         22        162        239        203        393
   10000       7062         22        162        239        204        358
   10000       7062         22        162        239        205        315
   10000       7062         22        162        239        206        306
   10000       7062         22        162        239        207        284
   10000       7062         22        162        239        208        276
   10000       7062         22        162        239        209        253
   10000       7062         22        162        239        239          1

22 rows selected.

这个查询最核心的部分是对我们需要的列值进行聚合,我们希望它能够将一个大数据集减少为一个小的结果集。然后我们根据频度对这个列排序,使用了几个分析函数来得到最小、最大的列值。然后我们将出现频度的前N个值对应的行及其最小、最大列值选出来。最后我们用了几个分析函数来计算我们实际选中的桶的个数,以及直方图的总数据量,将结果按列值排序返回。

sample和hist_size这两个列的差异告诉我们这是否是对整个数据集的一个“足够好”的近似值。buckets列的值告诉我们是否应该把最小和最大值加入到列表中,或者它们已经天生就落在我们的Top-N列表中,min_n1和max_n1列告诉我们这个列实际的最大、最小值。N1和CT列给了我们将会用来放到两个dbms_stat数组的详细信息(可能有少许修改)。

一旦我们有了这么一个查询,我们将会修改前一个版本的过程来去掉数组的赋值,代之以FOR游标循环:

for r in  ({query}) loop
     n_array.extend;
     srec.bkvals.extend;
     ct := ct+1;
     n_array(ct)     := r.n1;
     srec.bkvals(ct) := r.ct;
end loop;

还有几个小细节需要打磨一下,才能成为一个完整的解决方案——初始化变量,决定一个合适的密度值,决定当最小最大值不出现在Top-N中要如何处理,如果捕获到的数据量和样本的尺寸相比太小要怎么办,等等。进一步的完善留给读者作为练习。

结论

有时候你得使用一些伎俩才能让Oracle得到最好的利用,当你在处理直方图的时候,这个领域是你最有可能需要一些定制化机制来让事情理顺——但可能只是在几个特殊的情况下需要。

其中一个特殊的情况是,当你有一个列,它含有的不同的值超过了Oracle的频度直方图当前允许的限度(254),而你发现等高直方图不能够捕获你需要的足够信息,并且很可能在每次你收集统计信息的时候导致执行计划的改变。在这样的情况下,你可能决定构造一个“假”的频度直方图,它包含了表中最常见的那些值的详细信息,并且告诉优化器如何为其余的值算出一个有代表性的基数。

如果你很了解你的数据,你可以设立一个过程来使用一个固定的数据集来创建直方图,否则你可以查询数据来生成一个合适的"前N个值"的统计数据的集合。不管你选择哪种方法,你必须记住优化器将会放大你所产生的直方图的尺寸来匹配行数的值 (user_tables.num_rows – user_tab_cols.num_nulls),所以仅当频繁值代表了表中总数据量的很大的百分比(可能多达98%)你才应该使用这种方法。



使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2013-10-4 02:19 | 显示全部楼层
HelloWorld_001 发表于 2013-9-24 10:57
之前兔子发了一贴也是将这个东西的
倾斜直方图的时候用函数索引,利用索引不存null的特性。

如果你认为表数据的特征不再变化,可以把这张表的统计信息锁定,不再更新。
但是其实表数据还是在变的,哪天你锁定的信息已经过时,计划就劣化了,这是很难讲的。

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2013-10-12 03:40 | 显示全部楼层

RE: [翻译]Jonathan Lewis 关于直方图的系列文章(新增12c直方图介绍)

本帖最后由 newkid 于 2013-10-12 03:50 编辑


这是另一个专门讲12c直方图的系列,也一并翻译过来。

12c 的直方图(迷你系列之一)
Jonathan Lewis @ 7:11 pm BST Jul 14,2013
原文链接:
http://jonathanlewis.wordpress.com/2013/07/14/12c-histograms/


12c有几处改善,只要付出一点小小的努力就可能导致性能的巨大提升。其中最重要的改善之一来自直方图——它改善了收集的速度以及结果的准确性。这个变化是如此的显著,我把它选作去年OpenWorld的演讲题目。

在版本11g, Oracle给了我们一个选项来使用“近似的NDV(number of distinct values,不同值的个数)”快速而又准确地收集列的基础统计数据。在12c, Oracle将近似NDV的机制扩展到频度直方图,以及一种被称为"Top-N直方图"的新型频度直方图,同时,直方图允许的桶的最大个数从254提高到了2000——虽然缺省仍然是254, 而且大多数情况下你可能不需要增加(还记得你可能在sysaux表空间所遇到的副作用吗,因为Oracle会保留历史统计数据)。

相对来说,等高直方图的收集仍然比较昂贵——但是有一种更新的等高直方图,称作混合直方图,比起旧的等高直方图,它在相同的工作量下给了你更好的信息;多亏“Top-N”的引入,你可能发现你有些旧的(昂贵且不准确的)等高直方图被廉价的,准确的Top-N直方图所代替。

在这个迷你系列的第二篇文章,我会描述频度直方图的新机制,还有Top-N直方图的逻辑,在第三篇中我会描述混合直方图的机制,并且演示它的好处。本篇的作业是,你需要查看一下你当前的统计收集方法,想想在策略上的一个小小改变如何消减负载同时改善你的直方图的准确性。

12c 的直方图(迷你系列之二)
Jonathan Lewis @ 9:00 pm BST Jul 30,2013
原文链接:
http://jonathanlewis.wordpress.c ... 2c-histograms-pt-2/

在这个迷你系列的第二篇文章,我会描述频度直方图的新机制,还有Top-N直方图的逻辑。在第三篇中我会描述混合直方图。

你需要对NDV(number of distinct values,不同值的个数)有所了解,然后才能开始检测12c对频度直方图和最高频度直方图的实现方法——文章的结尾有个缩略的草图,如果你需要快速点拨的话。

简单频度直方图

为了允许简单频度直方图的收集——每当新产生一个散列值,记下第一个rowid;计算这个散列值产生的次数。如果到了表的末尾,所有不同的散列值不比需求的多(缺省254,最多2000个)你可以利用一个rowid的查询来查看实际的值。

留下的问题——如果两个值产生了同样的散列值会怎么样?我回答不了,除非我有两个值能够得出相同散列值;但是我假设代码会检查冲突,所以可能Oracle会为每个散列值保留一个链表,指明了原来的值(以及第一个rowid)所以在极端罕见的冲突情况下,它能够保留第二个值(及其随后的值)的一个rowid样本,并且保持独立的计数器。

注意,比起基本的近似NDV, 产生简单直方图的工作需要额外的内存,和一点额外的CPU, 但是相对于产生近似NDV所需要的表扫描而言,这个边际成本是很小的。

Top-N频度直方图
如果散列值的个数超过了要求的桶个数,Oracle的传统做法是退回到“等高直方图”; 但是在12c, Oracle可以识别出那些特殊情况,此时大部分的数据落在相对少数的桶内,剩下的小部分数据涵盖了大量的不同值。如果我们把这小部分数据称作“非频繁”数据,那么“小”就意味着非频繁数据会落在单独的一个桶内。

为了给出一个具体例子,假设你在表中有 100,000行数据,在一个列你有5,000个不同的值,这其中的95个达标了99,000行的数据;如果你请求一个100个桶的直方图,Oracle就会查看前100个最频繁的值是否覆盖了99/100 的数据;如果你要求的是200个桶的直方图,Oracle就会查看前200个最频繁的值是否覆盖了199/200 的数据。如果非频繁数据覆盖的少于一个“平常”的桶,那么Oracle就会产生一个频度直方图,它包含最低值,最高值,及其他的N-2个值即最频繁出现的值。

最低、最高值用于“超出范围”的谓词检验,如果它们是非频繁值,那么Oracle就会将边界稍微修整一下来容纳它们。(它们可在统计收集查询的标准的min/max部分找到,但是Oracle似乎将它们保存为计数为1,如果不是频繁值的话。)

如果不同值的个数小于16,384(Oracle用这个数作为近似NDV的上限),那么包含每个散列值的计数就会允许它将频度排序,这是为了决定是否足够少的散列值覆盖了足够大的百分比来产生一个Top-N直方图。

留下的问题——如果不同值的总个数大于Oracle用作极限的16,384,那么散列表将会有分裂(至少一次)。但是,Oracle仍然可以检查它持有的排序过的散列值计数,看看是否足够少的散列值覆盖了整个数据集的足够大的百分比,因为收集基础统计数据的查询含有表中的行的计数。可是,我认为这里面还有更加复杂的东西,因为我跑了一些测试,它们本来(很可能)应该会丢弃一半的频繁值——然而Oracle还是在合适的情况下为每个测试创建了一个Top-N直方图。

再说一遍,在基本的近似NDV策略下,产生直方图的时间和为了生成最小,最大和计数值所需要的表扫描所花的时间差不多。关于这种类型的直方图的很重要的一点是,在许多情况下,相比起你用早期版本的Oracle所得到的等高直方图,它的准确性和稳定性都高得多。


有趣的是,我已经说了很多年,如果你的数据有大量不同的值而你又需要稳定性的话,最好的策略是编码(使用dbms_stats.set_index_stats等)出一个频度直方图,它捕获了最频繁的252个值,最低值,最高值,和假的密度(或者说是你编造的一个最低频度值,它允许Oracle正确模拟非频繁的数据)。

警告:

虽然简单的频度直方图可以更准确,因为它们允许高达2,000个桶(尽管在大多数情况下你可能并不需要这么多),虽然Top-N频度直方图可能比等高直方图要好得多——别忘了你选择“何时”创建直方图可能是直方图收集的最重要的部分。如果一个状态列(STATUS)中“有趣”的数据在9:00am和5:00pm之间出现,到了6:00pm全部消失,那么10:00点收集的关于这列直方图就是错误数据的一个准确的映像,可能会导致整天都出现不合式的执行计划。

注解:


简而言之,一个列的近似NDV(完整的描述参见Amit Poddar的文章http://jonathanlewis.wordpress.com/2009/03/20/hotsos-2009/):
为表中的每一行数据(这里我们不采用取样),将保存的值变换为0至2^64之间的散列值。当不同的散列值的数量达到16,384就根据最低二进制位的值丢弃一半的散列值。继续,但保持只有一半的散列值,直到你再次记录了16384个不同的值,就根据最低的两个二进制位的值
丢弃一半的散列值。一直重复直到你到达表的尽头。计算你记录到的不同的散列值的个数,为你所丢弃的每个二进制位乘以二。


12c 的直方图(迷你系列之三)
Jonathan Lewis @ 8:13 pm BST Oct 9,2013
原文链接:
http://jonathanlewis.wordpress.c ... 2c-histograms-pt-3/
过了这么久,比我预想的要久得多,我终于有时间来写这篇迷你系列的第三篇,谈谈Oracle在12c中对直方图做了些什么。

在第一篇中我给出了12c中具有的三种直方图的简略介绍。
在第二篇中我比较详细地描述了频度直方图和最高频度直方图在性能和准确性方面的改善。

在这个迷你系列的第三篇,我将会描述 Oracle产生的“混合”直方图是如何实现,假如“近似NDV”机制被打开并且你将estimate_percent保留在auto_sample_size。创建一个混合直方图所需要的工作量以及产生一个就的“等高”直方图所需的工作量之间有细小的差别,但是混合直方图所捕获到的信息要比等高直方图更高一等。

开始数据集

这里有100个数的列表,其中有37个不同值,我将用来演示Oracle如何创建这两种类型的直方图。
8 12 12 13 13 13 15 16 16 17 18 18 19 19 19 20 20 20 20 20
21 22 22 22 23 23 24 24 25 26 26 26 27 27 27 27 27 27 28 28
28 28 28 28 29 29 29 29 29 29 30 30 30 31 31 31 31 31 32 32
32 33 33 33 33 33 33 33 33 34 34 34 35 35 35 35 35 35 35 36
37 38 38 38 38 38 39 39 40 41 42 42 43 43 43 44 45 46 50 59
  
你会注意到我已经将数据排序——这(实际上)是创建两种直方图的第一步,也是为什么Oracle通常选择原始数据的一个小型样本作为直方图的基础:排序是昂贵的操作。那么,设想一下,假如我要求Oracle创建一个20个桶的直方图。既然总共有37个不同值,我没有足够的桶来容纳频度直方图,在12c之前Oracle将会生成一个等高直方图。

既然我们想要20个桶,又有100行数据,Oracle会将桶的大小计算为5, 并且对已排序的样本每5行抽出一行,把它们标号为1至20; 既然在样本中的第一个值(8)不匹配第一个桶的“端点值”(13), Oracle还会把它(第一个值)选中并且将其标号为0。所以我们的选择现在看起来像这样:
0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20    (端点标号)
8 13 17 19 20 23 26 27 28 29 29 31 32 33 34 35 36 38 41 43 59    (端点值)

然而,你会注意到,29这个值在我们的列表中出现了两次——这有两个方面的意义:任何出现多于一次的值被识别为“频繁”值,并且,Oracle不会重复储存它,而只储存这个值的最高标号。为了利用直方图,Oracel对频繁值估算基数的时候“计算桶数”,而对非频繁值估算基数的时候则将频繁值“剔除出去”。

我不会在这里涉及基数计算的细节——我在这篇文章中想说明的唯一一点是关于直方图的质量。Oracle将出现6次的29识别为唯一的频繁值,但如果我们看一下原始数据,我们可以看到27和28同样也出现了6次,35出现了7次,33出现了8次。被识别为“频繁”需要极大的运气。日复一日对数据的少量修改,或者对样本的少量修改,都可能导致计划的显著变化,因为不同的值被识别为频繁值,尽管数据的变化并不明显。

混合直方图
新的12c直方图被标记为混合的,因为它包含了频度直方图的元素,同时也包含了等高直方图的“桶”的做法。我们从排序操作开始,然后计算桶个数(桶 的大小仍然是“行数”/“桶数”,但是我们允许桶的大小发生变化。如果能够动态演示这个机制就好了——我在演讲中就是这么做的——但是这个博客的格式无法做到,所以我还是用讲解的办法)。这里是拍过序的数字列表:

8 12 12 13 (13) [13] 15 16 16 17 (18) [18] 19 19 19 20 (20) 20 20 [20]
21 22 22 22  23   23  24 24 25 26  26   26  27 27 27 27  27  27 28  28
28 28 28 28  29   29  29 29 29 29  30   30  30 31 31 31  31  31 32  32
32 33 33 33  33   33  33 33 33 34  34   34  35 35 35 35  35  35 35  36
37 38 38 38  38   38  39 39 40 41  42   42  43 43 43 44  45  46 50  59

我们每五个算一次,所以第一个选中的值是13(带圆括号的那个)。但是下个值依然是13,所以我们将端点值移动一位(到带方括号的13)。接着我们又算另外的五个位置,到圆括号内的18,然后我们再次发现需要将端点往后移动一点,直到方括号内的18。然后我们又算五个位置到括号内的20,并且一直移到所有20的末端。

到目前为止我们选中了第一个桶内额外的一个13, 第二个桶内额外的一个18, 第三个桶内额外的三个20——所有我们的桶的大小是允许不同的(到目前为止,最小为5)你也能看到,当我们到达样本的末尾时,有趣的事情发生了,因为我们剩下的要分布在最后几个桶的数据已经很少。

值得注意的事情有两个:我们对每个桶内的最高值做了特殊处理,所以最好能够捕获一些和那个值相关的信息;我们的桶大小不等,所以我们必须知道那些桶的信息,我们不能够仅仅把他们标示为1号桶,2号桶,等等。这是Oracle保存的关于这个直方图的信息的前几行:
select
        endpoint_number,
        endpoint_value,
        endpoint_repeat_count
from
        user_tab_histograms
where
        table_name = 'T1'
order by
        endpoint_number
;
  
       EPN       EPV            REP
--------- --------- --------------
         1         8              1
         6        13              3
        12        18              2
        20        20              5    ***
        26        23              2
        32        26              3
        38        27              6
        44        28              6
        50        29              6
        58        31              5
        69        33              8
        79        35              7
        86        38              5
...

你会注意到,端点编号(endpoint_number)只是在样本数据里一路算下去(注意,和以前一样,我们同样也捕获了样本中的第一个值(8),以显示在第一个桶中不仅仅只有13)。Oracle还在直方图信息中加入了endpoint_repeat_count。结合这两个信息,我们可以查看输出的第4行,把它和第3行作比较,并且说:
桶的大小是8  (20-12)
桶里的最高值是20(端点号EPN和端点值EPV恰好相同,这只是巧合)
桶里面有5个20

采用这种策略,基本上它所消耗的资源和等高直方图策略是在同一个级别的,主要是用于对样本的选择和排序——我们对样本数据获得的信息要多得多。特别注意在我的输出里有多少个“频繁”值:27, 28, 29, 33, 和 35全部出现,而且都带有多于一个桶的数据(即REP大于5);我们甚至捕获到20和38,它们正好带有一个桶的数据。产生更多的合理的基数估算的能力被大大改善了。

这还没完,因为我们有可变的桶的大小,还知道了每个桶中最高值的行数,我们同样可以为那些“非频繁”值的分布做出更好的估算。再说一次我不会涉及到优化器的算法细节,但是我希望你能认识到混合直方图所带来的信息的增长。

SQL片段

当然,你可以在收集统计信息的时候打开SQL跟踪,来看看代码产生了什么样的SQL。我这么做只是为了证明我说的关于等高直方图和混合直方图的基本开销非常接近。这是用到的SQL 的几个样本,第一个是等高直方图的:

select  
        min(minbkt),maxbkt,
        substrb(dump(min(val),16,0,32),1,120) minval,
        substrb(dump(max(val),16,0,32),1,120) maxval,
        sum(rep) sumrep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv,  
        sum(case when rep=1 then 1 else 0 end) unqrep  
from    (
        select  
            val, min(bkt) minbkt, max(bkt) maxbkt,  
            count(val) rep, count(val)*count(val) repsq  
        from    (
            select /*+ lots of hints  */
                "LN100" val, ntile(200) over (order by "LN100") bkt   
            from    sys.ora_temp_1_ds_616 t  
            where   "LN100" is not null
            )  
        group by val
        )  
group by maxbkt  
order by maxbkt
  
  
select
        substrb(dump(val,16,0,64),1,20) ep, freq, cdn, ndv,
        (sum(pop) over()) popcnt, (sum(pop * freq) over()) popfreq,
        substrb(dump(max(val) over(),16,0,64),1,20) maxval,
        substrb(dump(min(val) over(),16,0,64),1,20) minval
from    (
        select
              val, freq, (sum(freq) over()) cdn, (count(*) over()) ndv,
              (case when freq > ((sum(freq) over())/15)  then 1  else 0 end) pop
        from  (
              select  /*+ lots of hints */
                      "VALUE"  val, count("VALUE") freq
              from
                      "TEST_USER"."T1" t
              where
                      "VALUE" is not null
              group by
                      "VALUE"
              )
        )
order by val
/

正如你所看到的,代码差别很大,但是你应该能认识到,两种负载很大程度上都是围绕着对样本数据的排序和聚合,只是方式不同。(顺便说一下,这些例子SQL并不是来自文章中显示的数据集,第一个例子来自一个案例,其中dbms_stats过程在做一些复杂操作之前被调用,它将原始表的数据样本拷贝到一个全局临时表)


使用道具 举报

回复
求职 : 数据库开发
认证徽章
论坛徽章:
28
ITPUB学员
日期:2009-10-14 18:49:45至尊黑钻
日期:2015-12-31 11:11:56数据库板块每日发贴之星
日期:2009-10-22 01:01:02优秀写手
日期:2014-04-30 06:00:17ITPUB8周年纪念徽章
日期:2009-10-09 21:30:10马上有车
日期:2014-10-09 10:14:53马上有钱
日期:2014-02-18 16:43:09路虎
日期:2013-10-15 15:38:59林肯
日期:2013-09-12 15:57:33ITPUB 11周年纪念徽章
日期:2012-10-09 18:11:48
发表于 2013-10-12 11:10 | 显示全部楼层
马克。。。

使用道具 举报

回复
论坛徽章:
27
ITPUB官方微博粉丝徽章
日期:2011-08-17 10:35:36托尼托尼·乔巴
日期:2017-10-25 16:45:57秀才
日期:2017-04-05 13:18:06秀才
日期:2017-03-02 10:35:322016猴年福章
日期:2016-02-23 09:58:342016猴年福章
日期:2016-02-18 09:31:302015年新春福章
日期:2015-03-06 11:57:312014年新春福章
日期:2014-02-18 16:42:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:07:31
发表于 2013-10-14 11:15 | 显示全部楼层
马克~~看了一遍没看懂,有空再看几遍

使用道具 举报

回复
招聘 : Java研发
论坛徽章:
13
2013年新春福章
日期:2013-02-25 14:51:24喜羊羊
日期:2015-03-04 14:52:46itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:08优秀写手
日期:2013-12-25 06:00:13奥迪
日期:2013-10-29 10:49:49马自达
日期:2013-08-29 11:48:21宝马
日期:2013-08-23 16:32:50
发表于 2013-10-15 18:19 | 显示全部楼层
俺也马克下。。

使用道具 举报

回复
论坛徽章:
5
2013年新春福章
日期:2013-02-25 14:51:24兰博基尼
日期:2013-10-11 16:43:30优秀写手
日期:2013-12-18 09:29:132014年新春福章
日期:2014-02-18 16:48:49马上加薪
日期:2014-02-18 16:48:49
发表于 2013-10-16 00:02 | 显示全部楼层
对列倾斜的情况下,要创建函数索引或者虚拟列,不是很明白,这样做有什么好处呢?为什么呢

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2013-10-16 03:02 | 显示全部楼层
kevy_small 发表于 2013-10-16 00:02
对列倾斜的情况下,要创建函数索引或者虚拟列,不是很明白,这样做有什么好处呢?为什么呢

好处是索引很小,而且CBO知道什么时候用索引,什么时候不用。

使用道具 举报

回复
论坛徽章:
5
2013年新春福章
日期:2013-02-25 14:51:24兰博基尼
日期:2013-10-11 16:43:30优秀写手
日期:2013-12-18 09:29:132014年新春福章
日期:2014-02-18 16:48:49马上加薪
日期:2014-02-18 16:48:49
发表于 2013-10-16 09:43 | 显示全部楼层
newkid 发表于 2013-10-16 03:02
好处是索引很小,而且CBO知道什么时候用索引,什么时候不用。

谢谢版主,创建函数索引的我明白了,但是为什么创建虚拟列也有用呢?oracle会自动用虚拟列吗

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2013-10-16 10:10 | 显示全部楼层
单有虚拟列还不够,还必须在这个虚拟列上建索引,其实效果等同于函数索引:
create index ord_is on orders(interesting_status);

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表