ORACLE直方图学习笔记

转载 2013年12月06日 09:46:57
1. 直方图有两种类别,等频直方图与等高直方图。
   默认的,如果一个倾斜列上的唯一值超过了254个,那么ORACLE会对此列建立等高直方图,否则建立等频直方图。
   
   何谓倾斜列(或者说是列值偏差较大):例如一个表TAB有10000行记录,列A前10行的值分别1-10,
   剩下的9900行值全部都为999,则该列称为倾斜列,意思就是列的值分布不均匀。
   
2. 使用场景:通常情况下,如果TAB的A列上建有普通索引,在A列上进行等值查询的时候,都是索引范围扫描。
   这时候,如果等值查询条件为A = '999',索引范围扫描花费的成本要远远大于全表扫描的成本,
   那么我们想要的情况是:当等值查询条件为A = '999'时候走全表扫描,其他条件如A='1'时候走索引,
   直方图正是为了解决这个问题的。我们可以在TAB的A列上建立直方图,那么优化器就会选择我们期望的最低成本
   的路径执行。
   
3. 语法,
   begin
             dbms_stats.gather_ table _stats(
              ownname          => '',
              tabname          =>'',
              estimate_percent => dbms_stats.auto_sample_size,
              method_opt       => 'for all columns size auto',
              cascade          =>true,
              degree           => 7
           );
end;

其中degree指定了并行度视主机的CPU个数而定,estimate_percent指定了采样比率,此处使用了auto目的是让oracle来决定采样收集的比率,绘制直方图时会根据采样的数据分析结果来绘制,当然也可以人为指定采样比率。如:estimate_percent=>20指定采样比率为20%,cascade=>true指定收集相关表的索引的统计信息,该参数默认为false,因此使用dbms_stats收集统计信息时抹人事部收集表的索引信息的。

method_opt: size 1不搜集,size 2~255会搜集,size auto自动判断,size skewonly只要数据倾斜则搜集

示例如下(转自itpub)

4. 问题是:如果使用位图索引,是否可以解决同样的问题呢?

ORACLE的直方图的一些试验


直方图有两种类别,等频直方图与等高直方图。
默认的,如果一个倾斜列上的唯一值超过了254个,那么ORACLE会对此列建立等高直方图,否则建立等频直方图。
通过如下方式,建立表TAB,更新字段B,让列B产生倾斜。并在B列上创建索引。
SQL> spool d:\hist.txt
SQL> create table tab (a number, b number);

表已创建。

SQL>
SQL> begin
  2         for i in 1..10000 loop
  3           insert into tab values (i, i);
  4         end loop;
  5         commit;
  6       end;
  7       /

PL/SQL 过程已成功完成。

SQL> update tab set b=5 where b between 6 and 9995;

已更新9990行。
SQL> commit;

提交完成。

SQL> create index ix_tab_b on tab(b);

索引已创建。


然后分析表,强制使列B不产生直方图。
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                TABNAME    => 'TAB',
                                CASCADE    => TRUE,
                                METHOD_OPT => 'FOR  COLUMNS B SIZE 1 ');
END;
查看视图USER_TAB_HISTOGRAMS,列B上只有最大值,最小值两条记录分别对应端点号(endpoint_number)0和1,这种显示说明列B没有直方图信息。
SQL>SELECT table_name,column_name,endpoint_number,endpoint_value FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TAB' ;

TABLE_NAME                     COLUMN_NAME                              ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB                            B                                                      0              1
TAB                            B                                                      1          10000

在没有直方图的情况下,在B列上进行等值查询的时候,都是索引范围扫描。

SQL> select * from tab where b=1;


执行计划
----------------------------------------------------------
Plan hash value: 439197569

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |  1000 |  6000 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |  1000 |  6000 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |  1000 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


SQL> select * from tab where b=5;

已选择9991行。


执行计划
----------------------------------------------------------
Plan hash value: 439197569

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |  1000 |  6000 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |  1000 |  6000 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |  1000 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


收集直方图信息。看看是什么效果。由于列B唯一值的个数没有超过254因此产生的是等频直方图。
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                TABNAME    => 'TAB',
                                CASCADE    => TRUE,
                                METHOD_OPT => 'FOR ALL COLUMNS  SIZE AUTO ');
END;

在B=1时候采用索引扫描,而B=5时候,已经采用全表扫描了,说明直方图起了作用。
SQL> select * from tab where b=1;


执行计划
----------------------------------------------------------
Plan hash value: 439197569

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |     1 |     6 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

SQL> select * from tab where b=5;

已选择9991行。


执行计划
----------------------------------------------------------
Plan hash value: 1995730731

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9991 | 59946 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB  |  9991 | 59946 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------



查看此时的直方图信息:
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB';
TABLE_NAME                     COLUMN_NAME                              ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB                            B                                                      1              1
TAB                            B                                                      2              2
TAB                            B                                                      3              3
TAB                            B                                                      4              4
TAB                            B                                                   9995              5
TAB                            B                                                   9996           9996
TAB                            B                                                   9997           9997
TAB                            B                                                   9998           9998
TAB                            B                                                   9999           9999
TAB                            B                                                  10000          10000

其中EDNPOINT_NUMBER是累计值。EDNPOINT_VALUE是列的值。可以看出这种等频直方图统计的 列的信息是非常精确的。它为每一个列值分配了一个桶。从执行计划的ROWS部分也可以看出ORACLE计算出来的cardinality是9991,和实 际的情况完全吻合。
如果想知道每一个列值对应的数量是多少,需要做一下简单的减法运算:
假如想知道列值等于5的个数,那么可以通过:
9995-4=9991得到。这就是ENDPOINT_NUMBER累计值的含义。


在看看等高直方图的情况。
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                TABNAME    => 'TAB',
                                CASCADE    => TRUE,
                                METHOD_OPT => 'FOR  COLUMNS B SIZE 8 ');
END;

由于列B有10个唯一值,通过上面的size 8可以强制ORACLE使用等高直方图。
查看直方图信息.
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB';

TABLE_NAME                     COLUMN_NAME                              ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB                            B                                                      0              1
TAB                            B                                                      7              5
TAB                            B                                                      8          10000

从查询结果惊奇的发现只有三个桶0 7 8,原来ORACLE会自动省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相邻的桶的值。
省去了桶(EDNPOINT_NUMBER)为1 2 3 4 5 6 ,EDNPOINT_VALUE为5的六条内容。


说明:在等高直方图中,EDNPOINT_NUMBER代表桶号,这一点与等频直方图不同。

再看等高直方图下的执行计划:

SQL> select * from tab where b=5;
已选择9991行。

执行计划
----------------------------------------------------------
Plan hash value: 1995730731
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9982 | 59892 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB  |  9982 | 59892 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------


有没有发现什么?

执行计划的ROWS部分,ORACLE计算出来的cardinality不是特别精确的。9991才是精确值。而等频直方图可以精确到9991,因此可以说等频直方图比等高直方图稳定,精确。
可是现实很多时候,列的唯一值是超过254的。只能使用等高直方图了。

ORACLE的直方图的

一.何谓直方图: 直方图是一种统计学上的工具,并非管理对象的某个方面的质量情况进行管理,通常情况下它会表现为一种几何图形表,这个图形表是根据从实际环境中所收集来的被管理对象某个方面的质量分布情况的数...
  • tchic
  • tchic
  • 2016年09月04日 18:56
  • 1170

oracle直方图

直方图 当某列数据分布不均衡,为了让CBO能生成最佳的执行计划,我们可能需要对表收集直方图,直方图最大的桶数(Bucket)是254。 收集直方图是一个很耗时的过程,如无必要,千万别去收集直方图。 ...
  • gdmzlhj1
  • gdmzlhj1
  • 2014年10月24日 15:53
  • 967

Oracle11g 多列收集统计信息--直方图

最近发现有一条SQL要执行几百秒,是执行计划走错了,为什么走错呢?来看一下: SELECT *   FROM (SELECT *           FROM (SELECT INNER_TABLE....
  • guogang83
  • guogang83
  • 2017年01月06日 10:39
  • 731

如何看懂oracle 直方图

转自:http://czmmiao.iteye.com/blog/1484298 直方图概述: 在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少,...
  • changyanmanman
  • changyanmanman
  • 2013年02月16日 03:01
  • 1992

Oracle直方图的详细解析

Oracle直方图解析 一、    何谓直方图: 直方图是一种统计学上的工具,并非Oracle专有。通常用于对被管理对象的某个方面的质量情况进行管理,通常情况下它会表现为一种几何图形表,这个图形...
  • javacoffe
  • javacoffe
  • 2010年05月11日 13:04
  • 23338

Oracle收集直方图信息

直方图在列数据分布不均匀时非常有用,查询优化器需要直方图信息才能做出正确的估算。有频度直方图与等高直方图两种。本篇依然使用上一篇的测试表。 一、频度直方图 频度直方图使用的不是频度,而是使用累积频度。...
  • panjc_job
  • panjc_job
  • 2013年12月05日 22:14
  • 1296

opencv学习笔记9 直方图均衡化并绘制直方图

我第一次做绘制直方图的时候走了不少弯路,现将我学习直方图均衡化并将直方图绘制出来的学习心得发表出来,希望能帮到需要此知识的同学。 此博文主要讲解进行直方图均衡化需要用到的函数以及如何在一幅图像上将直方...
  • tercel_zhang
  • tercel_zhang
  • 2015年01月05日 15:16
  • 1329

Oracle执行计划——直方图的使用

1. 建立一张倾斜表 object_type只有两个值, 一个500行左右, 剩下50000行左右. 2. 直方图的桶数量为1的情况 当Object_type=INDEX时, 明显执行...
  • huang_xw
  • huang_xw
  • 2013年10月13日 22:47
  • 2678

Oracle直方图详解

当系统中的某些表存在高度不均匀的数据分布时,使用柱状图能够产生更好的选择性评估,从而产生更加优化的执行计划。柱状图提供一种有效和简捷的方法来呈现数据的分布情况。下面通过一个具体的例子解释柱状图的使用。...
  • randyamor
  • randyamor
  • 2008年12月21日 17:09
  • 6585

oracle学习笔记汇总

一,oracle表命名规则 a):必须以字母开头 b):长度不能超过30个字符 c):不用室友oracle保留字 d):只能使用A-Z a-z 0-9 $ #等字符 一.Oracle数据库中...
  • chunqiuwei
  • chunqiuwei
  • 2012年08月04日 00:11
  • 2637
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE直方图学习笔记
举报原因:
原因补充:

(最多只允许输入30个字)