ORACLE直方图
来源:互联网 发布:淘宝店铺招牌图片女装 编辑:程序博客网 时间:2024/06/03 01:32
为什么需要直方图 ?当表中一列数据比较的值分布比较均匀时,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和桶数
就可以判断出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和桶数
0 0
- Oracle直方图
- Oracle 直方图
- ORACLE直方图
- oracle直方图
- oracle直方图
- Oracle直方图详解
- Oracle直方图详解
- Oracle直方图详解
- 转:Oracle直方图详解
- ORACLE等频直方图
- ORACLE等频直方图
- oracle直方图注意事项
- oracle直方图介绍
- Oracle Histogram直方图解析
- 如何看懂oracle 直方图
- oracle 直方图 使用
- Oracle直方图解析
- Oracle直方图解析
- Win32_NetworkAdapterConfiguration
- VBS枚举出这个类的所有属性
- VMware6 下安装Fedora 14
- bad ELF interpreter: No such file or directory
- Google无人驾驶汽车与机器人组合,秒杀无人机
- ORACLE直方图
- nand flash和nor flash
- rpm安装包下载地址
- Makefile:431: *** mixed implicit and normal rules. Stop.
- 转载:BOA的移植
- 编译模块(makefile)
- VS2008中使用CHttpFile类来提交表单
- 取得IE,然后运行本地JS
- rhel6安装ReviewBoard用相关rpm包及相关使用配置说明