首页 > 代码库 > ORACLE直方图（10g）
ORACLE直方图（10g）
为什么需要直方图 ？当表中一列数据比较的值分布比较均匀时，optimzer可以很好的通过最大值，最小值和NDV(唯一值的个数)，就可以判断出cardinality.对于cardinality越精确，optimzer就可以更加好的选择执行计划。
--创建测试表并插入数据
create table t1(a int,b varchar2(100));
begin
for i in 1..100 loop
insert into t1 values (1,‘abcd‘);
end loop;
commit;
end;
/
begin
for i in 1..100 loop
insert into t1 values (2,‘efg‘);
end loop;
commit;
end;
/
---收集统计信息
exec dbms_stats.gather_table_stats(tabname => ‘t1‘,ownname => user,method_opt => ‘for all columns size 1‘); --for all columns size 1 不收集直方图信息
---执行一个语句来看看optimizer评估的行
explain plan for select * from t1 where a=1;
select * from table(dbms_xplan.display());
--------------------------------------------------------------------------
| 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 t1 values(3,‘mnb‘); 一行，人为的模拟数据分布不均,再次收集统计信息
explain plan for select * from t1 where a=3;
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 => ‘T1‘,ownname => user,method_opt => ‘FOR ALL COLUMNS SIZE AUTO‘);
SQL> explain plan for select * from t1 where a=3;
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 t2(a int);
begin
for i in 1..76 loop
insert into t2 values (i);
end loop;
commit;
end;
/
SQL> select count(distinct a) from t2; --insert 76种不同的值
COUNT(DISTINCTA)
----------------
76
SQL> exec dbms_stats.gather_table_stats(tabname => ‘T2‘,ownname => user,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=‘T2‘;
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
A HEIGHT BALANCED
对于频率直方图，如果NDV小于254的情况，ndv应该是和桶数相等的.有些bug会产生不一致，导致评估不准确，具体可以参考metalink的相关bug。
SQL> select count(b.endpoint_value) from user_histograms b where table_name=‘T1‘ and column_name=‘A‘;
COUNT(B.ENDPOINT_VALUE)
-----------------------
3
SQL> select table_name,column_name,num_distinct from user_tab_col_statistics where table_name=‘T1‘ and column_name=‘A‘;
TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------------------------ ------------------------------ ------------
T2 A 3
一般建议的收集方法为‘FOR ALL COLUMNS SIZE AUTO‘,除非有很好的理由去更改，由oracle自行决定是否需要histogram和桶数
为什么需要直方图 ？当表中一列数据比较的值分布比较均匀时，optimzer可以很好的通过最大值，最小值和NDV(唯一值的个数)，就可以判断出cardinality.对于cardinality越精确，optimzer就可以更加好的选择执行计划。
--创建测试表并插入数据create table t1(a int,b varchar2(100));beginfor i in 1..100 loopinsert into t1 values (1,‘abcd‘);end loop;commit;end;/beginfor i in 1..100 loopinsert into t1 values (2,‘efg‘);end loop;commit;end;/---收集统计信息exec dbms_stats.gather_table_stats(tabname => ‘t1‘,ownname => user,method_opt => ‘for all columns size 1‘); --for all columns size 1 不收集直方图信息
---执行一个语句来看看optimizer评估的行explain plan for select * from t1 where a=1;select * from table(dbms_xplan.display());--------------------------------------------------------------------------| 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 t1 values(3,‘mnb‘); 一行，人为的模拟数据分布不均,再次收集统计信息explain plan for select * from t1 where a=3;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 => ‘T1‘,ownname => user,method_opt => ‘FOR ALL COLUMNS SIZE AUTO‘);SQL> explain plan for select * from t1 where a=3;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 t2(a int);beginfor i in 1..76 loopinsert into t2 values (i);end loop;commit;end;/SQL> select count(distinct a) from t2; --insert 76种不同的值COUNT(DISTINCTA)---------------- 76SQL> exec dbms_stats.gather_table_stats(tabname => ‘T2‘,ownname => user,method_opt => ‘FOR COLUMNS A SIZE 75‘);人为的定义桶数小于NDV,在这种条件，oracle会使用高度平衡直方图，因为频率直方图75个bucket容不下76SQL> select column_name,histogram from user_tab_col_statistics where table_name=‘T2‘;COLUMN_NAME HISTOGRAM------------------------------ ---------------A HEIGHT BALANCED
对于频率直方图，如果NDV小于254的情况，ndv应该是和桶数相等的.有些bug会产生不一致，导致评估不准确，具体可以参考metalink的相关bug。SQL> select count(b.endpoint_value) from user_histograms b where table_name=‘T1‘ and column_name=‘A‘;COUNT(B.ENDPOINT_VALUE)----------------------- 3SQL> select table_name,column_name,num_distinct from user_tab_col_statistics where table_name=‘T1‘ and column_name=‘A‘;TABLE_NAME COLUMN_NAME NUM_DISTINCT------------------------------ ------------------------------ ------------T2 A 3一般建议的收集方法为‘FOR ALL COLUMNS SIZE AUTO‘,除非有很好的理由去更改，由oracle自行决定是否需要histogram和桶数
ORACLE直方图（10g）