Oracle直方图详解

141次阅读

共计 28654 个字符，预计需要花费 72 分钟才能阅读完成。

1 直方图的含义

在 Oracle 数据库中，CBO 会默认认为目标列的数据在其最小值 LOW_VALUE 和最大值 HIGH_VALUE 之间均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加查询条件后的可选择率以及结果集的 Cardinality，进而据此来计算成本值并选择执行计划。但目标列的数据是均匀分布这个原则并不总是正确的，在实际的系统中，我们很容易就能看到一些目标列的数据分布是不均匀的，甚至是极度倾斜、分布极度不均衡的。对这样的列如果还按照均匀分布的原则去计算可选择率与 Cardinality，并据此来计算成本、选择执行计划，那么 CBO 所选择的执行计划就可能是不合理的，甚至是错误的。

看一个由于数据分布极不均衡而导致 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 就能据此来做出相对准确的判断。

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-02/141007p2.htm

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 的所有数据。