Oracle 11g的多列统计（Multi Column）值（上）

 

目前，CBO（Cost-Based Optimizer）是Oracle默认使用的查询优化器Query Optimizer模式。在CBO中，SQL执行计划的生成，是以一种寻找成本（Cost）最优为目标导向的执行计划探索过程。所谓成本（Cost）就是将CPU和IO消耗整合起来的量化指标，每一个执行计划的成本就是经过优化器内部公式估算出的数字值。

 

与RBO（Rule-Based Optimizer）不同，CBO的灵活性建立在对数据统计量的强依赖关系上。CBO Query Optimizer工作的原料就是数据表、索引等对象统计量信息。在绝大部分情况下，CBO是可以帮助我们寻找到最优的执行计划的。但是，在一些特殊的场合下，CBO在估算方面存在一些问题，可能导致一些问题。本篇主要介绍Oracle中多列统计量估算偏差问题。

 

1、环境准备

 

我们在Oracle 11g中进行试验。

 

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0    Production

 

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

 

 

创建数据表T，并且按照常规方法收集统计量信息。

 

 

SQL> create table t (id number, name varchar2(100));

Table created

 

SQL> select * from t;

        ID NAME

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

         1 TT

         2 MT

         3 FT

         1 MM

         1 TT

 

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

PL/SQL procedure successfully completed

 

 

2、Multi-Columns估算偏差问题展现

 

此时，我们需要获取到id=1并且name=’TT’的记录。我们首先生成执行计划。

 

 

SQL> explain plan for select * from t where id=1 and name='TT';

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

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

|*  1 |  TABLE ACCESS FULL| T    |     1 |     6 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("NAME"='TT' AND "ID"=1)

 

13 rows selected

 

 

注意，从估算结果看，该SQL执行返回的Row Source数量为1。也就是说，Oracle优化器认为该SQL返回的结果数量为1。但是实际上数量是多少呢？

 

--实际运行结果

SQL> select * from t where id=1 and name='TT';

 

        ID NAME

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

         1 TT

         1 TT

 

 

这就是出现了执行计划与实际Row Source差异的现象。产生这种问题的原因，在于Oracle中默认只对单列进行统计量收集，而SQL中出现多列情况组合，就会发生问题。

 

具体来说，对数据表T，Oracle只会分别对列id和name进行统计量收集。在进行组合估算的时候，只会进行组合方式“剔除”结果集合。如果出现数据表T这种id=1和name=’TT’较多且符合的情况，估算出执行计划的row source就会有偏差出现。

 

Row source在执行计划成本公式中地位是很重要的，直接与进行逻辑物理读（Logical/Physical Get）数据块的个数相关，进而影响到Cost计算。如果发生Multi Columns估算问题，执行计划成本估算的cost就会相对较小。

 

公允的说，在大多数情况下，由于Multi Column统计量引起的执行计划错误问题是很少发生的。真正出现的场景是一些特殊的数据分布结构和查询方式上。如果深究这些问题，都能或多或少的存在数据库设计不合理或者应用开发不适当的问题。

 

在过去的Oracle版本中，Multi Column问题是不能处理的。在Oracle 11g中，我们可以使用Oracle拓展统计量（也称为Column Group）来解决这个问题。

 

3、Multi-Column和Column Group

 

Oracle 11g对统计量提供了多列统计量的拓展功能。也就是说，我们可以指定对多列数据制定一个列组（Column Group），针对这个列组进行统计量收集过程。

 

在11g的dbms_stats包中，添加了函数create_extended_stats，用于收集拓展统计量。

 

 

function create_extended_stats(

      ownname    varchar2,

      tabname    varchar2,

      extension  varchar2)

return varchar2;

 

 

具体使用上，步骤如下：

 

根据create_extended_stats方法的提示，要求compatible参数选取在11以上。

 

 

SQL> show parameter compatible

 

NAME                                 TYPE        VALUE

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

compatible                           string      11.2.0.0.0

 

 

创建id和name共同构成的column group。

 

 

SQL> var vc_res varchar2(100);

SQL> exec :vc_res := dbms_stats.create_extended_stats('SCOTT','T','(id,name)');

 

PL/SQL procedure successfully completed

vc_res

---------

SYS_STUIA0V924QODN5R5SCAKM60G#

 

 

调用方法后，反馈回一个内部的编号。之后，我们重新收集统计量信息。

 

--可以让Oracle给Column Group收集直方图信息；

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for columns (id,name) size skewonly');

 

PL/SQL procedure successfully completed

 

 

SQL> explain plan for select * from t where id=1 and name='TT';

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      |     2 |    12 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     2 |    12 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("NAME"='TT' AND "ID"=1)

 

13 rows selected

 

 

注意，此时Oracle执行计划正确的获得了结果集合row source信息。多列统计量生效。

 

那么，Oracle在内部是怎么进行组织和管理的呢？以及调用create_extend_stats方法获得到那个随机字符串是什么含义呢？我们下面继续进行探讨。

 

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

转载于:http://blog.itpub.net/17203031/viewspace-710894/