ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用11g多列统计量提高多列group by操作估算值

使用11g多列统计量提高多列group by操作估算值

Linux操作系统 作者:bitifi 时间:2015-11-07 09:05:06 0 删除 编辑

 

CBO时代,执行计划决策过程完全取决于各个备选执行计划的成本预估算值。提高统计量精准度、优化CBO对于每个执行步骤的成本估算值是我们进行SQL优化的一个重要工作方向。

 

1、从多列group by估算row source值谈起

 

Oracle是如何评估group by操作的,我们在前文《How CBO estimate the row source for group operation》(http://space.itpub.net/17203031/viewspace-751065)已经进行过比较详细的研究。

 

在前文中,我们发现,如果统计量存在的时候,单列group by的评估值就是数据列的distinct值。而多列group by的情况下,多列笛卡尔乘积代表了操作的结果极限情况。同时,Oracle考虑到了列相关性(Column Correlation)的问题,对笛卡尔积估算结果进行一定因子比例的收缩。

 

但是,无论如何评估,group by多列的情况下,都不能够实现精确的评估。

 

那么,我们有什么方法来解决这个问题呢?笔者猜测11g的“多列统计量”(Multi-Column Statistic)可以解决问题。本篇就进行这个尝试。

 

2、实验环境和对比研究

 

我们使用Oracle 11g进行试验,并且创建数据表T

 

 

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

 

 

SQL> create table t as select * from dba_objects;

Table created

 

 

使用dbms_stats包收集统计量。

 

 

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

PL/SQL procedure successfully completed

 

SQL>  select column_name, num_distinct from dba_tab_columns where wner='SCOTT' and table_name='T';

 

COLUMN_NAME                    NUM_DISTINCT

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

OWNER                                    32

OBJECT_NAME                           43924

SUBOBJECT_NAME                          125

OBJECT_ID                             72719

DATA_OBJECT_ID                         7868

OBJECT_TYPE                              44

CREATED                                1087

(篇幅原因,省略部分……

 

15 rows selected

 

 

统计量收集在column层面准确估算出列的distinct值。下面我们看执行计划情况。

 

 

SQL> explain plan for select owner, object_type, count(*) from t group by owner,object_type;

 

Explained

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 47235625

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

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

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

|   0 | SELECT STATEMENT   |      |   996 | 14940 |   268   (2)| 00:00:04 |

|   1 |  HASH GROUP BY     |      |   996 | 14940 |   268   (2)| 00:00:04 |

|   2 |   TABLE ACCESS FULL| T    | 72718 |  1065K|   266   (1)| 00:00:04 |

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

 

9 rows selected

 

--经验公式:

分析:32×44/1.41421=995.61

 

 

 

但是语句真实执行的结果为:

 

 

SQL> set autotrace traceonly;

SQL> select /*+ gather_plan_statistics*/

  2   owner, object_type, count(*)

  3    from t

  4   group by owner, object_type;

 

已选择266行。

 

SQL> select sql_id from v$sqlarea where sql_text like 'select /*+ gather_plan_statistics*/%';

 

SQL_ID

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

aa21h3vcy4ghw

 

SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'aa21h3vcy4ghw',format => 'allstats last'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  aa21h3vcy4ghw, child number 0

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

select /*+ gather_plan_statistics*/  owner, object_type, count(*)

from t  group by owner, object_type

Plan hash value: 47235625

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

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buff

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

|   0 | SELECT STATEMENT   |      |      1 |        |    266 |00:00:00.05 |    1

|   1 |  HASH GROUP BY     |      |      1 |    996 |    266 |00:00:00.05 |    1

|   2 |   TABLE ACCESS FULL| T    |      1 |  72718 |  72718 |00:00:00.12 |    1

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

 

15 rows selected

 

 

差异存在比较大。下面尝试使用多列统计量。

 

3、多列统计量设置

 

Oracle 11g推出的多列统计量,就是应对多列相关性而导致的估算值不准的情况。在实现原理方面,多列统计量本质上就是创建一个虚拟信息统计列。

 

 

SQL> var vc_res varchar2(100);

SQL> exec :vc_res := dbms_stats.create_extended_stats ( user, 'T', '(owner,object_type)');

 

PL/SQL procedure successfully completed

vc_res

---------

SYS_STUXJ8K0YTS_5QD1O0PEA514IY

 

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

 

PL/SQL procedure successfully completed

 

 

我们可以发现,在统计量中存在一个新的计量列。

 

 

SQL> select column_name, num_distinct from dba_tab_col_statistics where wner='SCOTT' and table_name='T';

 

COLUMN_NAME                    NUM_DISTINCT

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

(篇幅原因,省略部分……

NAMESPACE                                21

EDITION_NAME                              0

SYS_STUXJ8K0YTS_5QD1O0PEA514IY          266

 

16 rows selected

 

 

 

SQL重新收集执行计划。

 

 

 

SQL> explain plan for select owner, object_type, count(*) from t group by owner, object_type;

 

Explained

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 47235625

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

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

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

|   0 | SELECT STATEMENT   |      |   266 |  3990 |   275   (2)| 00:00:04 |

|   1 |  HASH GROUP BY     |      |   266 |  3990 |   275   (2)| 00:00:04 |

|   2 |   TABLE ACCESS FULL| T    | 72719 |  1065K|   273   (1)| 00:00:04 |

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

 

9 rows selected

 

 

比对执行结果:

 

 

SQL> select sql_id from v$sqlarea where sql_text like 'select /*+ gather_plan_statistics*/%';

 

SQL_ID

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

aa21h3vcy4ghw

 

SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'aa21h3vcy4ghw',format => 'allstats last'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  aa21h3vcy4ghw, child number 0

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

select /*+ gather_plan_statistics*/  owner, object_type, count(*)

from t  group by owner, object_type

Plan hash value: 47235625

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

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buff

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

|   0 | SELECT STATEMENT   |      |      1 |        |    266 |00:00:00.05 |    1

|   1 |  HASH GROUP BY     |      |      1 |    266 |    266 |00:00:00.05 |    1

|   2 |   TABLE ACCESS FULL| T    |      1 |  72719 |  72719 |00:00:00.10 |    1

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

 

15 rows selected

 

 

完全匹配成功。

 

4、结论

 

Oracle多列统计量是应对列相关性的重要优化手段。默认情况下,Oracle对数据列的收集不考虑相关性的。所以在多条件处理的时候,执行计划步骤中会存在不准的情况。

 

11g中,Oracle推出了新特性多列统计量,解决了由于多列相关性引起的估算不准确的问题。在本篇中,我们也证明了多列统计量在评估group by操作过程中的使用场景。

 

 

另一方面,多列统计量意味着更多的辅助统计量信息需要保存在数据字典中。所以在使用的时候,也要关注这部分数据的膨胀问题。

 

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

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

注册时间:2015-09-21

  • 博文量
    211
  • 访问量
    275959