Oracle直方图详解

 http://blog.csdn.net/randyamor/archive/2008/12/21/3574181.asp

 

 

当系统中的某些表存在高度不均匀的数据分布时，使用柱状图能够产生更好的选择性评估，从而产生更加优化的执行计划。柱状图提供一种有效和简捷的方法来呈现数据的分布情况。

下面通过一个具体的例子解释柱状图的使用。

SQL> create table tab (a number, b number);

Table created.

SQL> begin
       for i in 1..10000 loop
         insert into tab values (i, i);
       end loop;
       commit;
     end;
     /

PL/SQL procedure successfully completed.

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

9990 rows updated.

SQL> commit;

Commit complete.

这样在tab表中，b列有10个不同的值，其中等于的值有9991个。在创建索引之前，无论是查询b=3或者是b=5，都只能是走全表扫描（FULL TABLE SCAN），因为没有别的可以使用的访问路径。

下面我们在b列上创建一个索引。

SQL> create index ix_tab_b on tab(b);

Index created.

SQL> select index_name, table_name, column_name, column_position, column_length
     from user_ind_columns
     where table_name='TAB';

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME          COLUMN_POSITION COLUMN_LENGTH
------------------------------ ------------------------------ -------------------- --------------- -------------
IX_TAB_B                       TAB                            B                                  1            22

现在我们分别来看看下面的查询。

SQL> select * from tab where b=3;

1 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 439197569

------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |
|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |
------------------------------------------------

Statistics
----------------------------------------------------------
        178  recursive calls
          0  db block gets
         30  consistent gets
          5  physical reads
        116  redo size
        462  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from tab where b=5;

9991 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 439197569

------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |
|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |
------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1370  consistent gets
         16  physical reads
          0  redo size
     206729  bytes sent via SQL*Net to client
       7711  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9991  rows processed

可以看出这里走的都是基于RBO的INDEX RANGE SCAN。

接下来，我们使用计算统计对表进行分析。

SQL> analyze table tab compute statistics;

Table analyzed.

SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len 
  2  from dba_tables
  3  where table_name = 'TAB';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
     10000         20            4       2080          0          10

SQL> select num_distinct, low_value, high_value, density, num_buckets, last_analyzed, sample_size
     from dba_tab_columns
     where table_name = 'TAB';

NUM_DISTINCT LOW_VALUE            HIGH_VALUE              DENSITY NUM_BUCKETS LAST_ANAL SAMPLE_SIZE
------------ -------------------- -------------------- ---------- ----------- --------- -----------
       10000 C102                 C302                      .0001           1 21-DEC-08       10000
          10 C102                 C302                         .1           1 21-DEC-08       10000

SQL> select table_name, column_name, endpoint_number, endpoint_value  
     from dba_tab_histograms
     where table_name = 'TAB';

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

再来执行上面的两个查询，观察其执行计划，发现两个查询仍然走的都是INDEX RANGE SCAN，只不过这时的执行计划是基于CBO的。

现在我们创建tab表b列的柱状图统计信息，使得优化器能够知道该列每个值的具体分布情况。

SQL> analyze table tab compute statistics for columns b size 10;

Table analyzed.

SQL> select table_name, column_name, endpoint_number, endpoint_value  
     from dba_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

直方图中的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> select * from tab where b=3;

1 rows selected.

Execution Plan
----------------------------------------------------------
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 |
----------------------------------------------------------------------------------------

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

SQL> select * from tab where b=5;

9991 rows selected.

Execution Plan
----------------------------------------------------------
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 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        689  consistent gets
          0  physical reads
          0  redo size
     174757  bytes sent via SQL*Net to client
       7711  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9991  rows processed

这时可以看出，不同值的分布导致了Oracle优化器选择了不同执行计划。对于b=5的查询来说，全表扫描的一致性读比之前的索引范围扫描要降低很多。可以看出此时的全表扫描比之索引范围扫描更加的合理，优化器正是根据直方图的统计信息做出的正确的判断。

上述的例子描述了一种理想的状况，因为我们为每一个不同的值创建了bucket。在实际的生产系统中，一张表可能包含很多的唯一值，我们不可能为每一个唯一值创建bucket，这样开销将是巨大的。

下面的例子描述了唯一值大于buckets的情况。

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

Table analyzed.

SQL> select table_name, column_name, endpoint_number, endpoint_value  
     from dba_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

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。