ITPub博客

首页 > 数据库 > Oracle > oracle11g multi column statistics

oracle11g multi column statistics

Oracle 作者:地铁上小虫 时间:2015-07-30 20:48:58 0 删除 编辑
来源:http://blog.itpub.net/17203031/viewspace-710894/

目前,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方法获得到那个随机字符串是什么含义呢?我们下面继续进行探讨。

4Multi-Column本质论

Oracle中,是怎么组织多列组的信息呢?我们首先从列统计量入手分析。

SQL> select column_name, num_distinct,SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM  from user_tab_col_statistics where table_name='T'; 

COLUMN_NAM NUM_DISTINCT SAMPLE_SIZE AVG_COL_LEN HISTOGRAM

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

ID                    3           5           3 FREQUENCY

NAME                  4           5           3 FREQUENCY

SYS_STUIA0            4           5          12 FREQUENCY

V924QODN5R                                     

5SCAKM60G#    

在列中存在一个特殊列的统计信息,这里的列名同我们生成拓展统计量时候的那个字符串。 

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也提供了一个视图user_stat_extensions来查看生成的拓展统计量。 

SQL> select * from user_stat_extensions where extension_name = 'SYS_STUIA0V924QODN5R5SCAKM60G#'; 

TABLE_NAME EXTENSION_NAME                 EXTENSION            CREATOR DROPPABLE

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

T          SYS_STUIA0V924QODN5R5SCAKM60G# ("ID","NAME")        USER    YES 

那么,Oracle是不是同函数索引采用相同的内部策略,构建一个虚拟列进行管理呢?我们只有去到col$基表中进行检查。 

SQL> select object_id from dba_objects where object_name='T' and wner='SCOTT'; 

 OBJECT_ID

----------

75482

SQL>  select col#, name,DEFAULT$  from col$ where obj#=75482; 

      COL# NAME       DEFAULT$

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

         1 ID        

         2 NAME      

         0 SYS_STUIA0 SYS_OP_COMBINED_HASH("ID","NAM

           V924QODN5R E")

           5SCAKM60G# 

果然,此处显示的内容是:Oracle使用类似虚拟列的方法,构建了一个列。之后对这个列进行统计量收集。 

5Column Group的失效场景 

在笔者的实验中,发现并不是建立了column group之后,所有的统计量估算都是正确的。起码当条件中存在非等号之后,拓展统计量估值是可能错误的。 

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

PL/SQL procedure successfully completed 

--结果集合为3

SQL> select * from emp where job='MANAGER' and sal>2000; 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20

 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30

 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10 

查看执行计划中的估算值。

SQL> explain plan for select * from emp where job='MANAGER' and sal>2000; 

Explained 

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 3956160932

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("JOB"='MANAGER' AND "SAL">2000)

 

13 rows selected 

此时,我们收集一下拓展统计量。 


SQL> var vc_res varchar2(1000);

SQL> exec :vc_res := dbms_stats.create_extended_stats('SCOTT','EMP','(job,sal)');

PL/SQL procedure successfully completed

vc_res

---------

SYS_STU73TUM4UV1A$7U9OVY05$MH6

SQL> select * from user_stat_extensions; 

TABLE_NAME EXTENSION_NAME                 EXTENSION            CREATOR DROPPABLE

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

EMP        SYS_STU73TUM4UV1A$7U9OVY05$MH6 ("JOB","SAL")        USER    YES 

SQL> exec dbms_stats.gather_table_stats(user,'EMP',method_opt => 'for columns (job,sal) size skewonly'); 

PL/SQL procedure successfully completed 

SQL> select column_name, num_distinct,SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM  from user_tab_col_statistics where table_name='EMP'; 

COLUMN_NAM NUM_DISTINCT SAMPLE_SIZE AVG_COL_LEN HISTOGRAM

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

(篇幅原因,有省略。。。。。。)

DEPTNO                3          14           3 FREQUENCY

SYS_STU73T           12          14          12 FREQUENCY

UM4UV1A$7U                                     

9OVY05$MH6                                


此时,执行计划并没有改变。 

SQL> explain plan for select * from emp where job='MANAGER' and sal>2000;

Explained 

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 3956160932

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("JOB"='MANAGER' AND "SAL">2000)

 

13 rows selected 

这说明拓展统计量在非等号情况下,是存在一些问题的。 

6、其他注意问题和结论 

此外,在使用column group上,我们还需要注意下面的问题: 

ü        拓展列中不允许出现虚拟列(Virtual Column);

ü        不能对sys schema下的数据表列建立column group

ü        不能对聚簇表(Cluster Table)、索引组织表(Index Organized Table)、临时表(Temporary Table)和外部表(External Table)上的列创建Column Group

ü        一个数据表中创建的拓展列数目不能超过20和数据表10%非虚拟列的数目;

ü        一个拓展列组中包括了列数目位于2-32的范围内;

ü        一个列只能出现在一个拓展列组中;

ü        列组中不能包括表达式;

ü        compatible参数必须在11.0.0.0以上; 

最后,个人感觉在实际中,特别是开发环境下很少会使用到column group的功能。因为解决执行计划问题的手段很多,column group不是最优的方法。而在运维环境中,常常会遇到书写很糟糕的SQL和设计。此时运维人员通常没有机会修改SQL源代码。所以,column group作为一种运维手段,是可以进行尝试的。


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

请登录后发表评论 登录
全部评论

注册时间:2014-05-12

  • 博文量
    49
  • 访问量
    155787