ITPub博客

首页 > 数据库 > Oracle > 直方图

直方图

原创 Oracle 作者:buptdream 时间:2014-08-06 15:55:12 0 删除 编辑
DBMS_STATS 包对段表的分析有三个层次:
表自身的分析
这包括表中的行数,数据块数,行长等信息。
列的分析
包括列值的重复数,列上的空值,数据在列上的分布情况。
索引的分析
包括索引叶块的数量,索引的深度,索引的聚合因子等。

下面讨论的直方图,单指的是第二项的最后一种,数据在列上的分布情况。
当Oracle 做直方图分析时,会将要分析的列上的数据分成很多数量相同的部分,每一
部分称作一个bucke,这样CBO 就可以非常容易的知道这个列上的数值的分布情况,
这种数据的分布将作为一个非常重要的因素纳入到执行成本的计算当中。
对于数据分布非常倾斜的表,做直方图分析是非常有用的。
直方图对于CBO 有时候非常重要,特别是对于有字段数据非常倾斜的表,做直方图分
析尤为重要,比如下面的例子:
Last login: Fri Aug 31 22:09:41 2012 from 10.8.0.249
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 9月 1 08:34:11 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn change/change
Connected.
SQL> create table change(a number,b number);

Table created.

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

PL/SQL procedure successfully completed.

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

9990 rows updated.

SQL> commit;

Commit complete.

SQL> 
SQL>  select b,count(*) from change group by b;

         B   COUNT(*)
---------- ----------
         1          1
      9997          1
      9999          1
     10000          1
         2          1
         5       9991
         4          1
      9998          1
         3          1
      9996          1

10 rows selected.
这样表中有10000条数据,对于b列,有10个不同的值,等于5的值有9991个,此时无论是选择b=1或者b=5的数据都必须要进行全表扫描,因为表上没有索引,也即是没有别的访问路径。

SQL> set autotrace traceonly
SQL> 
SQL> select * from change where b=1;


Execution Plan
----------------------------------------------------------
Plan hash value: 3349315267

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    26 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CHANGE |     1 |    26 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("B"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
         50  consistent gets
          0  physical reads
          0  redo size
        593  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace traceonly
SQL>  create index i_change_b on change(b);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'CHANGE',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace off
SQL>              

SQL> col TABLE_NAME for a20
SQL> col COLUMN_NAME for a20
SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where  table_name='CHANGE';

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
CHANGE               B                                  1              1
CHANGE               B                                  2              2
CHANGE               B                                  3              3
CHANGE               B                                  4              4
CHANGE               B                               9995              5
CHANGE               B                               9996           9996
CHANGE               B                               9997           9997
CHANGE               B                               9998           9998
CHANGE               B                               9999           9999
CHANGE               B                              10000          10000
CHANGE               A                                  0              1

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
CHANGE               A                                  1          10000

12 rows selected.

直方图中的ENDPOINT_VALUE表示列值,ENDPOINT_NUMBER表示累积的行数。比如ENDPOINT_VALUE=2,ENDPOINT_NUMBER=2,因为ENDPOINT_NUMBER是个累积值,
实际上2的 ENDPOINT_NUMBER应该是2减去上一个值的ENDPOINT_NUMBER,也即是2-1=1。同理,5的 ENDPOINT_NUMBER=9995-4=9991。

SQL> set autotrace traceonly explain;
SQL> select * from change where b=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1994370661

--------------------------------------------------------------------------------
----------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

--------------------------------------------------------------------------------
----------

|   0 | SELECT STATEMENT            |            |     1 |     7 |     2   (0)|
00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| CHANGE     |     1 |     7 |     2   (0)|
00:00:01 |

|*  2 |   INDEX RANGE SCAN          | I_CHANGE_B |     1 |       |     1   (0)|
00:00:01 |

--------------------------------------------------------------------------------
----------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"=1)

SQL> set linesize 1000
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 1994370661

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"=1)
从这里我们可以看到,CBO 根据直方图的信息知道id=1 的记录只有一行,所以它选择了索
引,这个选择非常正确:
SQL> 
SQL> select * from change where b=5;

Execution Plan
----------------------------------------------------------
Plan hash value: 3349315267

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("B"=5)
从这里我们可以看到,CBO 根据直方图的信息知道id=5 的记录有9991行,所以它选择了全表扫描,这个选择非常正确。通过对列做直方图分析,CBO 可以非常完美的制定出一个最有的执行计
划来。
下面让我们将直方图信息删除,但保留表和索引的分析信息:
SQL> exec dbms_stats.delete_column_stats(user,'change','b');

PL/SQL procedure successfully completed.

SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where table_name='CHANGE';

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
CHANGE               A                                  0              1
CHANGE               A                                  1          10000-------------------B列上的信息已经被删除

SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='CHANGE';

  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZE
---------- ----------- ---------- ------------
     10000           7         20 01-9月 -12

SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='CHANGE';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZE
---------- ----------- ------------- ------------
         1          20            10 01-9月 -12

索引和表的信息仍然存在,并且索引中甚至可以找到重复的数值只有10 个,但是CBO 却无
法得到这两个重复的数值的分布情况,所以依然没有办法选出一个正确的执行计划:
SQL> set autotrace traceonly
SQL> select * from change where b=1;


Execution Plan
----------------------------------------------------------
Plan hash value: 1994370661

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |   100 |   700 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CHANGE     |   100 |   700 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_CHANGE_B |    40 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        597  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * from change where b=5;

9991 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1994370661

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |   100 |   700 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CHANGE     |   100 |   700 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_CHANGE_B |    40 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"=5)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        689  consistent gets
          0  physical reads
          0  redo size
     212190  bytes sent via SQL*Net to client
       7850  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9991  rows processed
CBO 在b=1 时,估算出返回的结果是100行,比较全部表的记录数10000 行(这个
信息可以从表的分析数据中得到user_tables),CBO 认为选择索引是合适的
同样,在ib=5 时,CBO 依然估算出的返回值是100 条,在这种情况下,CBO 没有
办法得到数据的具体分布情况,所以作出的错误的判断。




SQL> analyze table change compute statistics for columns b size 8;

Table analyzed.

SQL> set autotrace off
SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where  table_name='CHANGE';

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


SQL> analyze table change compute statistics for columns b size 7;

Table analyzed.

SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where  table_name='CHANGE';

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
CHANGE               B                                  0              1
CHANGE               B                                  6              5
CHANGE               B                                  7          10000
CHANGE               A                                  0              1
CHANGE               A                                  1          10000

ENDPOINT_NUMBER是实际的bucket编号,ENDPOINT_VALUE是根据列值决定的该bucket的endpoint值。上面的输出中,bucket 0存放着b列的低值,为了节省空间没有显示出1-6号的bucket。但是我们能够理解,bucket[1-7]里存放着的endpoint=5,而 bucket8里存放endpoint=10000。因此,实际上bucket0里包含了1-5之间的所有值,而bucket8里包含了5-10000之间的所有值,在本例中也就是9996-10000这5个数值。
综上所述,假如数据是均衡的,没有必要使用直方图。如果使用唯一值数量来创建直方图,Oracle为每个值创建一个bucket;但是假如实际的生产系统中,不能够为每一个唯一值分配一个bucket时,Oracle采用合适的算法尽可能将值平均分布到每个bucket中,剩余的值放入到最后的bucket。
对于数据分布均匀的列,直方图没意义,对应列中数据分布比较倾斜的列(不均匀),直方图就非常有用。CBO就可以估计大概的数据分布,计算查询的选择率和基数也更精确。
最后提示一点:如果要删除直方图统计信息,那么只需指定size 1即可:

SQL> exec dbms_stats.gather_table_stats(user,'CHANGE',cascade =>false,method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where  table_name='CHANGE';

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
CHANGE               A                                  0              1
CHANGE               B                                  0              1
CHANGE               A                                  1          10000
CHANGE               B                                  1          10000

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10701850/viewspace-1246586/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-04

  • 博文量
    25
  • 访问量
    335697