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

Oracle 12c TOP－N Frequency Histogram

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2013-08-21 23:33:16

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

 

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

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

Top－N频率柱状图是频率柱状图的一个变种，它忽略了那些“非流行数据”（即出现频率低的数值）。例如，1000枚硬币中只有一枚面值1分的硬币，那在创建柱状图分组时，它就就可以被忽略。Top－N频率柱状图能产生一个更利于“流行数据”（高频率数据）的柱状图。

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

 

如何产生Top－N频率柱状图

 

我们首先需要了解到的一个事实就是，在收集统计数据时，入股为估算比设置了一个非默认值，则统计数据过程就类似于11g，即不会产生新类型的柱状图。因此，产生Top－N频率柱状图的一个必要条件就是让估算比为默认值。

例如，

SQL代码
  1. HelloDBA.COM> exec dbms_stats.gather_table_stats(ownname=>'OE',tabname=>'T_NTOP',method_opt =>'for columns A size 25');  

从对Top－N频率柱状图的描述可以知道，Top－N频率柱状图的柱状图分组数量一定是小于其唯一值数量（Distinct Value Number）的。所以，产生Top－N频率柱状图的另外一个必要条件是设置的分组数或者默认分组数设置（默认为254）小于其唯一值数。

在进一步为字段收集统计数据之前，统计数据收集过程首先会计算近似唯一值数。这一步骤会调用SQL分析器（SQL Analyzer）来分析根据当前输入参数所产生的一条SQL语句，例如如下语句，

SQL代码
  1. select /*+  full(t)    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  */to_char(count("A")),to_char(substrb(dump(min("A"),16,0,64),1,240)),to_char(substrb(dump(max("A"),16,0,64),1,240)),to_char(count("B")),count(rowidtochar(rowid)) from "OE"."T_NTOP" t  /* TOPN,NIL,NIL,ACL,RWID,U25,UU*/  

SQL分析器不光会获得这条查询语句的结果，还会根据输入选项（如TOPN,NIL,NIL,ACL,RWID,U25,UU）在执行和分析过程中调用内部函数获取更多的额外信息。其中就有TOP－N数值信息。然而，如果TOP－N数值的数据总数在该字段的非空值数据总数中的比例低于一个阈值（1-1/MNB，MNB为最大分组数，Maximum Number of Buckets，它是影响选择频率柱状图还是高平衡柱状图的重要因素，参见http://www.hellodba.com/reader.php?ID=19&lang=EN）时，TOP－N数据会被丢弃，也就是说，不会产生Top－N频率柱状图。因而，TOP－N数值的数据总数在该字段的非空值数据总数中的比例大于(1-1/MNB)也成为产生Top－N频率柱状图的一个必要条件。

而字段的最大、最小唯一值必须包含在柱状图数据当中，因此统计过程还需要检查是否需要从现有Top－n数据中移除数据以容纳最大、最小值：如果最大、最小值已经在Top－N数据当中，则不需要移除，否则将数据量最小的数值移除以腾出位置给最大最小值。相应的，要根据调整后的Top－N数据记录总数在非空数值记录总数中的比例再与阈值比较以决定是否采纳Top－N频率柱状图。

概括之，要产生Top－N频率柱状图，需要满足以下条件：

× 估算比为默认值；

× 柱状图分组数小于唯一值数；

× （调整后的Top－N数据记录总数）/（非空数值记录总数） > (1 - (1/MNB))

 

演示

 

以下用一个例子来演示Top－N频率柱状图的产生。

SQL代码
  1. HelloDBA.COM> create table oe.t_ntop (a number, b number);  
  2.   
  3. Table created.  
  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> exec dbms_stats.gather_table_stats(ownname=>'OE',tabname=>'T_NTOP',method_opt =>'for columns A size 25');  
  16.   
  17. PL/SQL procedure successfully completed.  
  18.   
  19. HelloDBA.COM> select histogram from all_tab_columns where owner='OE' and table_name='T_NTOP' and column_name='A';  
  20.   
  21. HISTOGRAM  
  22. ---------------  
  23. HYBRID  

可以看到，尽管设置了分组数为小于唯一值数（30）的25，并采用了默认估算比，统计收集过程最终还是未给该字段收集Top－N频率柱状图。我们需要检查调Top－N数据记录总数在非空数值记录总数中的比例以及阈值。

SQL代码
  1. HelloDBA.COM> select total_topn, total_notnull, round(total_topn/total_notnull*100,2) pct, round((1-1/25)*100,2) threshold from (select sum(case when rownum<=25 then cnt else 0 end) total_topn, sum(cnt) total_notnull, count(1) dvn from (select a, count(1) cnt from oe.t_ntop where a is not null group by a order by count(1) desc));  
  2.   
  3. TOTAL_TOPN TOTAL_NOTNULL        PCT  THRESHOLD  
  4. ---------- ------------- ---------- ----------  
  5.       4500          4650      96.77         96  

 最初计算的Top－N数据记录总数在非空数值记录总数中的比例是大于阈值的。再看Top－N数据记录总数是否会被调整:

SQL代码
  1. HelloDBA.COM> select * from (select a, count(1) cnt from oe.t_ntop where a is not null group by a order by count(1) desc) where rownum<=25;  
  2.   
  3.          A        CNT  
  4. ---------- ----------  
  5.         30        300  
  6. ... ...  
  7.         6         60  
  8.   
  9. 25 rows selected.  
  10.   
  11. HelloDBA.COM> select max(a) maxa, min(a) mina from oe.t_ntop;  
  12.   
  13.       MAXA       MINA  
  14. ---------- ----------  
  15.         30          1  

最小值（1）并未在最初的Top－N数值当中，它要替换Top－N数值当中的数据量（60）最少的数（6）。调整后计算得到的百分比为：

SQL代码
  1. HelloDBA.COM> select round((4500-60+1)/4650*100,2) adjusted_pct from dual;  
  2.   
  3. ADJUSTED_PCT  
  4. ------------  
  5.        95.51  

这一值小于阈值，因而不会产生Top－N频率柱状图。

 

基于Top－N频率柱状图的选择率计算

 

基于Top－N频率柱状图的选择率计算并不复杂。

 × 如果判定谓词中数据位于柱状图当中，则采用柱状图数据计算选择率；

 × 如果判定谓词中数据不位于柱状图当中，则由柱状图以外的唯一值数及其数据数量来计算选择率；

举例说明：

SQL代码
  1. HelloDBA.COM> exec dbms_stats.gather_table_stats(ownname=>'OE',tabname=>'T_NTOP',method_opt =>'for columns A size 26');  
  2.   
  3. PL/SQL procedure successfully completed.  
  4.   
  5. HelloDBA.COM> select sample_size, density, histogram from all_tab_columns where owner='OE' and table_name='T_NTOP' and column_name='A';  
  6.   
  7. SAMPLE_SIZE    DENSITY HISTOGRAM  
  8. ----------- ---------- ---------------  
  9.        4650 .000107527 TOP-FREQUENCY  
  10.   
  11. HelloDBA.COM> select endpoint_value, endpoint_number from dba_histograms where owner='OE' and table_name='T_NTOP' and column_name='A';  
  12.   
  13. ENDPOINT_VALUE ENDPOINT_NUMBER  
  14. -------------- ---------------  
  15.              1               1  
  16.              6              61  
  17. ... ...  
  18.             19            1751  
  19.             20            1951  
  20. ... ...  
  21.             30            4501  

注意：因为最小值（1）没有被包含在最初分析的Top－N值当中，它替换了里面数据量最少的唯一值（5），并且数据量设置为1。

SQL代码
  1. HelloDBA.COM> set autot trace exp  
  2. HelloDBA.COM> select * from oe.t_ntop where a = 20;  
  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  |        |   200 |  1200 |     5   (0)| 00:00:01 |  
  12. |*  1 |  TABLE ACCESS FULL| T_NTOP |   200 |  1200 |     5   (0)| 00:00:01 |  
  13. ----------------------------------------------------------------------------  
  14.   
  15. Predicate Information (identified by operation id):  
  16. ---------------------------------------------------  
  17.   
  18.    1 - filter("A"=20)  

唯一值20的分组数据量为200（1951－1751），计算所得选择率为 200/取样大小=200/4650，因此，过滤后的数据记录数为 cardinality*selectivity=4650。

SQL代码
  1. HelloDBA.COM> select * from oe.t_ntop where a = 2;  
  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  |        |    37 |   222 |     5   (0)| 00:00:01 |  
  11. |*  1 |  TABLE ACCESS FULL| T_NTOP |    37 |   222 |     5   (0)| 00:00:01 |  
  12. ----------------------------------------------------------------------------  
  13.   
  14. Predicate Information (identified by operation id):  
  15. ---------------------------------------------------  
  16.   
  17.    1 - filter("A"=2)  

“2”不是一个Top－N数值，这一谓词表达式的选择率为该字段的密度（Density）。然而，对于Top－N频率柱状图字段，优化器会根据非Top－N数值数据重新计算密度。本例当中，新的密度为 （非空值总数 － Top－N数值总数）/（非Top－N数值总数）/（非空值总数）= (4650-4501)/(30-26)/4650=0.008010753。因此，过滤后的数据记录为 cardinality*selectivity=trunc(4650*.008010753)=37。

TIPS: 新计算所得密度可以通过10053事件跟踪观察到。

 

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

Top

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

申明
by fuyuncat