Oracle直方图详解
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就能据此来做出相对准确的判断。