CBO（基於成本的最佳化器）是目前Oracle預設使用的SQL最佳化器方法。最佳化器的作用在於對輸入執行的SQL產生適合的執行路徑。

傳統的RBO（基於規則的最佳化器）是依據一系列預定義規則進行SQL執行計劃生成，會去考慮如SQL語句的結構、where條件和資料表索引情況等。這樣的做雖然簡單，但是不會考慮資料實際情況，很多時候生成高效執行計劃的機率不高。CBO更多的是依據實際資料列的情況，透過公式進行成本試算，這種方式依據的是實際資料表資料列的分佈情況和統計資訊。從實際情況上看，我們透過CBO獲取的執行路徑，更加科學和有效。

本質上看，如果RBO就是一系列規則的集合和匹配機制。那麼CBO就是一系列引數和統計量的公式，不多試算各種執行路徑，之後返回一條Cost最小。那麼，CBO依據的就是資料統計量和預設值的一系列控制引數。

一系列的控制引數，如optimizer_index_cost_adj等，大都都是Oracle的預設設定，通常不需要調整。

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

SQL> show parameter adj

NAME TYPE VALUE

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

optimizer_index_cost_adj integer 100

另一部分就是對資料物件的統計量資訊。藉助抽樣等方法對資料表物件進行分析，得出統計量，是CBO計算執行成本的另一方面重要因素。

收集統計量，我們可以使用Oracle的dbms_stats包進行統計量收集。

SQL> create table t as select * from dba_objects;

Table created

SQL> create index ind_t_cmp on t(owner,object_name);

Index created

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

PL/SQL procedure successfully completed

SQL> set autotrace traceonly;

SQL> select * from t where wner='SCOTT' and object_name='EMP';

執行計劃

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

Plan hash value: 1097962935

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

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

| 0 | SELECT STATEMENT | | 1 | 93 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 93 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_T_CMP | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SCOTT' AND "OBJECT_NAME"='EMP')

統計資訊

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

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

1202 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

我們透過執行計劃，可以看出藉助統計量，可以獲取到相對較好的CBO執行計劃。那麼，統計量檢視資訊是存在的，例如：

SQL> col column_name for a20;

SQL> select column_name, NUM_DISTINCT, DENSITY,NUM_NULLS from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS

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

OWNER 20 0.05 0

OBJECT_NAME 30086 3.32380509 0

SUBOBJECT_NAME 103 0.00970873 49938

OBJECT_ID 50347 1.98621566 0

DATA_OBJECT_ID 4050 0.00024691 46252

OBJECT_TYPE 29 0.03448275 0

CREATED 1354 0.00073855 0

LAST_DDL_TIME 1366 0.00073206 0

TIMESTAMP 1357 0.00073691 0

STATUS 2 0.5 0

TEMPORARY 2 0.5 0

GENERATED 2 0.5 0

SECONDARY 1 1 0

13 rows selected

透過檢視，我們可以看到資料表T對應資料列的一些基本統計資訊（篇幅原因，其他一些統計量省略），包括密度、分佈情況和空值等資訊。此外還有對執行計劃影響重要的直方圖資訊。這些引數都會在CBO的成本計算公式中進入計算範疇。

一個疑問就出現了。我們現在是使用了顯示的dbms_stats方法，收集統計量。那麼如果我們沒有收集統計量，建立資料後直接進行查詢，Oracle CBO依據的統計資料怎麼獲取呢？我們進行下列的實驗。

SQL> create table t2 as select * from dba_objects;

Table created

SQL> create index ind_t2_cmp on t2(owner,object_name);

Index created

SQL> select column_name, NUM_DISTINCT, DENSITY,NUM_NULLS from dba_tab_col_statistics where wner='SYS' and table_name='T2';

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS

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

我們沒有對資料表T2進行任何顯示的統計量收集。統計量檢視dba_tab_col_statistics中沒有對應的任何統計資訊。此時，我們進行查詢。

SQL> select * from t2 where wner='SCOTT' and object_name='EMP';

執行計劃

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

Plan hash value: 3661878964

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

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

| 0 | SELECT STATEMENT | | 1 | 177 | 2 (0)|00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 177 | 2 (0)|00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_T2_CMP | 1 | | 1 (0)|00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SCOTT' AND "OBJECT_NAME"='EMP')

Note

-----

- dynamic sampling used for this statement

統計資訊

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

254 recursive calls

0 db block gets

（篇幅原因，略…）

1 rows processed

結果，我們發現，雖然沒有顯示的進行統計量收集。Oracle執行計劃還是生成了，CBO還為我們找出了相對較好的一條路徑。唯一需要注意的就是標註：“- dynamic sampling used for this statement”。

原來，Oracle在這裡採用了一種“動態取樣”技術才應對這種情況。詳細過程猜想是這樣：在沒有進行統計量收集的時候，Oracle執行對一個資料表的執行計劃生成。一旦發現沒有對應的統計量資訊，就會開始一個動態取樣收集的過程，用相對較低的取樣率收集資料表統計量。最後根據這些收集結果生成執行計劃。

當然，Oracle認為這樣的情況是一種特殊情況，並不推薦。原因主要在於動態收集要兼顧執行效率（畢竟是佔用SQL執行的時間），所以抽樣率和其他準確度不會很高。對一些資料量很大的資料表，取樣率高意味著準確，但同時也意味著時間長。所以，Oracle對這種動態取樣結果是不推薦的。

那麼，是不是對於Oracle資料表，我們依賴動態取樣的結果就好了呢？答案是否定的。首先，動態取樣的結果抽樣率低，不準確。這個已經提過，一些很大的資料表，為了提高效率還是保證一定的抽樣比例較好。其次，動態取樣的結果是不記錄如資料字典的。也就是動態的結果生成執行計劃即可，下次執行的時候還會進行動態收集。這樣得不償失。

//沒有計入資料字典任何資訊

SQL> select column_name, NUM_DISTINCT, DENSITY,NUM_NULLS from dba_tab_col_statistics where wner='SYS' and table_name='T2';

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS

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

//顯示進行收集！

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

PL/SQL procedure successfully completed

SQL> select column_name, NUM_DISTINCT, DENSITY,NUM_NULLS from dba_tab_col_statistics where wner='SYS' and table_name='T2';

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS

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

OWNER 24 9.94954676 0

（篇幅原因，省略…）

SUBOBJECT_NAME 103 0.00970873 49940

SECONDARY 2 0.5 0

13 rows selected

最後，我們簡單說說收集統計量的方法問題。從上面的實驗可以看出，Oracle進入CBO時代後，對統計量的依存度很高。那麼，保證統計量的方法是什麼呢？就需要定期的進行全庫物件的統計量收集。這個方面，Oracle提供了“自動統計量收集”的功能。

從10g開始，Oracle預設（11g中允許選擇）會新增一條自動作業專案。在每天晚上10點開始進行資料統計量收集的工作。這樣就利用業務處理量相對較少的時段進行自動的統計量收集。

當然，這樣也給一些7×24小時的系統帶來一些困擾。本來想利用夜間進行一些報表作業或者處理，結果發現比白天還慢。這樣可能就需要去除掉這個功能了，這方面網路上介紹很多，本文就不累述了。

如果沒有采用自動收集策略，就需要DBA進行指令碼方式的收集工作。

無論是何種方式，越精準、越有代表性的統計量，就越可能生成高效的執行計劃，提高整體的效能。通常，因為統計量的異常，是引起SQL執行效能的重要原因。

CBO與動態統計量取樣

相關文章