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的评估相对而言误差反而没有这么大.