ORACLE 12C默认直方图的变化

                                             ORACLE 12C默认直方图的变化

      在12C以前的版本中,当Number Distinct Value(NDV)大于Bucket Number(BN)时,使用的是高度平衡直方图,
在12C中引入混合直方图和TOP N直方图.在NDV大于BN的情况下会使用混合直方图,而原来的高度平衡直方图由于
存在的较大的误差,所以会慢慢放弃.

我们来看一个例子

DONGDONGTANG> create table t1 (a int, b varchar2(100));
Table created.
    
DONGDONGTANG> create index ix_a on t1(a);

Index created.

DONGDONGTANG> create index ix_b on t1(b);

Index created.

DONGDONGTANG> insert into t1
select level, substr('abcdefghbiklkmtnok',dbms_random.value(1,20),dbms_random.value(1,20)) ||trunc(dbms_random.value(1,1000))
from dual
connect by level<=1000000;
1000000 rows created.

DONGDONGTANG> commit;

在12C和11g中使用以下方式来收集统计信息

DONGDONGTANG> begin
 dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T1',method_opt=>'FOR ALL COLUMNS SIZE 253');
end;
/  

PL/SQL procedure successfully completed.
在12C中,我们可以看到oracle收集了混合直方图

DONGDONGTANG> select column_name,histogram from dba_tab_col_statistics where table_name='T1' and owner='SYS';
COLUMN_NAME       HISTOGRAM
------------------------------ ---------------
B       HYBRID
A       HYBRID

11G中收集的直方图信息

SYS@11G >select column_name,histogram from dba_tab_col_statistics where table_name='T1' and owner='SYS';

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

由于对cardinality的评估更加准确,带来的变化就是导致在升级后执行计划发生变化,而11G的高度平
衡直方图误差更小,
11G >select count(a) from t1 where b='kdf'; 

  COUNT(A)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 4116353145


-------------------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |    |  1 | 15 | 10   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |    |  1 | 15 | |    |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |  6 | 90 | 10   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN     | IX_B |  6 |    |  3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

12C> select count(a) from t1 where b='kdf'; 
  COUNT(A)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2507449121
---------------------------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |    |  1 | 15 | 16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |    |  1 | 15 | |    |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   | 12 | 180 | 16   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN     | IX_B | 12 |    |  3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
可以看出12C的在相等谓词下,评估返回的行数更多.

使用其他谓词操作的时候
11G >select count(a) from t1 where b>'k581' and b<'k585';
  COUNT(A)
----------
       174
Execution Plan
----------------------------------------------------------
Plan hash value: 4116353145


-------------------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |    |  1 | 15 | 16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |    |  1 | 15 | |    |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 12 | 180 | 16   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN     | IX_B | 12 |    |  3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

12C> select count(a) from t1 where b>'k581' and b<'k585';

  COUNT(A)
----------
       314
Execution Plan
----------------------------------------------------------
Plan hash value: 2507449121
---------------------------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |    |  1 | 15 |  4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |    |  1 | 15 | |    |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   | 12 | 180 |  4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN     | IX_B |  1 |    |  3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
12C满足查询的行有314行,而评估的谓词只有1,而11G的评估相对而言误差反而没有这么大.