cbo心得(选择率,基数,直方图)(by oratea)

一，概述

关于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 wner = ‘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，这种浪费并不是仅仅出现一次，在以后的每次查询的时候会存在的，直到你开始收集统计信息为止。

 

2， 存在表和索引上的统计信息，没有直方图

这是最一般的情况，定期对表和索引进行分析，但是并不分析直方图信息。大部分时候，这种统计信息收集策略都会很好的工作。我们这的大部分指得是，列上数据分布很平均的情况下。为了更好的演示，我需要创建一个存在倾斜列的表。

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;

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

转载于:http://blog.itpub.net/13650998/viewspace-220336/