在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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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操作過程中的使用場景。

另一方面，多列統計量意味著更多的輔助統計量資訊需要儲存在資料字典中。所以在使用的時候，也要關注這部分資料的膨脹問題。

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

相關文章