目前，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

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

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

| 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

這就是出現了執行計劃與實際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

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

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

| 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方法獲得到那個隨機字串是什麼含義呢？我們下面繼續進行探討。

Oracle 11g的多列統計（Multi Column）值（上）

相關文章