基於成本的優化器 — 一般錯誤概念和問題(轉)

本短文著意於消除一些關於基於成本的優化器（CBO）的錯誤說法，強調一般的錯誤和問題。

　　Background 背景

　　~~~~~~~~~~

　　為了執行任何一個SQL語句，Oracle都要先匯出一個“執行計劃(execution plan)”。它基本上就是Oracle如何檢索出符合給定SQL語句要求的資料的執行計劃。

　　Oracle7和Oracle8 都有兩種可以為SQL語句匯出執行計劃的優化器:

　　- 基於規則的優化器（RBO）

　　繼承自Oracle6，它使用一系列嚴格的規則來決定每個SQL語句的執行計劃。如果你知道這些規則，你可以構造一個SQL查詢使其以指定的方式訪問資料。表的內容對於執行計劃沒有影響。

　　這個優化器已經不再被增強了，所以不能使用很多oracle8的特性。

　　- 基於成本的優化器（CBO）

　　從Oracle7開始引入，該優化器嘗試找到最低成本的訪問資料的方法，為了最大的吞吐量或最快的初始響應時間。計算使用不同的執行計劃的成本，並選擇成本最低的一個。關於表的資料內容的統計被用於確定執行計劃。

　　Fundamental Points 基本點

　　~~~~~~~~~~~~~~~~~~

　　對於每個SQL語句，都有很多可能的執行計劃。

　　“最佳計劃”永遠是“最佳計劃”，無論它如何到達。

　　最佳計劃可以由兩個意思：

　　1 此計劃使用最小的資源來處理此語句涉及到的所有行。 [叫做ALL_ROWS]

　　2 此計劃以最短的時間返回這個語句的第一行 。 [叫做FIRST_ROWS]

　　CBO不理解應用的相關特性，也不能完全理解關聯表之間的複雜關係的影響。僅有有限的資訊可以用來確定最佳計劃。

　　CBO通過計算不同執行方案的估計成本來確定最佳計劃，並選用最低成本的計劃。因為這個關係到相關成本的假設，所選的計劃不一定是真的最好的計劃。這種情況經常被當作BUG報告給oracle 技術支援,因為 CBO沒有為一個指定方案選擇一個最佳的計劃。人們通常可以證實因為給定的輸入統計試有效的並且預設的“成本”被牽扯進來。所選中的計劃被計算成最佳計劃，雖然它不是。無論CBO如何改進提高，總也會有所選的計劃不是最優的這種情況。所以，你必須經常地準備優化語句。

RBO的功能已經不再增強。這就意味著一些執行計劃只對CBO有效。然而，RBO還將在Oracle 8中繼續存在。

　　Before you Continue 在你繼續之前

　　~~~~~~~~~~~~~~~~~~~

　　不建議你在Oracle releases 7.0.X中使用CBO.

　　本文中的資訊適用於Oracle releases 7.1 以上（包括Oracle 8.0）。

　　Base Statistics 基礎統計

　　~~~~~~~~~~~~~~~

　　為了要給CBO最多的資訊（有機會選擇好的執行計劃），你必須對所有將被查詢的表做ANALYZE。

　　帶有ESTIMATE選項的ANALYZE操作對於一些表能夠產生不正確的結果，尤其是那些取樣較小的表。這不是個BUG,而是每個統計取樣方法的特性。如果所選取樣不能代表整個資料集，你就不能期待產生正確的統計。

　　在Oracle 7.1 和7.2 中，列的值被假定為是均勻分佈的。這是在這些版本中的一個重要的限制，完全和精確的統計也不能指出實際資料的分佈情況。這一限制在Oracle release 7.3 以上版本被部分解決了，能夠儲存列值的分佈資訊 - 但是這些額外的資訊可能對某些型別的查詢沒有實際的幫助，請看後面的章節中關於Bind Variables 的注意事項。

　　在考慮使用ANALYZE時，要考慮如下的重要問題：

　　- 對一個帶索引的表的ANALYZE，將分析其相關索引。 （在Oracle 7.3 中可能值分析表而不分析索引。）

　　- 如果你對一個表進行ANALYZE ... ESTIMATE 分析，那麼然後在其相關索引上做ANALYZE COMPUTE分析是很明智的。這樣可以確保被索引欄位的統計是準確的。

　　- 分析索引不用到臨時表空間

　　- 如果分析一個索引而不分析其基表，在這一單一基礎上CBO不會被選中。

　　- 如果你需要使用ESTIMATE- 估計（例如，由於時間的限制），建議你在幾個不同的取樣大小上進行 ANALYZE ... ESTIMATE， 來確定每個物件的理想的取樣大小。總的目標是找到一個能在最短的時間內產生準確的統計的取樣大小。較好的開始點是 10% - 15%。

　　- 進行超過50%的ANALYZE ... ESTIMATE 就會導致/變成ANALYZE ... COMPUTE。

　　- 7.1.6 以前的版本在進行ANALYZE ... ESTIMATE 時，會有ORA 600 錯。

　　Optimizer Goal / Mode 優化目標和模式

採用什麼樣的優化器和其操作方式是由下面的因素決定的：

　　Object Type 物件型別

　　- 某些物件型別是基於規則的優化所不知道的。例如：索引表（IOT）RBO根本不認識，在牽扯IOT的查詢中將自動使用CBO.

　　Parallel Degree > 1 on a table 表上的並行度大於1

　　- 如果查詢中的某個表的並行度大於一，CBO都將被採用而不管提示、OPTIMIZER_MODE或OPTIMIZER_GOAL的值是否為"RULE"。適用於Oracle 7.3 以上。

　　- 在Oracle 8.0.5 和 Oracle 8.1.5 releases 中如果任何索引的並行度超過1，也將採用CBO。僅適用於Oracle 8.0.5和Oracle 8.1.5。

　　Hints 提示

　　- 除了RULE之外的任何提示都會導致使用CBO。HINT不能被任何引數關掉，這一點非常重要。

　　Session level會話級 OPTIMIZER_GOAL

　　- 如果沒有給定以上的條件，優化器的選用由會話級的引數OPTIMIZER_GOAL決定。如果上面的一個條件給定了，OPTIMIZER_GOAL就不起作用了。

　　如果OPTIMIZER_GOAL設為RULE，將採用RBO，而不管任何表的統計。

　　如果OPTIMIZER_GOAL設為CHOOSE，對於只要有一個表被分析過的查詢，都將選用ALL_ROWS 。

　　Init.Ora OPTIMIZER_MODE 引數

　　- 會話級的OPTIMIZER_GOAL引數的預設設定是init.ora檔案中的OPTIMIZER_MODE的值。

　　PL/SQL 塊（包括匿名塊和儲存過程）應使用顯式的提示(hint)來確定實際的優化方法。沒有指定提示、並行的或“CBO-only”的物件的情況下，PL/SQL 塊中的SQL語句採用的優化器，見下：

　　INIT.ORA OPTIMIZER_MODE

　　Mode used in PLSQL

　　RULE

　　RULE

　　CHOOSE

　　ALL_ROWS

　　ALL_ROWS

　　ALL_ROWS

　　FIRST_ROWS

　　ALL_ROWS

　　Summary Optimizer Mode: 優化模式的總結

　　~~~~~~~~~~~~~~~~~~~~~~~

　　對於以上的文章使我們清楚的確定採用何種優化器的一些事情，總結如下：

　　Description

　　Table Statistics

　　Mode Used

Non-RBO Object(Eg:IOT)

　　n/a

　　#1

　　Parallelism > 1

　　n/a

　　#1

　　RULE hint

　　n/a

　　RULE

　　ALL_ROWS hint

　　n/a

　　ALL_ROWS

　　FIRST_ROWS hint

　　n/a

　　FIRST_ROWS

　　*Other Hint

　　n/a

　　#1

　　OPTIMIZER_GOAL=RULE

　　n/a

　　RULE

　　OPTIMIZER_GOAL=ALL_ROWS

　　n/a

　　ALL_ROWS

　　OPTIMIZER_GOAL=FIRST_ROWS

　　n/a

　　FIRST_ROWS

　　OPTIMIZER_GOAL=CHOOSE

　　NO

　　RULE

　　OPTIMIZER_GOAL=CHOOSE

　　YES

　　ALL_ROWS

　　#1 除非OPTIMIZER_GOAL 設定為FIRST_ROWS,都將採用ALL_ROWS 。在PLSQL中，將採用ALL_ROWS。

　　*Other Hint 其他提示的意思是指除了RULE, ALL_ROWS 或FIRST_ROWS之外的提示。

　　General Optimizer Notes 優化器的一般注意事項

　　~~~~~~~~~~~~~~~~~~~~~~

　　在看待優化器問題的時候，應考慮如下幾點：

　　- ALL_ROWS 傾向於全表掃描（full table scans）。

　　- FIRST_ROWS 傾向於索引訪問（ index access）。

　　- CBO預設使用ALL_ROWS計算成本。

　　- 在Oracle 7.3之前，CBO不會為了迎合並行查詢（ Parallel Queries）而調整成本。

　　- 在Oracle 7.3之前，CBO認為欄位的值載最大和最小之間是均勻分佈的；這之後，可以根據請求儲存柱狀圖統計。

　　- 所有等於RBO的情況，以表在FROM子句中從右到左的順序為驅動順序(Driving Order)。

　　CBO根據由收集到的統計資訊而匯出的成本，來確定連線順序(Join Order).

　　如果沒有統計資訊，CBO就將以表在FROM子句中從左到右的順序為驅動順序(Driving Order),正好和RBO相反。

　　- CBO將結合當前表的高水位資訊使用ANALYZE資訊。因此，一個語句的執行計劃是可能因時間的不同而改變的。

- 注意：TRUNCATE重置了表的“高水位”，但是不修改表的統計資訊，而是留下了該表的舊的CBO資訊。

　　- 當執行各種連線時，一些連線組合將被排除以降低確定一個執行計劃所需要的整體時間花費。總之，每個連線順序都要和目前為止最好的一個做比較，顯然部分優化的方案將被排除。

　　Problem SQL Statements 問題SQL語句

　　~~~~~~~~~~~~~~~~~~~~~~

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