ORACLE直方图

                                               ORACLE直方图

    为什么需要直方图 ？当表中一列数据比较的值分布比较均匀时，optimzer可以很好的通过最大值，最小值和NDV(唯一值的个数)
就可以判断出cardinality.对于cardinality越精确，optimzer就可以更加好的选择执行计划
  
  创建一个表

SQL> create table frank.t2(a int,b varchar2(100));

Table created.

分别执行insert into frank.t2 values(1,'abcd')和insert into frank.t2 values(2,'efg'), 然后收集统计信息

 
SQL> begin
  2     dbms_stats.gather_table_stats(tabname => 'T2',ownname => 'FRANK',method_opt => 'FOR ALL COLUMNS SIZE 1');
  3     end;
  4  /

--FOR ALL COLUMNS SIZE 1 不收集直方图信息

执行一个语句来看看optimizer评估的行
SQL>explain plan for select * from t2 where a=1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |   700 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |   100 |   700 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

返回100行，说明优化器在这种数据平均分布的情况下评估很准确.

现在insert into frank.t2 values(3,'mnb') 一行，人为的模拟数据分布不均,再次收集统计信息

SQL> explain plan for select * from t2 where a=3;

Explained.

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    67 |   469 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |    67 |   469 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

优化器评估为67行.计算公式为 rows/ndv=(200/3)=66.66666

看看收集了集方图后的结果
SQL> exec dbms_stats.gather_table_stats(tabname => 'T2',ownname => 'FRANK',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL>  explain plan for select * from t2 where a=3;

Explained.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |     7 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

可以看出通过增加了直方图,oracle比较准确的评估了cardinality。

SQL> select column_name,histogram from user_tab_col_statistics where table_name='T2';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
A                              FREQUENCY       --频率直方图
B                              NONE

直方图分为两种频率直方图和高度平衡直方图

直方图的限制:1，收集直方图有开销，如cpu和磁盘空间;2,对于每个栏位超过254的distinct value，频率直方图的作用开始下降
随着NDV的增加，精度进一步下降,这时候只能使用高度平衡直方图.3,对于字符类型，只能收集前32个字节;
4,在非索引的栏位上收集直方图的效果有限.

高度平衡和频率直方图的选择:对于某个栏位的NDV小于所定义的桶数，使用频率直方图，否则使用高度平衡直方图。两种方式的最大
的桶数为254,

SQL> create table t3(a int);

Table created.

SQL> select count(distinct a) from t3;  --insert 76种不同的值

COUNT(DISTINCTA)
----------------
              76

SQL> exec dbms_stats.gather_table_stats(tabname => 'T3',ownname => 'FRANK',method_opt => 'FOR COLUMNS A SIZE 75');

人为的定义桶数小于NDV,在这种条件，oracle会使用高度平衡直方图，因为频率直方图75个bucket容不下76

SQL>  select column_name,histogram from user_tab_col_statistics where table_name='T3';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
A                              HEIGHT BALANCED

 

对于频率直方图，如果NDV小于254的情况，ndv应该是和桶数相等的.有些bug会产生不一致，导致评估不准确，具体可以参考metalink
的相关bug。

SQL> select count(b.endpoint_value)
  2  from user_histograms b
  3  where table_name='T2'
  4  and column_name='A'
  5  ;

COUNT(B.ENDPOINT_VALUE)
-----------------------
                      3

SQL> select table_name,column_name,num_distinct from user_tab_col_statistics
  2  where table_name='T2' and column_name='A';

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
T2                             A                                         3

 

一般建议的收集方法为'FOR ALL COLUMNS SIZE AUTO',除非有很好的理由去更改，由oracle自行决定是否需要histogram和桶数