ITPub博客

首页 > Linux操作系统 > Linux操作系统 > CBO与动态统计量采样

CBO与动态统计量采样

原创 Linux操作系统 作者:realkid4 时间:2011-03-11 00:13:06 0 删除 编辑

 

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

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

| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time |

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

|   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

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

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |

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

|   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

(篇幅原因,省略

SUBOBJECT_NAME                103 0.00970873      49940

SECONDARY                       2        0.5          0

 

13 rows selected

 

 

最后,我们简单说说收集统计量的方法问题。从上面的实验可以看出,Oracle进入CBO时代后,对统计量的依存度很高。那么,保证统计量的方法是什么呢?就需要定期的进行全库对象的统计量收集。这个方面,Oracle提供了“自动统计量收集”的功能。

 

10g开始,Oracle默认(11g中允许选择)会添加一条自动作业项目。在每天晚上10点开始进行数据统计量收集的工作。这样就利用业务处理量相对较少的时段进行自动的统计量收集。

 

当然,这样也给一些7×24小时的系统带来一些困扰。本来想利用夜间进行一些报表作业或者处理,结果发现比白天还慢。这样可能就需要去除掉这个功能了,这方面网络上介绍很多,本文就不累述了。

 

 

如果没有采用自动收集策略,就需要DBA进行脚本方式的收集工作。

 

 

无论是何种方式,越精准、越有代表性的统计量,就越可能生成高效的执行计划,提高整体的性能。通常,因为统计量的异常,是引起SQL执行性能的重要原因。

 

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7690304