CBO与动态统计量采样-CSDN博客

CBO（基于成本的优化器）是目前Oracle默认使用的SQL优化器方法。优化器的作用在于对输入执行的SQL产生适合的执行路径。

传统的RBO（基于规则的优化器）是依据一系列预定义规则进行SQL执行计划生成，会去考虑如SQL语句的结构、where条件和数据表索引情况等。这样的做虽然简单，但是不会考虑数据实际情况，很多时候生成高效执行计划的概率不高。CBO更多的是依据实际数据列的情况，通过公式进行成本试算，这种方式依据的是实际数据表数据列的分布情况和统计信息。从实际情况上看，我们通过CBO获取的执行路径，更加科学和有效。

本质上看，如果RBO就是一系列规则的集合和匹配机制。那么CBO就是一系列参数和统计量的公式，不多试算各种执行路径，之后返回一条Cost最小。那么，CBO依据的就是数据统计量和预设值的一系列控制参数。

一系列的控制参数，如optimizer_index_cost_adj等，大都都是Oracle的默认设置，通常不需要调整。

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

SQL> show parameter adj

NAME TYPE VALUE

------------------------------------ -------

optimizer_index_cost_adj integer 100

另一部分就是对数据对象的统计量信息。借助抽样等方法对数据表对象进行分析，得出统计量，是CBO计算执行成本的另一方面重要因素。

收集统计量，我们可以使用Oracle的dbms_stats包进行统计量收集。

SQL> create table t as select * from dba_objects;

Table created

SQL> create index ind_t_cmp on t(owner,object_name);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> set autotrace traceonly;

SQL> select * from t where wner='SCOTT' and object_name='EMP';

执行计划

----------------------------------------------------------

Plan hash value: 1097962935

----------------------------------------------------

------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 93 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 93 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_T_CMP | 1 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"='SCOTT' AND "OBJECT_NAME"='EMP')

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

1202 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执行计划。那么，统计量视图信息是存在的，例如：

SQL> col column_name for a20;

SQL> select column_name, NUM_DISTINCT, DENSITY,NUM_NULLS from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS

-------------------- ------------ ---------- ----------

OWNER 20 0.05 0

OBJECT_NAME 30086 3.32380509 0

SUBOBJECT_NAME 103 0.00970873 49938

OBJECT_ID 50347 1.98621566 0

DATA_OBJECT_ID 4050 0.00024691 46252

OBJECT_TYPE 29 0.03448275 0

CREATED 1354 0.00073855 0

LAST_DDL_TIME 1366 0.00073206 0

TIMESTAMP 1357 0.00073691 0

STATUS 2 0.5 0

TEMPORARY 2 0.5 0

GENERATED 2 0.5 0

SECONDARY 1 1 0

13 rows selected

通过视图，我们可以看到数据表T对应数据列的一些基本统计信息（篇幅原因，其他一些统计量省略），包括密度、分布情况和空值等信息。此外还有对执行计划影响重要的直方图信息。这些参数都会在CBO的成本计算公式中进入计算范畴。

一个疑问就出现了。我们现在是使用了显示的dbms_stats方法，收集统计量。那么如果我们没有收集统计量，创建数据后直接进行查询，Oracle CBO依据的统计数据怎么获取呢？我们进行下列的实验。

SQL> create table t2 as select * from dba_objects;

Table created

SQL> create index ind_t2_cmp on t2(owner,object_name);

Index created

SQL> select column_name, NUM_DISTINCT, DENSITY,NUM_NULLS from dba_tab_col_statistics where wner='SYS' and table_name='T2';

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS

-------------------- ------------ ---------- ----------

我们没有对数据表T2进行任何显示的统计量收集。统计量视图dba_tab_col_statistics中没有对应的任何统计信息。此时，我们进行查询。

SQL> select * from t2 where wner='SCOTT' and object_name='EMP';

执行计划

----------------------------------------------------------

Plan hash value: 3661878964

-------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 177 | 2 (0)|00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 177 | 2 (0)|00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_T2_CMP | 1 | | 1 (0)|00:00:01 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"='SCOTT' AND "OBJECT_NAME"='EMP')

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

254 recursive calls

0 db block gets

（篇幅原因，略…）

1 rows processed

结果，我们发现，虽然没有显示的进行统计量收集。Oracle执行计划还是生成了，CBO还为我们找出了相对较好的一条路径。唯一需要注意的就是标注：“- dynamic sampling used for this statement”。

原来，Oracle在这里采用了一种“动态采样”技术才应对这种情况。详细过程猜想是这样：在没有进行统计量收集的时候，Oracle执行对一个数据表的执行计划生成。一旦发现没有对应的统计量信息，就会开始一个动态采样收集的过程，用相对较低的采样率收集数据表统计量。最后根据这些收集结果生成执行计划。

当然，Oracle认为这样的情况是一种特殊情况，并不推荐。原因主要在于动态收集要兼顾执行效率（毕竟是占用SQL执行的时间），所以抽样率和其他准确度不会很高。对一些数据量很大的数据表，采样率高意味着准确，但同时也意味着时间长。所以，Oracle对这种动态采样结果是不推荐的。

那么，是不是对于Oracle数据表，我们依赖动态采样的结果就好了呢？答案是否定的。首先，动态采样的结果抽样率低，不准确。这个已经提过，一些很大的数据表，为了提高效率还是保证一定的抽样比例较好。其次，动态采样的结果是不记录如数据字典的。也就是动态的结果生成执行计划即可，下次执行的时候还会进行动态收集。这样得不偿失。

//没有计入数据字典任何信息

SQL> select column_name, NUM_DISTINCT, DENSITY,NUM_NULLS from dba_tab_col_statistics where wner='SYS' and table_name='T2';

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS

-------------------- ------------ ---------- ----------

//显示进行收集！

SQL> exec dbms_stats.gather_table_stats(user,'T2',cascade => true);

PL/SQL procedure successfully completed

SQL> select column_name, NUM_DISTINCT, DENSITY,NUM_NULLS from dba_tab_col_statistics where wner='SYS' and table_name='T2';

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS

-------------------- ------------ ---------- ----------

OWNER 24 9.94954676 0

（篇幅原因，省略…）