HelloDBA [English]
搜索Internet 搜索 HelloDBABA
首页
技术文档
产品及下载
订阅
  • 至 Google
  • 至 Yahoo
  • 至 zhuxia
  • 至 xianguo
  • 至 QQ email
  • 至 YouDao
关于
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

Oracle 12c Hybrid Histogram

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2013-08-27 04:18:04

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

在 Oracle 12c当中，优化器的一个新特性就是提供了新类型的柱状图数据，Top－N频率柱状图和混合柱状图。优化器利用它们可以更加高效、精确地计算执行计划代价，选择最优计划。这里我将探究一下混合频率柱状图在什么情况下获得、以及它如何影响优化器的选择率的计算。 

<<<<<<<<<<<<<<<<<<<<<<<<<<<<引用自Oracle 12c 在线文档>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

混合柱状图结合了高平衡柱状图和频率柱状图两者的特性。“取双方之所长”的方法使得优化器在某些情况下获得最可靠的选择率估算值。

<<<<<<<<<<<<<<<<<<<<<<<<<<<<引用自Oracle 12c 在线文档>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

高平衡柱状图的问题

简单的说，混合柱状图的引入是为了解决高平衡柱状图的问题。

首先我们回顾一下高平衡柱状图的选择率计算：

  * 如果是非“流行”值，

      selectivity=NewDensity

                 =(非流行数值数据数)/(非流行数值数)/(非空数值数据数)

                 =((ssize-nv)-(ssize-nv)*PopBktCnt/BktCnt)/(NDV-PopValCnt)/(ssize-nv)

                 =((ssize-nv)(1-*PopBktCnt/BktCnt))/(NDV-PopValCnt)/(ssize-nv)

                 =((BktCnt-PopBktCnt)/BktCnt)/(NDV-PopValCnt);

  * 如果是“流行”数据但不是最大值。

      selectivity=PopBktNum/BktCnt;

  * 如果是“流行”数据且是最大值。

      selectivity=(PopBktNum-0.5)/BktCnt;

 

而产生高平衡柱状图的过程也不复杂：

  * 分组大小限制为唯一值的分组平均记录数；将数值按顺序分别放入分组当中。分别记录下当前的分组累计大小和数值数据累计数量；

  * 如果当前分组大小足够容纳当前数值数据，则该数值被分入最后一个分组，并继续比较下一数值；

  * 如果当前分组大小不足以容纳当前数值数据，则将该数值分配至下一分组；并且，如果此时的分组累计大小和数值数据累计数量之间的差值大于一个分组大小，则需要分配多个分组，且该值为“流行”值；

  * “流行”值之后的值则会被分入新的分组；

 

按照高平衡柱状图的选择率计算方法，“流行”值和非“流行”值之间的差别会比较大。而由其产生过程来看，一个“流行”值可能仅有稍大于一个分组大小数据量，但因为其之间的分组仅剩余少量空间导致其被认为是一个“流行”值；而一个非“流行”值可能含有接近于两个分组大小数据量，但因为其正好被分配给空的分组，使得其不被认为是“流行”数值。这两种数值计算得到的选择率将与它们实际的选择率相差甚远。

我们看一个示例，

SQL代码
  1. HelloDBA.COM> truncate table oe.t_ntop;  
  2.   
  3. Table truncated.  
  4.   
  5. HelloDBA.COM> begin  
  6.   2    for r in (select level lv from dual connect by level<=30) loop  
  7.   3      insert into oe.t_ntop select r.lv, level from dual connect by level<=r.lv*10;  
  8.   4    end loop;  
  9.   5    commit;  
  10.   6  end;  
  11.   7  /  
  12.   
  13. PL/SQL procedure successfully completed.  
  14.   
  15. HelloDBA.COM>compute sum of cnt on dummy;  
  16. HelloDBA.COM>break on dummy;  
  17. HelloDBA.COM>select null dummy, a, count(a) cnt from oe.t_ntop group by a order by a;  
  18.   
  19. D          A        CNT  
  20. - ---------- ----------  
  21.            1         10  
  22.            2         20  
  23.            3         30  
  24. ... ...  
  25.           26        260  
  26.           27        270  
  27.           28        280  
  28.           29        290  
  29.           30        300  
  30. *            ----------  
  31. s                  4650  
  32.   
  33. 30 rows selected.  
  34.   
  35. HelloDBA.COM> exec dbms_stats.gather_table_stats(ownname=>'OE',tabname=>'T_NTOP',method_opt =>'for columns A size 25',estimate_percent => 100);  
  36.   
  37. PL/SQL procedure successfully completed.  
  38.   
  39. HelloDBA.COM>select endpoint_value, endpoint_number, nvl(endpoint_number - lag(endpoint_number,1) over (order by endpoint_value),1) num_in_bucket, endpoint_repeat_count from dba_histograms where owner='OE' and table_name = 'T_NTOP' and column_name = 'A';  
  40.   
  41. ENDPOINT_VALUE ENDPOINT_NUMBER NUM_IN_BUCKET ENDPOINT_REPEAT_COUNT  
  42. -------------- --------------- ------------- ---------------------  
  43.              1               0             1                     0  
  44.              6               1             1                     0  
  45.              9               2             1                     0  
  46.             11               3             1                     0  
  47. ... ...  
  48.             26              18             1                     0  
  49.             27              20             2                     0  
  50.             28              21             1                     0  
  51.             29              23             2                     0  
  52.             30              25             2                     0  
  53.   
  54. 22 rows selected.  

基于收集到的统计数据，我们可以计算出NewDensity：

SQL代码
  1. HelloDBA.COM>select ((BktCnt-PopBktCnt)/BktCnt)/(NDV-PopValCnt) NewDensity  
  2.   2  from (select sample_size ssize, num_nulls nv, num_distinct ndv, num_buckets BktCnt, density OldDensity  
  3.   3        from dba_tab_col_statistics  
  4.   4        where owner='OE' and table_name = 'T_NTOP' and column_name = 'A'),  
  5.   5        (select count(num_in_bucket) PopValCnt, sum(num_in_bucket) PopBktCnt  
  6.   6         from (select endpoint_value, endpoint_number, endpoint_number - lag(endpoint_number,1) over (order by endpoint_value) num_in_bucket  
  7.   7               from dba_histograms  
  8.   8               where owner='OE' and table_name = 'T_NTOP' and column_name = 'A')  
  9.   9         where num_in_bucket>1);  
  10.   
  11. NEWDENSITY  
  12. ----------  
  13. .026153846  

从柱状图的结果可以看出，27是一个“流行”数值，28则为非“流行”数值。基于之前的计算原则，我们可以计算出由它们作为谓词条件过滤后的数据量。

SQL代码
  1. HelloDBA.COM>select 4650*2/25 CAL_ROWS_OF_27, 4650*0.026153846 CAL_ROWS_OF_28 from dual;  
  2.   
  3. CAL_ROWS_OF_27 CAL_ROWS_OF_28  
  4. -------------- --------------  
  5.            372     121.615384  

再看优化器的估算结果：

SQL代码
  1. HelloDBA.COM>set autot trace exp  
  2. HelloDBA.COM>select * from oe.t_ntop where a=27;  
  3.   
  4. Execution Plan  
  5. ----------------------------------------------------------  
  6. Plan hash value: 1181773393  
  7.   
  8. ----------------------------------------------------------------------------  
  9. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  10. ----------------------------------------------------------------------------  
  11. |   0 | SELECT STATEMENT  |        |   372 |  2232 |     5   (0)| 00:00:01 |  
  12. |*  1 |  TABLE ACCESS FULL| T_NTOP |   372 |  2232 |     5   (0)| 00:00:01 |  
  13. ----------------------------------------------------------------------------  
  14.   
  15. Predicate Information (identified by operation id):  
  16. ---------------------------------------------------  
  17.   
  18.    1 - filter("A"=27)  
  19.   
  20. HelloDBA.COM>select * from oe.t_ntop where a=28;  
  21.   
  22. Execution Plan  
  23. ----------------------------------------------------------  
  24. Plan hash value: 1181773393  
  25.   
  26. ----------------------------------------------------------------------------  
  27. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  28. ----------------------------------------------------------------------------  
  29. |   0 | SELECT STATEMENT  |        |   122 |   732 |     5   (0)| 00:00:01 |  
  30. |*  1 |  TABLE ACCESS FULL| T_NTOP |   122 |   732 |     5   (0)| 00:00:01 |  
  31. ----------------------------------------------------------------------------  
  32.   
  33. Predicate Information (identified by operation id):  
  34. ---------------------------------------------------  
  35.   
  36.    1 - filter("A"=28)  

优化器的估算结果和我们的计算结果相同。但是，这与他们的实际数量出入较大。尤其是，实际上27的数据量是小于28的数据量的，但计算结果远远大于28的数据量。这样问题可能会导致优化器选择到性能不好的执行计划。

混合柱状图如何解决这个问题

混合柱状图不再使用结束点数来指示其是否为“流行”数据。而是增加额外字段（endpoint_repeat_count）存储当前分组中结束值的数据量。因此，结束点值得选择率计算的精确度就会大大增加。而在混合柱状图当中，如果一个值得数据量大于所有数据分组平均大小（非空值数据量/分组数）的话，就会被认为是“流行数据”。

混合柱状图的选择率计算为，

  * 如果数值为“流行”数值（“流行”数值一定是结束点值），其选择率为endpoint_repeat_count/(非空值数据量)

    selectivity = endpoint_repeat_count/(not null value number)

  * 如果数值为非“流行”数值且不是结束点值，其选择率为有非“流行”数值数据量和分组数计算得到的NewDensity。NewDensity的计算公式看起来和高平衡的计算公式相同，但是其结束点数代表的含义并不同

    selectivity = NewDensity = ((BktCnt-PopBktCnt)/BktCnt)/(NDV-PopValCnt)

    所有的*Cnt变量实际上都为特定数值数据量，而非他们的数值数或者分组数；

  * 如果数值为非“流行”数值且是结束点值，其选择率为NewDensity和endpoint_repeat_count/(非空值数据量)之间的最大值

    selectivity = Greatest(NewDensity, endpoint_repeat_count/(not null value number))

 

再看一个示例，

SQL代码
  1. HelloDBA.COM>exec dbms_stats.gather_table_stats(ownname=>'OE',tabname=>'T_NTOP',method_opt =>'for columns A size 25');  
  2.   
  3. PL/SQL procedure successfully completed.  
  4.   
  5. HelloDBA.COM>select endpoint_value, endpoint_number, nvl(endpoint_number - lag(endpoint_number,1) over (order by endpoint_value), endpoint_number) bucket_size, endpoint_repeat_count from dba_histograms where owner='OE' and table_name = 'T_NTOP' and column_name = 'A';  
  6.   
  7. ENDPOINT_VALUE ENDPOINT_NUMBER BUCKET_SIZE ENDPOINT_REPEAT_COUNT  
  8. -------------- --------------- ----------- ---------------------  
  9.              1              10          10                    10  
  10.              6             210         200                    60  
  11.              7             280          70                    70  
  12. ... ...  
  13.             28            4060         280                   280  
  14.             30            4650         590                   300  
  15.   
  16. 25 rows selected.  
  17.   
  18. HelloDBA.COM>select ((BktCnt-PopBktCnt)/BktCnt)/(NDV-PopValCnt) NewDensity, pop_threshold  
  19.   2  from (select count(1) PopValCnt, sum(endpoint_repeat_count) PopBktCnt, ndv, BktCnt, pop_threshold  
  20.   3          from (select (sample_size - num_nulls) BktCnt, num_distinct ndv, num_buckets, density OldDensity, (sample_size-num_nulls)/num_buckets pop_threshold  
  21.   4                from dba_tab_col_statistics  
  22.   5                where owner='OE' and table_name = 'T_NTOP' and column_name = 'A'),  
  23.   6               dba_histograms  
  24.   7         where owner='OE' and table_name = 'T_NTOP' and column_name = 'A'  
  25.   8         and endpoint_repeat_count>pop_threshold  
  26.   9         group by ndv, BktCnt, pop_threshold);  
  27.   
  28. NEWDENSITY POP_THRESHOLD  
  29. ---------- -------------  
  30. .022637238           186  

我们选择3个有代表性数值来检查他们的选择率：

SQL代码
  1. HelloDBA.COM>set autot trace exp  
  2. HelloDBA.COM>select * from oe.t_ntop where a=29;  
  3.   
  4. Execution Plan  
  5. ----------------------------------------------------------  
  6. Plan hash value: 1181773393  
  7.   
  8. ----------------------------------------------------------------------------  
  9. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  10. ----------------------------------------------------------------------------  
  11. |   0 | SELECT STATEMENT  |        |   105 |   630 |     5   (0)| 00:00:01 |  
  12. |*  1 |  TABLE ACCESS FULL| T_NTOP |   105 |   630 |     5   (0)| 00:00:01 |  
  13. ----------------------------------------------------------------------------  
  14.   
  15. Predicate Information (identified by operation id):  
  16. ---------------------------------------------------  
  17.   
  18.    1 - filter("A"=29)  

29不是一个结束点值，因此它不被认为是“流行”值（尽管其数据量大于分组平均值）——这种情况只会出现在最后一个分组当中，可以说是混合柱状图的一个缺陷。因此其选择率为NewDensity，过滤后的数据量为trunc(4650*.022637238)=105;

SQL代码
  1. HelloDBA.COM>select * from oe.t_ntop where a=30;  
  2.   
  3. Execution Plan  
  4. ----------------------------------------------------------  
  5. Plan hash value: 1181773393  
  6.   
  7. ----------------------------------------------------------------------------  
  8. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  9. ----------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT  |        |   300 |  1800 |     5   (0)| 00:00:01 |  
  11. |*  1 |  TABLE ACCESS FULL| T_NTOP |   300 |  1800 |     5   (0)| 00:00:01 |  
  12. ----------------------------------------------------------------------------  
  13.   
  14. Predicate Information (identified by operation id):  
  15. ---------------------------------------------------  
  16.   
  17.    1 - filter("A"=30)  

30是一个“流行”值，它的选择率为endpoint_repeat_count/(not null value number)，过滤后的数据量为 trunc(4650*300/4650)=300;

SQL代码
  1. HelloDBA.COM>select * from oe.t_ntop where a=11;  
  2.   
  3. Execution Plan  
  4. ----------------------------------------------------------  
  5. Plan hash value: 1181773393  
  6.   
  7. ----------------------------------------------------------------------------  
  8. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  9. ----------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT  |        |   110 |   660 |     5   (0)| 00:00:01 |  
  11. |*  1 |  TABLE ACCESS FULL| T_NTOP |   110 |   660 |     5   (0)| 00:00:01 |  
  12. ----------------------------------------------------------------------------  
  13.   
  14. Predicate Information (identified by operation id):  
  15. ---------------------------------------------------  
  16.   
  17.    1 - filter("A"=11)  

11是一个非“流行”值同时也是一个结束点数值。它的选择率为NewDensity和endpoint_repeat_count/(not null value number)两者之间的最大值，由其过滤数据记录数为trunc(4650*greatest(0.022637238,110/4650))=110;

由上面的例子可以看出，由混合柱状图计算得到选择率在大多数情况下会比由高平衡柱状图计算得到的选择率更加精确。

如何产生混合柱状图

要产生混合柱状图，需要满足三个条件：

<<<<<<<<<<<<<<<<<<<<<<<<<<<<引用自12c在线文档>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

从12cR1开始，当满足以下三个条件时，数据库会生成混合柱状图：

  如果用户指定分组数，分组数要小于唯一值数。如果没有指定分组数，其默认为254，即唯一值数要大于254；

  生成Top－N频率柱状的条件不被满足，其条件参考http://www.hellodba.com/reader.php?ID=212；

  取样百分百设置为AUTO_SAMPLE_SIZE；

如果用户指定了百分百，数据库则会生成高平衡柱状图。

<<<<<<<<<<<<<<<<<<<<<<<<<<<<引用自12c在线文档>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

注意：取样百分比还可以设置为NULL（即由数据库自己计算）生成混合柱状图。而由于混合柱状图的出现是为了解决高平衡柱状图的问题，因此他们之间是互斥的。即生成混合柱状图的条件满足后，统计数据收集过程就不会考虑高平衡柱状图。

在统计数据收集过程当中，有两条路径可以生成混合柱状图，

  * 当取样百分比设置为NULL时，统计数据收集过程会用一条查询语句获取唯一值的一些原始信息，并将它们按顺序逐个分入分组当中。以下为该查询语句的示例，

    select substrb(dump(val,16,0,64),1,240) ep,  freq, cdn, ndv, (sum(pop) over()) popcnt,  (sum(pop*freq) over()) popfreq,  substrb(dump(max(val) over(),16,0,64),1,240) maxval,  substrb(dump(min(val) over(),16,0,64),1,240) minval  from (select val, freq,  (sum(freq) over()) cdn, (count(*) over()) ndv,  (case when freq > ((sum(freq) over())/25)  then 1  else 0 end) pop from  (select /*+  no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad  */ "A"  val, count("A") freq  from "OE"."T_NTOP" t  where "A" is not null  group by "A")) order by val;

  * 当取样百分比设置为AUTO_SAMPLE_SIZE时，统计数据收集过程会先尝试建立频率柱状图。如果当前设置不满足建立频率柱状图的条件，如，MNB < NDV，该过程将会将这些用于创建频率柱状图的数据转换为混合柱状图。

 

与高平衡柱状图类似，这些数据被逐个分入分组当中，分组的大小为数据量除以分组数（not-null value number/bucket number）的平均大小。一旦数据溢出，则会被分入下一分组。分组中最后一个数值数据量则为该分组的endpoint_repeat_count。最后一个分组则会容纳下所有还未被分配的数值，无论非结束点数值是否为“流行”值。

--- Fuyuncat ---
上一篇： 下一篇：
本类中的所有文章

Top

Copyright ©2005，HelloDBA.Com 保留一切权利

申明
by fuyuncat