本文探讨了Oracle数据库的成本基优化器(CBO)如何利用统计信息来选择最优的执行计划，重点介绍了选择率、基数及直方图的概念，并通过实例展示了直方图在数据分布不均匀时对执行计划选择的影响。
一，概述
关于CBO优化器你上网可以搜到一大堆的介绍。用一句话来解释CBO：CBO是一种根据执行代价来选择执行计划的机制。对于CBO来说，最重要的就是各种不同的执行计划的代价的计算。和RBO比较起来，CBO确实要聪明很多。CBO会根据对象上的统计信息来进行执行计划的代价评估，选择出较为高效的。而不是象RBO那样根据一些既有的规则来选择执行计划，愣头青一样的。
既然是根据对象上的统计信息来计算代价，那么统计信息对于CBO的重要性就不用多说了。所以在CBO的数据库上，统计信息一定要定期收集，保证不光要有统计信息，而且也要尽量保证统计信息的准确性。不准确的统计信息可能比没有统计信息还要糟糕。
最近一直在看关于CBO的东西，有了一些简单的体会，拿出来分享一下，对我自己来说也是一个再提高的过程。
下面主要就是对CBO中几个最基本的概念的说明。它们主要是：选择率，基数和直方图。
二，基础知识
选择率：目标结果集占全部数据的百分比
基数：目标结果集的大小
直方图：列上的统计信息，主要用于倾斜度很高的列上
选择率和基数的含义差不多太多，都是为了描述目标结果集的大小的。选择率和基数是CBO的基础，在选择SQL执行计划的主要参考的就是这两个结果。比如说：
Create table sunwg as select * from dba_objects;
Create index ind_sunwg_1 on sunwg(object_id);
Create index ind_sunwg_2 on sunwg(owner);
对于查询select * from sunwg where object_id = 12345来说，是选择索引扫描还是选择全表扫描呢？答案很简单，哪个效率更好就选择哪个。CBO会根据表和列上的统计信息得知这个查询大概会返回1行记录，在5W多条记录中选择一条，那么基数就是1，使用索引的效果会更好，这样大概几个IO就可以搞定。
对于查询select * from sunwg where owner = ‘SYS’来说，这个查询会返回几W条记录，那么自然而然就会想到全表扫描会更加的快。
所以说选择率和基数对于执行计划的选择起着相当重要的影响。
直方图也是列上统计信息的一种。我们常用的统计信息主要包括表上的统计信息，索引上的统计信息，在有就是列上的统计信息了。
查看表上的统计信息
SQL> select NUM_ROWS, --表中的记录数
BLOCKS, --表中数据所占的数据块数
EMPTY_BLOCKS, --表中的空块数
AVG_SPACE, --数据块中平均的使用空间
CHAIN_CNT, --表中行连接和行迁移的数量
AVG_ROW_LEN --每条记录的平均长度
from user_tables
where table_name = 'SUNWG';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- -------------- -------- ----------
5050 12 3 3450 6
从上面的结果可以看到表SUNWG一共有5050条记录，占用了15个数据块，其中有12块是存储数据的，另外3个数据块是空的，平均每个块使用3450B的空间，平均每条记录的长度是6B。
查看索引的统计也是类似的
select BLEVEL, --索引的层数
LEAF_BLOCKS, --叶子结点的个数
DISTINCT_KEYS, --唯一值的个数
AVG_LEAF_BLOCKS_PER_KEY, --每个KEY的平均叶块个数
AVG_DATA_BLOCKS_PER_KEY, --每个KEY的平均数据块个数
CLUSTERING_FACTOR --群集因子
from user_indexes
where index_name = ‘IND_SUNWG_1;
查看列上的统计信息
select NUM_DISTINCT, --唯一值的个数
LOW_VALUE, --列上的最小值
HIGH_VALUE, --列上的最大值
DENSITY, --选择率因子（密度）
NUM_NULLS, --空值的个数
NUM_BUCKETS, --直方图的BUCKET个数
HISTOGRAM --直方图的类型
from user_tab_columns
where table_name ='SUNWG'
and column_name = 'OBJECT_ID';
查询列上的直方图信息
select ENDPOINT_NUMBER,
ENDPOINT_VALUE
from user_tab_histograms
where table_name ='SUNWG'
and column_name = 'OBJECT_ID';
上面的这几个查询就是我们工作中经常会遇到的关于统计信息的查询。为什么要统计表，索引和列的统计信息比较好理解，关于直方图信息可能很多人都是比较模糊，不知道为什么要统计直方图，直方图到底有什么用。下面用个例子来说明：
drop table sunwg purge;
Create table sunwg (id number);
Create index ind_sunwg on sunwg(id);
Insert into sunwg select 1 from dba_objects where rownum < 5000;
Insert into sunwg select rownum from dba_objects where rownum < 5002;
Commit;
对于查询select * from sunwg where id = 1来说，表中一共有1W条记录，而id = 1的记录有5000条，占了50%，在这种情况下，走全表扫描来说是更明智的。对于查询select * from sunwg where id = 500来说，表中一共有1W条记录，而id = 1的记录只有1条，这时候应该走索引扫描更好。这是我们知道数据分布的情况下。
假设现在我们没有收集列ID上的直方图信息，我们仅仅有表上的，索引上的和列上的统计信息。我们能得到的统计信息大概是这样的，表上一共有1W条记录，其中大概有5001个不同的值，ID最小的值是1，ID最大的值是5001。在表中ID的值是平均分布的情况下，我们可以得到选择率大概是1/5001，走索引扫描会快一些。那么对于前面说的id = 1和id = 5000的查询，CBO会选择执行索引的扫描。前面我们已经分析了，对于id = 1来说最好的方式就是全表扫描，此时CBO选择了不合适的执行计划。这也不能怪CBO，因为我们给它的信息并不完整，不足以分析出ID上的这些差异。直方图就是为了解决这样的问题。
直方图对于那些列上值分布不平均，列上信息明显倾斜的列十分的有用。有了列上的直方图信息后，CBO就可以知道大概的数据分布，就可以作出相对来说更加正确的选择。至于CBO是如果使用直方图信息的，我们在下面会详细的介绍。
三，正文
1， 没有任何统计信息（包括表，索引，直方图等）
在ORACLE 10g中，RBO已经彻底的被抛弃了，取而代之的是CBO。我们知道CBO的基础就是统计信息，那么在10g中如果没有收集统计信息，CBO是怎么工作的呢？我们看下面的这个例子。
Create table sunwg (Id number);
Create index ind_sunwg on sunwg(id);
Begin
For I in 1..100 loop
For j in 1..i loop
Insert into sunwg values (i);
End loop;
End loop;
Commit;
End;
这样我们就创建了一张有5050条记录的测试表。
例1， 不存在统计信息
SQL> select * from sunwg where id = 10;
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 3109917279
---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 130 | 1 (0)| 00:00:01 |* 1 | INDEX RANGE SCAN | IND_SUNWG | 10 | 130 | 1 (0)| 00:00:01 ---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=10)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
25 consistent gets
0 physical reads
124 redo size
478 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
我们可以注意到Note中的说明dynamic sampling used for this statement，这表示在执行上面的SQL的时候，系统对表和索引进行了动态的取样。也就是说在执行这个SQL之前系统首先动态的收集表上和索引上的统计信息，然后利用这些统计信息通过CBO来找到合适的执行计划。这个统计的信息获得是需要很大的系统开销的，所以我们一定要把统计信息的收集放到一个重要的地位。那么这个收集后的统计信息会数据库保存起来供其他的SQL使用么？
如果这个统计信息被数据库保存起来的话，那么其他关于SUNWG表的查询也会利用到这些统计信息；反过来说，如果其他SQL没有使用统计信息的话，说明这些统计信息并没有被保存起来。
SQL> select * from sunwg where id = 50;
已选择50行。
执行计划
----------------------------------------------------------
Plan hash value: 3109917279
-----------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes|Cost (%CPU)| Time |
-----------------------------------------------------------------------| 0 | SELECT STATEMENT | | 50 | 650 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_SUNWG| 50 | 650 | 1 (0)| 00:00:01 -----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=50)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
1059 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed
我们换了一个查询，可以看到这次仍然进行了动态的取样操作，由此可以证明这些统计信息并没有被ORACLE保存起来，对于没有统计信息的表每一个相关的查询都会进行动态的统计信息的收集。我也查询了大部分和统计信息相关的系统表，并没有发现这些统计信息，看来这些统计信息是和特定SQL关联的，并不会为其他的SQL使用。没有统计信息的后果是多么的可怕啊，本来3个逻辑读可以搞定的事情，却用了26个逻辑读才完成。
那么这个25个逻辑读到底读取了哪些内容呢，下面做个大概的测试。
SQL> analyze table sunwg compute statistics for table for all indexes;
表已分析。
SQL> select blocks,empty_blocks from user_tables;
BLOCKS EMPTY_BLOCKS
---------- ------------
13 3
SQL> select leaf_blocks from user_indexes;
LEAF_BLOCKS
-----------
9
13 + 9 + 3 = 25
虽然可能不那么准确，也可以看得出来在进行动态统计信息收集的时候会对表和表上索引进行统计，浪费大量的IO，这种浪费并不是仅仅出现一次，在以后的每次查询的时候会存在的，直到你开始收集统计信息为止。SQL> drop table sunwg purge;
表已删除。
SQL> create table sunwg (id number);
表已创建。
SQL> create index ind_sunwg on sunwg(id);
索引已创建。
SQL> insert into sunwg select 1 from dba_objects where rownum<2001;
已创建2000行。
SQL> insert into sunwg select 1+ rownum from dba_objects where rownum<2001;
已创建2000行。
SQL> commit;
提交完成。
SQL> analyze table sunwg compute statistics for table for all indexes for columns id size 1;
表已分析。
这样我们就有了一个有倾斜列的表SUNWG，在表中id = 1的记录有2000条，占了全部记录的50%。查询下表和索引的统计信息，看看我们都告诉了CBO什么信息。
SQL> select NUM_ROWS,
2 BLOCKS,
3 EMPTY_BLOCKS,
4 AVG_SPACE,
5 CHAIN_CNT,
6 AVG_ROW_LEN
7 from user_tables
8 where table_name = 'SUNWG';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
4000 13 3 4687 0 6
SQL> select BLEVEL,
2 LEAF_BLOCKS,
3 DISTINCT_KEYS,
4 AVG_LEAF_BLOCKS_PER_KEY,
5 AVG_DATA_BLOCKS_PER_KEY,
6 CLUSTERING_FACTOR
7 from user_indexes
8 where index_name = 'IND_SUNWG';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
---------- ----------- ------------- -----------------------
AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
----------------------- -----------------
1 8 2001 1
1 7
SQL> select NUM_DISTINCT,
2 LOW_VALUE,
3 HIGH_VALUE,
4 DENSITY,
5 NUM_NULLS,
6 NUM_BUCKETS,
7 HISTOGRAM
8 from user_tab_columns
9 where table_name ='SUNWG'
10 and column_name = 'ID';
NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS HISTOGRAM
----------- --------- --------- ------ -------- ----------- --------
2001 C102 C21502 .00049975 0 1 NONE
关于LOW_VALUE和HIGH_VALUE要特别说明下，在user_tab_columns里面的这两个值是采用RAW数据类型来存储的。我们想要得到number类型的值需要调用dbms_stats中的过程convert_raw_value。下面是个写好函数raw_to_number。
function raw_to_number (in_raw raw)
return number
as
ft FLOAT(126);
begin
dbms_stats.convert_raw_value(in_raw,ft);
return ft;
end;
SQL> select * from sunwg where id = 50;
执行计划
----------------------------------------------------------
Plan hash value: 3109917279
---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 6 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_SUNWG | 2 | 6 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=50)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
402 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
上面这条SQL，CBO选择的通过索引范围扫描来执行。CBO为什么会做出这样的决定呢？我们看一下计划里面有一列是Rows，这列代表着ORACLE估计的结果集合的大小。前面这个例子中ORACLE估计ID = 50的结果集大概是2条。实际上呢，ID = 50的记录只有一条。先不管这点差异，在总量是4000条记录的表中查询几条记录用索引扫描会更加的高效。因此，CBO选择了索引扫描而不采用全表扫描。
接下来在说这个Rows = 2是怎么产生的。在列的统计信息中一个字段是NUM_DISTINCT，它说明该列不同值的个数。由于不存在直方图信息，ORACLE无法知道列上数据的真实分布情况，所以ORACLE假设数据在列ID上是平均分配的。所以ID = 50的记录数就应该是：
4000 * （1/2001） = 1.99
SQL> select * from sunwg where id = 1;
已选择2000行。
执行计划
----------------------------------------------------------
Plan hash value: 3109917279
---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 6 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_SUNWG | 2 | 6 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
138 consistent gets
0 physical reads
0 redo size
27319 bytes sent via SQL*Net to client
1848 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
上面的这个例子中ORACLE关于结果集的估计是差得比较多了，实际上ID = 1的记录有2000条，可ORACLE的估计值只有2条。差距这么大的就可能导致CBO选择错误的执行计划，用低效的索引扫描来代替更加高效的全表扫描。直方图信息就是用来解决这种问题的，在数据倾斜的情况下给ORACLE更多的信息，使ORACLE可以意识到倾斜的存在。
我们上边仅仅对ID = 1这样的最简单的情况进行了测试，平时常用的谓词还有>,>=,<等等，下表列出了常用的一些谓词的情况
Where 条件 | Oracle估计记录数 | 表中实际记录数 | Oracle估算公式（猜想） |
ID = 1 | 2 | 2000 | 4000 * （1/2001） |
ID > 1 | 3998 | 2000 | 4000 * （2001 – 1）/2001 |
ID >= 1 | 4000 | 4000 | 4000*（2001 – 1/2001）+2 |
ID = 110 | 2 | 0 | 1<= X<= 4000 * （1/2001） |
ID + 1 > 1 | 200 | 4000 | 4000* 5% |
ID + 1 >= 1 | 200 | 4000 | 4000* 5% |
ID + 1 > 1 AND ID + 1 > 1 | 200 | 4000 | 4000* 5% |
ID > 1 AND ID < 50 | 96 | 48 | 4000 *( 5%* 5%) - 2 - 2 |
ID >= 1 AND ID <= 50 | 100 | 2049 | 4000 *( 5%* 5%) |
为了更明显的说明直方图的作用，我修改一下上面的创建的表，进一步的增加列ID的倾斜性。
SQL> drop table sunwg purge;
表已删除。
SQL> create table sunwg (id number);
表已创建。
SQL> create index ind_sunwg on sunwg(id);
索引已创建。
SQL> insert into sunwg select 1 from dba_objects where rownum < 4000;
已创建3999行。
SQL> insert into sunwg select rownum from dba_objects where rownum < 2002;
已创建4001行。
SQL> commit;
在表SUNWG中ID = 1的记录一共有4000条，占了全部记录的66%。
分析表和索引，并且统计直方图信息。
SQL> analyze table sunwg compute statistics for table for all indexes for column
s id size 10;
表已分析。
还是执行前面的那两个SQL看看有了直方图信息之后的效果。
SQL> select * from sunwg where id = 50;
执行计划
----------------------------------------------------------
Plan hash value: 3109917279
---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_SUNWG | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=50)
因为ID = 50的记录在表SUNWG中仅有一条，所以才用索引范围扫描的效率会更好一些。CBO选择了高效的执行方式。
SQL> select * from sunwg where id = 1;
执行计划
----------------------------------------------------------
Plan hash value: 650063079
---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3600 | 10800 | 5 (0)|
|* 1 | INDEX FAST FULL SCAN| IND_SUNWG | 3600 | 10800 | 5 (0)| 00:00:01
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
在ID = 1的时候，CBO选择了执行快速索引全扫描。ORACLE意识到了ID = 1的时候结果集的变化，所以CBO没有采用索引范围扫描，而是选择了更加高效的快速索引全扫描。
结合前面的例子，我们可以知道对于那些严重倾斜的列来说，直方图信息是多么的重要。所以我们在收集统计信息的时候，一定不要忽略了直方图这个重要的因素。下面是存在直方图信息时，其他谓词的情况：
Where 条件 | 来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/82387/viewspace-1021215/，如需转载，请注明出处，否则将追究法律责任。 
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%> <%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%}%>
<%=items[i].items.items[j].createtime%>
回复
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备09055130号-4 北京市公安局海淀分局网监中心备案编号：11010802021510 广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员 |
转载于:http://blog.itpub.net/82387/viewspace-1021215/
扫一扫
到【灌水乐园】发言
