在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博客 ” ，链接：https://blog.itpub.net/30496894/viewspace-1825216/ ，如需转载，请注明出处，否则将追究法律责任。
