1 直方图的含义
在Oracle数据库中，CBO会默认认为目标列的数据在其最小值LOW_VALUE和最大值HIGH_VALUE之间均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加查询条件后的可选择率以及结果集的Cardinality，进而据此来计算成本值并选择执行计划。但目标列的数据是均匀分布这个原则并不总是正确的，在实际的系统中，我们很容易就能看到一些目标列的数据分布是不均匀的，甚至是极度倾斜、分布极度不均衡的。对这样的列如果还按照均匀分布的原则去计算可选择率与Cardinality，并据此来计算成本、选择执行计划，那么CBO所选择的执行计划就可能是不合理的，甚至是错误的。
看一个由于数据分布极不均衡而导致CBO选错执行计划的例子：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
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不收集直方图统计信息的方式收集一下统计信息：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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：
|
1
2
3
4
5
|
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的执行计划：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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的情形。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
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的显示结果来模拟：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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来实际收集一下直方图统计信息
|
1
2
3
|
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上依然没有直方图统计信息：
|
1
2
3
4
5
|
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条件中被使用过
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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自动收集直方图统计信息：
|
1
2
3
4
5
6
7
8
9
|
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类型的直方图的具体信息：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|