时间：2021-07-01 10:21:17 帮助过：4人阅读
在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‘，所以对于上述