首页 > 代码库 > Oracle的直方图
Oracle的直方图
1 直方图的含义
在Oracle数据库中，CBO会默认认为目标列的数据在其最小值LOW_VALUE和最大值HIGH_VALUE之间均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加查询条件后的可选择率以及结果集的Cardinality，进而据此来计算成本值并选择执行计划。但目标列的数据是均匀分布这个原则并不总是正确的，在实际的系统中，我们很容易就能看到一些目标列的数据分布是不均匀的，甚至是极度倾斜、分布极度不均衡的。对这样的列如果还按照均匀分布的原则去计算可选择率与Cardinality，并据此来计算成本、选择执行计划，那么CBO所选择的执行计划就可能是不合理的，甚至是错误的。
看一个由于数据分布极不均衡而导致CBO选错执行计划的例子：
zx@ORCL>create table t1 (a number(5),b varchar2(5)); Table created. zx@ORCL>declare cnt number(5) := 1; 2 begin 3 loop 4 insert into t1 values(1,‘1‘); 5 if cnt=10000 then 6 exit; 7 end if; 8 cnt:=cnt+1; 9 end loop; 10 insert into t1 values(2,‘2‘); 11 commit; 12 end; 13 / PL/SQL procedure successfully completed. zx@ORCL>select b,count(*) from t1 group by b; B COUNT(*) --------------- ---------- 1 10000 2 1 zx@ORCL>create index t1_ix_b on t1(b); Index created.
对表T1不收集直方图统计信息的方式收集一下统计信息：
zx@ORCL>exec dbms_stats.gather_table_stats(USER,‘T1‘,estimate_percent=>100,method_opt=>‘for all columns size 1‘); PL/SQL procedure successfully completed. zx@ORCL>select * from t1 where b=‘2‘; A B ---------- --------------- 2 2 zx@ORCL>select * from table(dbms_xplan.display_cursor(null,null,‘all‘)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5p7b772tpcvm4, child number 0 ------------------------------------- select * from t1 where b=‘2‘ Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 5001 | 25005 | 7 (0)| 00:00:01 | -------------------------------------------------------------------------- .....省略部分输出
从执行计划可以看出执行计划走的是全表扫描，但是很显然应该走索引T1_IX_B。这是因为CBO默认认为列B的数据是均匀分布的，而列B上的distinct值只有1和2这两值，所以CBO评估出来的对列B施加等值查询条件的可选择率就是1/2,进而评估出来对列B施加等值查询条件的结果集的Cardinality就是5001：
zx@ORCL>select round(10001*(1/2)) from dual; ROUND(10001*(1/2)) ------------------ 5001
正因为CBO评估出上述等值查询要返回结果集的Cardinality是5001，已经占了表T1总记录数的一半，所以CBO认为此时再走列B上的索引T1_IX_B就已经不合适了，进而就选择了对列T1的全表扫描。但实际上，CBO对上述等值查询要返回结果集的Cardinality的评估已经与事实严重不符，评估出来的值是5001，其实却只有1，差了好几个数量级。
CBO这里选择了执行计划，正确的执行计划应该是走索引T1_IX_B。CBO选错执行计划的根本原因是表T1的列B的分布实际上是极度不均衡的(列B一共就两值，其中10000个1，只有1个2)，CBO在评估的一开始所用的原则就错了，当然结果也就错了。
为了解决上述问题，Oracle引入了直方图(Histogram)。直方图是一种特殊的列统计信息，它详细描述了目标列的数据分布情况。直方图实际上存储在数据字典基表HISTGRM$中，可以通过数据字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS和DBA_SUBPART_HISTOGRAMS来分别查看表、分区表的分区和分区表的子分区的直方图统计信息。
如果对目标列收集了直方图，则意味着CBO将不再认为该目标列上的数据是均匀分布的了，CBO就会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和返回结果集的Cardinality，进而据此计算成本并选择相应的执行计划。
还用上面的例子，对表T1的列B收集了直方图统计信息后，CBO正确地评估出了返回结果集的Cardinality不是5001而是1，进而就正确地选择了走索引T1_IX_B的执行计划：
zx@ORCL>exec dbms_stats.gather_table_stats(USER,‘T1‘,estimate_percent=>100,method_opt=>‘for all columns size auto‘,cascade=>true); PL/SQL procedure successfully completed. #清空shared_pool,生产系统不要随便执行 zx@ORCL>alter system flush shared_pool; System altered. zx@ORCL>select * from t1 where b=‘2‘; A B ---------- --------------- 2 2 zx@ORCL>select * from table(dbms_xplan.display_cursor(null,null,‘all‘)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5p7b772tpcvm4, child number 0 ------------------------------------- select * from t1 where b=‘2‘ Plan hash value: 3579362925 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_IX_B | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- .....省略部分输出
所以，直方图就是专门为了准确评估这种分布不均匀的目标列的可选择率、结果集的Cardinality而被Oracle引入的，它详细描述了目标列的数据分布情况，并将这些分布情况记录在数据字典里，相当于直观地告诉了CBO这些列的数据分布情况，于是CBO就能据此来做出相对准确的判断。
2 直方图的类型
Oracle数据库里的直方图使用了一种称为Bucket(桶)的方式来描述目标列的数据分布。这有点类似哈希算法的Bucket，它实际上是一个逻辑上的概念，相当于分组，每个Bucket就是一组，每个Bucket里会存储一个或多个目标列上的数据。Oracle会用两个维度来描述一个Bucket，这两个维度分别是ENDPOINT NUMBER和ENDPOINT VALUE。Oracle会将每个Bucket的维度ENDPOIONTNUMBER和ENDPOINT VALUE记录在数据字典基表HISTGRM$中，这样就达到了目标列的直方图统计信息记录在数据字典中的目的。维度ENDPOINT NUMBER和ENDPOINT VALUE分别对应于数据字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS及DBA_SUBPART_HISTOGRAMS中的字段ENDPOINT_NUMBER/BUCKET_NUMBER和ENDPOINT_VALUE。同时，Oracle还会记录目标列的直方图统计信息所占用的Bucket的总数，可以通过数据字典DBA_TAB_COL_STATISTICS、DBA_PART_COL_STATISTICS及DBA_SUBPART_COL_STATISTICS中字段NUM_BUCKETS来查看目标列对应直方图的Bucket的总数。
在Oracle 12c之前，Oracle数据库里的直方图分为两种类型，分别是Frequency和HeightBalanced(Oracle 12c中还存在名为Top-Frequency和Hybrid类型的直方图)。在Oracle 12以之前，如果存储在数据字典里描述目标列直方图的Buckt的数量等于目标列的distinct值的数量，则这种类型的直方图就是Frequency类型的直方图。如果存储在数据字典里描述目标列直方图的Bucket的数量小于目标列的distinct值的数量，则这种类型的直方图就是Height Balanced类型的直方图。
2.1 Frequency类型的直方图
对于Frequency类型的直方图而言，目标列直方图的Bucket的数量就等于目标列的distinct的数量，此时目标列有多个个distinct值，Oracle在数据字典DBA_TAB_HISTOGRAMS、DBA-PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS中就会存储多少条记录，每一条记录不代表了对其中的一个Bucket的描述，上述数据字典中的字段ENDPOINT_VALUE记录了这些distinct值，而字段ENDPOINT_NUMBER是一个累加值，实际上，我们可以用一条记录的ENDPOINT_NUMBER值减去它的上一条记录的ENDPOINT_NUMBER值来得到这条记录本身所对应的ENDPOINT_VALUE值的记录数。
实际上，Frequency类型的直方图就是把目标列的每一个distinct值都记录在数据字典里，同时在数据字典里记录记录每个distinct值在目标表里一共有多少条记录，这样CBO就能非常清楚地知道目标列在目标表里的实际数据分布情况了。这种Frequency类型的直方图所对应的收集方法并不适用于目标列的distinct值非常多的情形，所以Oracle对Frequence类型的直方图有如下限制：Frequency类型的直方图所对应的Bucket的数量不能超过254(注意，Oracle 12c 中将不再有这一限制，在Oracle 12c中Frequency类型的直方图所对应的Bucket的数量可以超过254)，即Frequency类型的直方图只适用于那些目标列的distinct值数量小于或等于254的情形。
zx@ORCL>create table h (x number); Table created. zx@ORCL>declare i number; begin for i in 1..3296 loop insert into h values(1); 5 6 end loop; 7 for i in 1..100 loop 8 insert into h values(3); 9 end loop; 10 for i in 1..798 loop 11 insert into h values(5); 12 end loop; 13 for i in 1..3970 loop 14 insert into h values(7); 15 end loop; 16 for i in 1..16293 loop 17 insert into h values(10); 18 end loop; 19 for i in 1..3399 loop 20 insert into h values(16); 21 end loop; 22 for i in 1..3651 loop 23 insert into h values(27); 24 end loop; 25 for i in 1..3892 loop 26 insert into h values(32); 27 end loop; 28 for i in 1..3521 loop 29 insert into h values(39); 30 end loop; 31 for i in 1..1080 loop 32 insert into h values(49); 33 end loop; 34 commit; 35 end; 36 / PL/SQL procedure successfully completed. zx@ORCL>select count(*) from h; COUNT(*) ---------- 40000
按照Frequency类型直方图的定义，如果对列X收集Frequency类型的直方图，则DBA_TAB_HISTOGRAMS中应该有10条记录，而且这10条记录的ENDPOINT_VALUE记录的就是这10个distinct值，对应的ENDPOINT_NUMBER就是到此distinct值为止累加的行记录数。这10条记录的ENDPOINT_VALUE和ENDPOINT_NUMBER实际上可以用如下SQL的显示结果来模拟：
zx@ORCL>select x as x,count(*) as cardinality,sum(count(*)) over(order by x range unbounded preceding) as cum_cardinality from h group by x; X CARDINALITY CUM_CARDINALITY ---------- ----------- --------------- 1 3296 3296 3 100 3396 5 798 4194 7 3970 8164 10 16293 24457 16 3399 27856 27 3651 31507 32 3892 35399 39 3521 38920 49 1080 40000 10 rows selected.
上述查询结果中的列X就模拟了DBA_TAB_HISTOGRAMS中那10条记录的ENDPOINT_VALUE，列CUM_CARDINALITY就模拟了DBA_TAB_HISTOGRAMS中那10条记录的ENDPOINT_NUMBER。
对表h的列x来实际收集一下直方图统计信息
zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>‘H‘,method_opt=>‘for columns size auto X‘,cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed.
收集完统计信息后发现DBA_TAB_COL_STATISTICS中列x所对应的字段HISTOGRAM的值为NONE，这表明现在列x上依然没有直方图统计信息：
zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name=‘H‘; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- H X 10 .1 1 NONE
这种现象是正常的。因为Oracle在自动收集直方图统计信息时会秉承一个原则，那就是只对那些用过的列(即在SQL语句where条件中出现过的列)收集直方图统计信息。Oracle会在表SYS.COL_USAGE$中记录各表中各列的使用情况，在自动收集直方图统计信息时Oracle会查询SYS.COL_USAGE$，如果发现其中没有目标列的使用记录，那就不会对目标列收集直方图统计信息。表H刚刚建立，还没有在SQL语句的where条件中使用过列X，所以这里不会对列X收集直方图统计信息。
收集直方图的前提条件是：1.列上的数据分布不均匀，2.列在sql的where条件中被使用过
zx@ORCL>select name,intcol# from sys.col$ where obj# = (select object_id from dba_objects where object_name=‘H‘); NAME INTCOL# ------------------------------------------------------------------------------------------ ---------- X 1 zx@ORCL>select obj#,intcol#,equality_preds from sys.col_usage$ where obj# = (select object_id from dba_objects where object_name=‘H‘); no rows selected zx@ORCL>select count(*) from h where x=10; COUNT(*) ---------- 16293 zx@ORCL>select obj#,intcol#,equality_preds from sys.col_usage$ where obj# = (select object_id from dba_objects where object_name=‘H‘); OBJ# INTCOL# EQUALITY_PREDS ---------- ---------- -------------- 88766 1 1
再次对表H的列X自动收集直方图统计信息：
zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>‘H‘,method_opt=>‘for columns size auto X‘,cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name=‘H‘; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- H X 10 .0000125 10 FREQUENCY
另外DBA_TAB_COL_STATISTICS中列x所对应的字段HISTORAM的值已经由NONE变成了RREQUENCY，这说明现在列X上已经有了Frequency类型的直方图
可以从DBA_TAB_HISTOGRAMS中看到列x的Frequence类型的直方图的具体信息：
zx@ORCL>select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name=‘H‘; TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- H X 3296 1 H X 3396 3 H X 4194 5 H X 8164 7 H X 24457 10 H X 27856 16 H X 31507 27 H X 35399 32 H X 38920 39 H X 40000 49 10 rows selected.
从结果中可以看出，DBA_TAB_HISTOGRAMS中的10条记录与之前模拟出来的结果一模一样。
介绍完Frequency类型的直方图的含义，现在来讨论Oracle数据库里针对文本类型字段的直方图统计的先天缺陷了。
在Oracle数据库，如果针对文本开的字段收集直方图统计信息，则Oracle只会将该文本字段的文本值的头32个字节(Byte)给取出来(实际上只取头15个字节)，并将其转换成一个浮点数，然后就将这个浮点数作为其直方图统计信息存储在上述数据字典里。这种处理机制的先天身陷就在于，对于那些超过32个字节的文本型字段，只要其对应记录的文本值的头32个字节相同，Oracle在收集直方图统计信息时就会认为这引起记录在该字段的文本值是相同的，即使实际上它们并不相同。这种先天性缺陷会直接影响CBO对相关文本类型字段的可选择率及返回结果集的Cardinality的评估。
使用之前的测试表T1，其中列B为文本型字段
zx@ORCL>select b,count(*) from t1 group by b; B COUNT(*) --------------- ---------- 1 10000 2 1 zx@ORCL>select count(*) from t1 where b=‘1‘; COUNT(*) ---------- 10000 zx@ORCL>exec dbms_stats.gather_table_stats(USER,‘T1‘,estimate_percent=>100,method_opt=>‘for columns size auto B‘); PL/SQL procedure successfully completed. zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name=‘T1‘; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- T1 B 2 .000049995 2 FREQUENCY T1 A 2 .5 1 NONE
从DBA_TAB_HISTOGRAMS中查看列B的直方图具体信息
zx@ORCL>select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name=‘T1‘; TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- T1 B 10000 2.5442E+35 T1 B 10001 2.5961E+35 T1 A 0 1 T1 A 1 2
从结果可以看到，由文本型的‘1‘和‘2‘转换而来的浮点数。
转换方法：
select dump(‘1‘,16)from dual;
将0x31右边补0一直补到15个字节的长度，再将其转换为十进制数：
zx@ORCL>select dump(‘1‘,16)from dual; DUMP(‘1‘,16) ------------------------------------------------ Typ=96 Len=1: 31 zx@ORCL>select to_number(‘310000000000000000000000000000‘,‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX‘)from dual; TO_NUMBER(‘310000000000000000000000000000‘,‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX‘) ---------------------------------------------------------------------------- 2.5442E+35
转换出的值与数据字典的数据一致。
再创建一个测试表T2，有一个长度为33字节的文本型字段B：
zx@ORCL>create table t2(b varchar2(33)); Table created. zx@ORCL>insert into t2 values(‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1‘); 1 row created. zx@ORCL>insert into t2 values(‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2‘); 1 row created. zx@ORCL>insert into t2 values(‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2‘); 1 row created. zx@ORCL>commit; Commit complete.
这三条记录的头32个字节均相同，均为32个a，但distinct值有两个
zx@ORCL>select b,length(b)from t2; B LENGTH(B) --------------------------------------------------------------------------------------------------- ---------- aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1 33 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2 33 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2 33 zx@ORCL>select count(distinct(b)) from t2; COUNT(DISTINCT(B)) ------------------ 2
使用一下列B，以让SYS.COL_USAGE$中有列B的使用记录：
select count(*) from t2 where b=‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2‘;
对列B以自动方式收集直方图：
zx@ORCL>exec dbms_stats.gather_table_stats(USER,‘T2‘,estimate_percent=>100,method_opt=>‘for columns size auto B‘); PL/SQL procedure successfully completed.
现在DBA_TAB_COL_STATISTICS中列B所对应的字段HISTOGRAM的值为FREQUENCY(注意：10.2.0.4和11.2.0.1为FREQUENCY,11.2.0.4为HEIGHT BALANCED)，说明现在列B上已经有了Frequency类型的直方图统计信息：
SQL> select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name=‘T2‘; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- T2 B 1 .166666667 1 FREQUENCY
注意，上述查询结果中文本型字段B的不同distinct的值只有1个，Frequency类型的直方图所在的Bucket数量也只有1个，这明显和事实不符。其实这已经说明了对那些超过32字节的文本型字段而言，只要对应记录的文本值的头32个字节相同，Oracle在收集直方图统计信息时就会认为这些记录在该字段的文本值是相同的，即使实际上它们并不相同。
从DBA_TAB_HISTOGRAMS中看到列B的Frequency类型的直方图统计信息的具体内容，这进一步证实了上述结论：
SQL> select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name=‘T2‘; TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- T2 B 3 5.0563E+35 SQL> select dump(‘a‘,‘16‘) from dual; DUMP(‘A‘,‘16‘) ------------------------------------------------ Typ=96 Len=1: 61 SQL> select to_number(‘616161616161616161616161616161‘,‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX‘) from dual; TO_NUMBER(‘616161616161616161616161616161‘,‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX‘) ---------------------------------------------------------------------------- 5.0563E+35
通过计算相互符合。
对表T2执行如下sql
select count(*) fromt2 where b=‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1‘;
实际返回结果集的Cardinality为1
但从执行计划的结果可以看出CBO错误地评估出上述SQL返回结果集的Cardinality为3：
SQL> select count(*) from t2 where b=‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1‘; COUNT(*) ---------- 1 SQL> select * from table(dbms_xplan.display_cursor(null,null,‘all‘)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 3n69wfhjuj4sg, child number 0 ------------------------------------- select count(*) from t2 where b=‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1‘ Plan hash value: 3321871023 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 34 | | | |* 2 | TABLE ACCESS FULL| T2 | 3 | 102 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------
这是因为DBA_TAB_HISTOGRAMS中列B的Frequency类型的直方图只有1个Bucket，这会使Oracle认为表T2中只有一个distinct文本值32个‘a‘，所以对于上述SQL而言，Oracle会认为该SQL要访问的就是表T2的所有数据。
2.2 Height Balanced类型的直方图
前面介绍到Oracle 12c之前，Frequence类型的直方图对应的Bucket的数量不能超过254，那如果目标列的distinct值的数量大于254呢？此时Oracle会对目标列收集Height Balanced类型的直方图。
zx@ORCL>create table t1(id number); Table created. zx@ORCL>begin 2 for i in 1..254 loop 3 for j in 1..i loop 4 insert into t1 values(i); 5 end loop; 6 end loop; 7 commit; 8 end; 9 / PL/SQL procedure successfully completed. #distinct值的数量为254 zx@ORCL>select count(distinct(id)) from t1; COUNT(DISTINCT(ID)) ------------------- 254 #执行一个查询使id列在where条件中 zx@ORCL>select * from t1 where id=1; ID ---------- 1 #收集直方图信息 zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>‘T1‘,method_opt=>‘for columns size auto id‘,cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@ORCL>col table_name for a10 zx@ORCL>col column_name for a10 zx@ORCL>set linesize 200 zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name=‘T1‘; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- T1 ID 254 .000015372 251 FREQUENCY zx@ORCL>select endpoint_value,endpoint_number from dba_tab_histograms where owner=user and table_name=‘T1‘; ENDPOINT_VALUE ENDPOINT_NUMBER -------------- --------------- 1 1 2 3 3 6 4 10 5 15 .... 252 31878 253 32131 254 32385 254 rows selected.
从输出的结果可以看出ID列上已经有了Frequency类型的直方图。
现在对表T1再插入一条包含不同ID值的记录，然后删除列ID上的直方图信息，再列ID列重新收集直方图信息，然后查询ID列直方图的类型。
zx@ORCL>insert into t1 values(255); 1 row created. zx@ORCL>commit; Commit complete. zx@ORCL>select count(distinct id) from t1; COUNT(DISTINCTID) ----------------- 255 zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>‘T1‘,method_opt=>‘for columns size 1 id‘,cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>‘T1‘,method_opt=>‘for columns size auto id‘,cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name=‘T1‘; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- T1 ID 255 .004243247 254 HEIGHT BALANCED
从输出的结果看现在ID列上的直方图类型已经从之前的Frequency变为了Height Balanced。
对于Height Balanced类型的直方图而言，即当目标列直方图的Bucket的数量小于目标列的distinct值的数量时，Oracle首先会根据目标列对目标表的所有记录按从小到大的顺序排序，然后用目标表总的记录数除以需要使用的Bucket的数量，来决定每个Bucket里需要描述的已经排好序的记录数。假设目标表的总记录数为M，需要使用的Bucket数量为N，每个Bucket里需要描述的已经排好序的记录数为O，则O=M/N；
然后Oracle会用DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS中的每一条记录的ENDPOINT_NUMBER来记录Bucket号，Bucket号从0开始，一直到N。其中0号Bucket里存储的是目标列的最小值，所以0号Bucket所在记录的ENDPOINT_NUMBER值为0，其余Bucket所在记录的ENDPOINT_NUMBER从1一直递增到N，这些记录除了0号Bucket所在记录的ENDPOINT_VALUE值是目标列的最小值外，其他所有记录的ENDPOINT_VALUE值实际上存储的是到此记录所描述述Bucket为此之前所有Bucket描述的记录里目标列的最大值。即除了0号Bucket之外，其他所有记录的ENDPOINT_VALUE值都是用如下公式来计算的：
最后，Oracle在将这些ENDPOINT_NUMBER和ENDPOINT_VALUE存储在数据字典里时使用了一个节省存储空间的技巧：对那些相邻的公ENDPOINT_NUMBER值不同，但ENDPOINT_VALUE值相同的记录合并存储，并且只在数据字典中存储合并后的记录。比如2号桶的ENDPOINT_NUMBER是2，它的ENDPOINT_VALUE是P，3号桶的ENDPOINT_NUMBER是3，它的ENDPOINT_VALUE也是P，则Oracle就会将上述相邻的记录合并且只在数据字典中存储合并后的值。此时合并后的记录的ENDPOINT_NUMBER是3，ENDPOINT_VALUE是P也就是说DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS中Height Balanced类型的直方图所在记录的ENDPOINT_NUMBER值可能是不连续的，这种记录在数据字典里的合并后的记录所在的ENDPOINT_VALUE，Oracle称之为popular value。显然，popular value所在记录的ENDPOINT_NUMBER值和它上一条记录的ENDPOINT_NUMBER值之间的差值越大，则意味着该popular value在目标表中所占的比例也就越大，它所对应的Cardinality也就越大。
我们再来使用之前的H表来说明Height Balanced类型的直方图
先删除表H中已存在的Frequency类型的直方图
zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>‘H‘,method_opt=>‘for columns size 1 X‘,cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed.
对于Height Balanced类型的直方图而言，目标列直方图的Bucket的数量会小于目标列的distinct值的数量。这里表H有10个distinct值，如果在收集直方图统计信息的时候指定Bucket数量为5，则Oracle就应该收集Height Balanced类型的直方图了。这里收集直方图统计信息时指定method_opt的值为‘for columns size 5 X‘,这里表示在对列X收集直方图时已经指定所用Bucket的数量为5(注意，这里的Bucket数量不含0号Bucket)：
zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>‘H‘,method_opt=>‘for columns size 5 X‘,cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name=‘H‘; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- H X 10 .085276318 5 HEIGHT BALANCED
从输出来看X列所对应的字段HISTOGRAM的值为HEIGHT BALANCED，这说明X列上已经有Height Balanced类型的直方图。
现在按照刚才介绍的算法算一下DBA_TAB_HISTOGRAM中存储的Height Balanced类型的直方图统计信息的详情。
现在需要使用的Bucket数量为5(不含0号Bucket)表H中总的记录数为40000，所以每个Bucket里所需要描述的记录数为40000/5=8000。
0号Bucket所在记录的ENDPOINT_NUMBER是0，ENDPOINT_VALUE是表H中10个distinct值中最小值1。
使用如下公式计算出每个Bucket所在记录的ENDPOINT_VALUE值：
#Bucket1 zx@ORCL>select max(x) from (select x from h order by x) where rownum<8000; MAX(X) ---------- 7 #Bucket2 zx@ORCL>select max(x) from (select x from h order by x) where rownum<8000*2; MAX(X) ---------- 10 #Bucket3 zx@ORCL>select max(x) from (select x from h order by x) where rownum<8000*3; MAX(X) ---------- 10 #Bucket4 zx@ORCL>select max(x) from (select x from h order by x) where rownum<8000*4; MAX(X) ---------- 32 #Bucket5 zx@ORCL>select max(x) from (select x from h order by x) where rownum<8000*5; MAX(X) ---------- 49
从上述结果可以看到2号Bucket和3号Bucket所对应记录的ENDPOINT_VALUE值都是10，所以Oracle会将2号和3号Bucket合并存储，合并后的记录ENDPOINT_NUMBER值是3，ENDPOINT_VALUE值是10。这里10就是一个popular value。经过上述分析，我们可知DBA_TAB_HISTOGRAMS中的存储的Height Balanced类型的直方图统计信息的详细应为如下所示：
ENDPOINT_NUMBER ENDPOINT_VALUE 0 1 1 7 3 10 4 32 5 49
我们查询DBA_TAB_HISTOGRAMS中列X的Height Balanced类型的直方图统计信息：
zx@ORCL>select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name=‘H‘; TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- H X 1 7 H X 3 10 H X 4 32 H X 5 49 H X 0 1
可以看到实际查询结果与我们分析的一致。
3 直方图的收集方法
在Oracle数据库里收集直方图统计信息，通常是在调用DBMS_STATS包中的存储过程GATHER_DATABASE_STATS/GATHER_DICTIONARY_STATS/GATHER_SCHEMA_STAS/GATHER_TABLE_STATS收集统计信息时通过指定输入参数METHOD_OPT来实现。当然也可以使用ANALYZE命令来收集直方图统计信息，比如使用命令“analyze table h compute statistics forcolumns X”来收集表H的列X的直方图统计信息。因为ANALYZE命令在收集统计信息方面有先天的缺陷，所以这里只讨论用DBMS_STATS包来收集直方图统计信息。
DBMS_STATS包中上述存储过程的输入参数METHOD_OPT可以接受如下的输入值：
FOR ALL[INDEXES|HIDDEN] COLUMNS [size_clause]
FOR COLUMNS[size_clause] column|attribute [size_clause] [,column|attribute[size_clause]...]
其中的size_clause必须符合如下的格式：
SIZE {integer|REPEAT|AUTO|SKEWONLY}
size_clause子名中各选项的含义如下所述：
Integer：直方图的Bucket的数量，必须是在1~254的范围内，1表示删除该目标列上直方图统计信息。
REPEAT：只对已经有直方图统计信息的列收集直集直方图统计信息。
AUTO：让Oracle自行决定是否对目标列收集直方图统计信息，以及使用哪种类型的直方图。
SKEWONLY：只对数据分布不均衡的列收集直方图统计信息。
使用SCOTT用户下的表EMP为例来说明：
scott@ORCL> desc emp Name Null? Type ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
1)对表EMP所有有索引的列以自动收集的方式收集直方图统计信息：
exec dbms_stats.gather_table_stats(ownname=>‘SCOTT‘,tabname=>‘EMP‘,method_opt=>‘for all indexed columns size auto‘);
2)对表EMP上的列EMPNO和DEPTNO以自动收集的方式收集直方图统计信息：
exec dbms_stats.gather_table_stats(ownname=>‘SCOTT‘,tabname=>‘EMP‘,method_opt=>‘for columns size auto EMPNO DEPTNO‘);
3)对表EMP上的列EMPNO和DEPTNO收集直方图统计信息，同时指定Bucket数量均为10：
exec dbms_stats.gather_table_stats(ownname=>‘SCOTT‘,tabname=>‘EMP‘,method_opt=>‘for columns size 10 EMPNO DEPTNO‘);
4)对表EMP上的列EMPNO和DEPTNO收集直方图统计信息，同时指定列EMPNO的Bucket数量为10，列DEPTNO的Bucket数量为5：
exec dbms_stats.gather_table_stats(ownname=>‘SCOTT‘,tabname=>‘EMP‘,method_opt=>‘for columns EMPNO size 10 DEPTNO size 5‘);
5)只删除表EMP上列EMPNO的直方图统计信息：
execdbms_stats.gather_table_stats(ownname=>‘SCOTT‘,tabname=>‘EMP‘,method_opt=>‘for columns EMPNO size 1‘);
6)删除表EMP上所有列的直方图统计信息：
exec dbms_stats.gather_table_stats(ownname=>‘SCOTT‘,tabname=>‘EMP‘,method_opt=>‘for all columns size 1‘);
参考《基于Oracle的SQL优化》
官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF30103
http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF003
本文出自 “DBA Fighting!” 博客，请务必保留此出处http://hbxztc.blog.51cto.com/1587495/1900721
Oracle的直方图