学习直方图知识

http://jolly10.itpub.net/post/7268/485264
http://jolly10.itpub.net/post/7268/485260


oracle统计信息和直方图的理解
以前一直对统计信息的理解就是对行的数据分布的，提供改CBO来选择高效的执行计划。这段时间看了不少资料，对统计有了一个更清晰的认识

统计信息：

1，表中的统计信息

2，索引列的统计信息

3，一般列的统计信息

表的统计信息：1，表行数，使用的块数，空的块数，块的使用率，行迁移和链接的数量，pctfree，pctused的数据，行的平均大小

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt select  NUM_ROWS,  -- 表中的记录数
BLOCKS,  -- 表中数据所占的数据块数
EMPTY_BLOCKS,  -- 表中的空块数
AVG_SPACE,  -- 数据块中平均的使用空间
CHAIN_CNT,  -- 表中行连接和行迁移的数量
AVG_ROW_LEN  -- 每条记录的平均长度
from  user_tables

索引列的统计信息 1，索引的深度（B-Tree的级别），索引叶级的块数量，集群因子（clustering_factor), 唯一值的个数

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt select  BLEVEL,  -- 索引的层数
LEAF_BLOCKS,  -- 叶子结点的个数
DISTINCT_KEYS,  -- 唯一值的个数
AVG_LEAF_BLOCKS_PER_KEY,  -- 每个KEY的平均叶块个数 
AVG_DATA_BLOCKS_PER_KEY,  -- 每个KEY的平均数据块个数
CLUSTERING_FACTOR  -- 群集因子
from  user_indexes

列的统计信息 1,唯一的值个数，列最大小值，密度（选择率），数据分布（直方图信息），NUll值个数

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtselect NUM_DISTINCT, --唯一值的个数
LOW_VALUE, --列上的最小值
HIGH_VALUE, --列上的最大值
DENSITY, --选择率因子（密度）
NUM_NULLS, --空值的个数
NUM_BUCKETS, --直方图的BUCKET个数
HISTOGRAM --直方图的类型
from user_tab_columns

直方图：是统计信息一种，对数据分布的统计，目的是为了更精确的得到选择率和基数，CBO才能估计出最优的执行计划。

上一篇我们知道了统计信息在：表，列，索引上能够获取的统计信息，但如果有一个sql：

select * from table where x=1;（在x列上有索引）

那Oracle如何选择是表扫描，索引查询数据，通过对列列的统计信息，但没有直方图，CBO优化器不知道x=1的数据有多少？无法提供数据参考。有两钟情况：

table表中：1，x=1的数据比如就一条 CBO优化器：应该索引查找

2，x=1的数据有很多条，CBO优化器：这时应该表扫描

如果索引列没有直方图，索引上有最大，最小值，总行数，那就不能知道x=1在表中大概有多少行。如果只是有索引上统计信息，就无法获取大概的数据分布

CBO也就得不到正确的结果。

对于数据分布均匀的列，直方图没意义，对应列中数据分布比较倾斜的列（不均匀），直方图就非常有用。CBO就可以估计大概的数据分布，计算查询的选择率和基数也更精确。

所以直方图有两种：1，高度均衡直方图 2，频率直方图

高度均衡直方图： 数据分布不均匀 ，由于列中数据很多，这时数据比较密集，不利于分析和评估，这时直方图需要均衡化。

频率直方图：就是数据分布很均匀，

得到的直方图：信息的准确性就由两个数值决定，一个是bucket的个数，一个NUM_DISTINCT的个数。

一般来说，bucket的数据越多，关于列数据分布的信息就越正确，但统计直方图的花费的时间和资源就多，oracle中bucket的最大254个，默认是75个。而SQL Server默认是200个。

在oracle中要删除直方图信息就是设置bucket的数据为1，如下：

Analyze table 表 compute statistics for table for columns id size 1；

exec dbms_stats.gather_table_stats('用户', '表',cascade=>false, method_opt=>'for columns 列 size 1');

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

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

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。

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

转载于:http://blog.itpub.net/22295535/viewspace-712816/