扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >Oracle的直方图
  • 833

0
分享到

Oracle的直方图

2024-04-02 19:04:59 833人浏览 安东尼
摘要

1 直方图的含义在oracle数据库中，CBO会默认认为目标列的数据在其最小值LOW_VALUE和最大值HIGH_VALUE之间均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加查询条件后的可选择率以

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的直方图最后，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

您可能感兴趣的文档:

--结束END--

本文标题: Oracle的直方图

本文链接: https://lsjlt.com/news/40893.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • Oracle的直方图
    1 直方图的含义在Oracle数据库中，CBO会默认认为目标列的数据在其最小值LOW_VALUE和最大值HIGH_VALUE之间均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加查询条件后的可选择率以...
    99+
    2024-04-02
  • oracle直方图
    直方图概述：直方图是一种统计学上的工具，并非Oracle专有，通常情况下它会表现为一种几何图形表，这个图形表是根据从实际环境中所收集来的被管理对象某个方面的质量分布情况的数据所绘制成的，通常会画成以数量为底...
    99+
    2024-04-02
  • Oracle直方图统计信息的应用
    Oracle直方图统计信息说明了表中数据的分布情况，用于在表中数据分布十分不均衡的情况下，指导CBO优化器选择最优的执行计划。以下例子说明了这一应用。 创建表 create tab...
    99+
    2024-04-02
  • python绘制直方图的方法
    本文实例为大家分享了python绘制直方图的具体代码，供大家参考，具体内容如下 用两列数据绘制直方图 #coding=gbk import xlwings as xw impor...
    99+
    2024-04-02
  • python绘制饼图和直方图的方法
    本文实例为大家分享了python绘制饼图和直方图的具体代码，供大家参考，具体内容如下 #饼图，常与结构分析结合使用 import pandas import numpy impo...
    99+
    2024-04-02
  • Python图像运算之图像掩膜直方图和HS直方图详解
    目录一.图像掩膜直方图二.图像HS直方图三.直方图判断白天黑夜四.总结一.图像掩膜直方图 如果要统计图像的某一部分直方图，就需要使用掩码（蒙板）来进行计算。假设将要统计的部分设置为白...
    99+
    2024-04-02
  • Python绘制直方图
    文章目录 初步参数绘图类型多组数据直方图对比 初步 对于大量样本来说，如果想快速获知其分布特征，最方便的可视化方案就是直方图，即统计落入不同区间中的样本个数。 以正态分布为例 impo...
    99+
    2023-10-27
    python matplotlib 直方图 hist 统计
  • Oracle中直方图对执行计划的影响有哪些
    这篇文章主要介绍Oracle中直方图对执行计划的影响有哪些，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！前言在Oracle数据库中，CBO会默认目标列的数据在其最小值low_valu...
    99+
    2024-04-02
  • Oracle的自动统计信息不收集直方图的信息
    在oracle9i中，默认的统计信息收集是不收集直方图信息的，也就是说默认的MOTHOD_OPT模式为FOR ALL COLUMNS SIZE 1 在10g开始，dbms_stats包中默认的ME...
    99+
    2024-04-02
  • python OpenCV图像直方图处理
    目录1.图像直方图基本含义和绘制2.OpenCV统计直方图并绘制3.使用掩码的直方图-直方图、掩膜4.直方图均衡化原理及函数5.子图的绘制6.直方图均衡化对比1.图像直方图基本含义和...
    99+
    2024-04-02
  • 直方图使用技巧及analyze table操作对直方图统计的影响
    原文：http://www.cnblogs.com/yumiko/p/6060485.html 前言 针对索引列，尤其是存在严重数据倾斜的索引列，直方图的统计信息，对于CBO优...
    99+
    2024-04-02
  • python绘制简单直方图的方法
    直方图，又称质量分布图，是一种统计报告图，由一系列高度不等的纵条或线段表示数据分布情况。用横轴表示数据类型，纵轴表示分布情况。直方图是数值数据分布的精确图形表示。绘制连续性的数据，展...
    99+
    2024-04-02
  • python中pyplot直方图的绘制方式
    目录pyplot直方图的绘制python灰度直方图绘制pyplot直方图的绘制 import matplotlib.pyplot as plt import numpy as np ...
    99+
    2024-04-02
  • Python matplotlib.pyplot.hist()绘制直方图的方法实例
    目录一、matplotlib.pyplot.hist()语法二、绘制直方图①绘制简单直方图②：各个参数绘制的直方图（1）histtype参数（设置样式bar、barstacked、s...
    99+
    2024-04-02
  • python中opencv 直方图处理
    目录直方图处理直方图的含义绘制直方图使用Numpy绘制直方图使用OpenCV绘制直方图使用掩模绘制直方图直方图均衡化直方图均衡化原理直方图均衡化处理pyplot 模块介绍subplo...
    99+
    2024-04-02
  • 使用Python生成直方图
    1. 引言 你想知道如何在Python中生成直方图吗？在本教程中，我将向大家展示如何做到这一点。 数值数据的分布可以用直方图表示。直方图是数据的一种可视化表示，它使用不同高度的条形图，其中每个条形图标...
    99+
    2023-09-02
    python pandas
  • Python 之 Matplotlib 柱状图（竖直柱状图和水平柱状图）、直方图和饼状图
    文章目录 一、柱状图二、竖直柱状图1. 基本的柱状图2. 同位置多柱状图3. 堆叠柱状图 三、水平柱状图1. 基本的柱状图2. 同位置多柱状图3. 堆叠柱状图 四、直方图 plt.hi...
    99+
    2023-09-05
    python matplotlib 开发语言
  • Python+matplotlib绘制条形图和直方图
    目录摘要一、bar()函数二，hist()函数三、数据统计摘要 先介绍条形图直方图，然后用随机数生成一系列数据，保存到列表中，最后统计出相关随机数据的概率并展示 前述介绍了由点进行划...
    99+
    2024-04-02
  • python OpenCV图像直方图如何处理
    这篇“python OpenCV图像直方图如何处理”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“python&nb...
    99+
    2023-07-02
  • 基于OpenCV的直方图匹配的实现方法
    如何为图像生成直方图，如何使直方图相等，最后如何将图像直方图修改为与其他直方图相似。 01. 什么是图像直方图？ 在开始定义直方图之前，为简单起见我们先使用灰度图像，稍后再解释彩色图...
    99+
    2024-04-02
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作