ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 学习直方图知识

学习直方图知识

原创 Linux操作系统 作者:mafeiyan 时间:2011-12-06 16:57:35 0 删除 编辑
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/

-->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/

-->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/

-->select 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/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2009-11-30

  • 博文量
    18
  • 访问量
    19703