聊聊CBO的連線排列（Join Permutation）

realkid4發表於2013-08-18

Oracle CBO（Cost Based Optimizer）是目前Oracle採用的主流最佳化器種類。

 

簡單的說，SQL語句的執行計劃，主要是透過資料庫SQL最佳化器根據統計資訊（System Statistical Data和Object Statistical Data），使用預定義公式進行計算，將最後的計算結果作為執行計劃路徑的成本值Cost。

 

一個SQL語句，理論上有很多種執行計劃可以選擇（所謂“條條大路通羅馬”），最佳化器Optimizer最終會選擇使用哪個執行計劃一定是依據各條路徑的計算成本Cost值。注意：Optimizer依據的是計算出的成本值，而不是真實的執行成本。所以，當統計量不能及時反映資料真實情況的時候，SQL執行計劃往往就會是低效的。

 

1、從連線Join順序說起

 

關係型資料庫的兩個基本要素就是資料表和連線。我們將資料依據正規化原則拆成若干的資料表。資料資訊表達和組織都是透過表間連線完成。所以，Join是關係型資料庫的一個重要概念。

 

在很多的SQL最佳化和資料庫最佳化過程中，Join的最佳化佔到了很大比例。不同的Join方法、不同的Join順序，對相同SQL語句執行結果過程有很大的影響。

 

一個SQL語句中，可能會有很多表的連線Join。各種連線Type種類，如內連線、外連線和全連線，都是將若干表連線在一起。但是在執行計劃和實際執行過程中，都是兩兩資料集合的連線。下面是一個例子中看出：

 

 

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語句。

 

 

SQL> explain plan for select * from scott.bonus a, scott.dept b, scott.emp c where a.ename=c.ename and b.deptno=c.deptno;

Explained

 

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1315453310

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

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

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

|   0 | SELECT STATEMENT            |         |     1 |    97 |     7  (15)| 00

|   1 |  NESTED LOOPS               |         |       |       |            |

|   2 |   NESTED LOOPS              |         |     1 |    97 |     7  (15)| 00

|*  3 |    HASH JOIN                |         |     1 |    77 |     6  (17)| 00

|   4 |     TABLE ACCESS FULL       | BONUS   |     1 |    39 |     2   (0)| 00

|   5 |     TABLE ACCESS FULL       | EMP     |    14 |   532 |     3   (0)| 00

|*  6 |    INDEX UNIQUE SCAN        | PK_DEPT |     1 |       |     0   (0)| 00

|   7 |   TABLE ACCESS BY INDEX ROWID| DEPT   |     1 |    20 |     1   (0)| 00

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

Predicate Information (identified by operation id):

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

   3 - access("A"."ENAME"="C"."ENAME")

   6 - access("B"."DEPTNO"="C"."DEPTNO")

 

20 rows selected

 

 

我們的SQL語句，是直接的三個資料表連線。而在執行過程中，我們看到Oracle是先將Bonus與Emp連線（Hash Join），之後將結果集合和PK_DEPT資料集合進行連線（Nested Loop）。Oracle的連線，無論資料表連線數量是多少、Type是什麼，Oracle都是老老實實的兩兩連線。

 

那麼，我們回到兩個問題：n個資料表，Oracle怎麼知道連線順序是最合適的？CBO在進行判斷的時候採用什麼樣的準則？這就是Oracle的Join Permutation機制。

 

2、Join Permutation

 

Join順序判斷，對於RBO和過去的時候，是比較簡單的。我們依然記得有“按照from之後從右到左”這樣的經驗傳言。但是在CBO的時候，這個過程是很複雜的。

 

如果我們希望對n個資料表進行連線，那麼Oracle CBO面對的試算規模理論上是n!條路徑。這個量級是非常大的。如果一個SQL需要連線資料表的數量很多，那麼生成執行計劃的過程也是消耗時間不可控。這個是任何Oracle CBO無法容忍的。

 

事實上，實際中發生全域性路徑解析的情況是很少的，並不是所有的n!條路徑都是回去計算。Oracle解決這個問題是透過兩個手段：

 

ü  路徑成本非完全計算：Oracle CBO在計算路徑成本值的時候，一般都有一定的傾向性。也就是說，在確定檢索路徑的時候，就將可能的最優計劃在前面進行估算。當進行後續路徑的檢索時，如果計算的過程中發現，成本已經高於之前路徑的成本值，這條路徑就不會計算下去，而是直接放棄；

ü  最大試算Permutation閾值：Oracle並不是直接會將所有的n！全部進行測試。而是系統級別存在一個計算閾值，如果計算次數超過閾值，試算過程終值。採用當前最優執行計劃（連線順序）作為返回的結果。

 

在很多資料中，我們可以發現對Permutation的計算閾值為80000，但是我們從11gR2版本中，看到這個數字值是2000。

 

注意：有Permutation的存在，會造成CBO工作的一個情況，就是Oracle CBO返回的連線順序可能不是最優的。進而，執行計劃也不是最優的，是一個相對最優的執行計劃。這應該是Oracle進行的一種折中操作。

 

3、測算過程

 

我們在11gR2中，可以看到Oracle的Permutation隱含引數。

 

 

_optimizer_max_permutations         = 2000

 

 

存在Permutation，讓我們可以存在一個連線表數量n的對應關係。Permutation計算的覆蓋比例公式：PERM/n!*100

 

n

n！全路徑數量

當Permutation為80000

當Permutation為2000

1

1

8000000

200000

2

2

4000000

100000

3

6

1333333.333

33333.33333

4

24

333333.3333

8333.333333

5

120

66666.66667

1666.666667

6

720

11111.11111

277.7777778

7

5040

1587.301587

39.68253968

8

40320

198.4126984

4.96031746

9

362880

22.04585538

0.551146384

10

3628800

2.204585538

0.055114638

11

39916800

0.200416867

0.005010422

12

479001600

0.016701406

0.000417535

13

6227020800

0.001284724

3.21181E-05

 

從計算結果看，如果閾值次數是80000，那麼在資料表數量大於8之後，查詢路徑是不完全的。在資料表為9個之後，覆蓋範圍為22%。如果閾值次數為2000，那麼查詢的完全資料表為6。超過6個資料表之後，覆蓋率降低。

 

4、應對方法

 

那麼，我們應該怎麼樣進行處理，包括Oracle官方的推薦建議，有下面幾個建議：

 

ü  使用Hint來指導連線順序。我們如果發現執行計劃的不穩定問題，可以透過order的hint來控制資料表的連線。當然，這樣的情況需要對執行計劃有一個清晰的分析理解；

ü  調整隱含引數_optimizer_max_permutations，如果系統整體連線比較多，可以嘗試調整Permutation試探計算的引數；

 

下面是筆者幾個拙見：

 

ü  合理化正規化設計。我們說：連線Join是SQL效能的一個危險點。Join過多，SQL過於複雜是我們SQL出現問題的一個潛在問題點。如果從根本上看，減少Join次數是避免問題的一個好方法。三正規化為基礎，適度冗餘是一個比較好的方法。連線過多、資料量過大很多時候也沒有太多好方法；

ü  避免From後面直接所有的資料表列。雖然使用From後面把資料表都列出來是一個比較容易書寫的方法，也帶來問題是Order順序的問題。筆者一系列的經驗是使用Left Join等方法一層層的新增連線。這樣的寫法可以“誘使”Oracle形成更好的執行計劃；

 

5、結論

 

CBO的連線排列是最佳化器的一種重要功能。從Permutation次數變化情況看，Oracle也在不斷的調整這個資料值。相信這種是一種效能的折中。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-768748/，如需轉載，請註明出處，否則將追究法律責任。

相關文章