基于成本的優化器 — 一般錯誤概念和問題
2024-07-21 02:39:28
供稿：網友
IntrodUCtion 介紹
~~~~~~~~~~~~
本短文著意于消除一些關于基于成本的優化器（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 錯。
- 不要分析數據字典表（SYS表）除非你有足夠充分的理由。關于這一點有和一些文檔或README文件相矛盾，他們說DBMS_UTILITY.ANALYZE_SCHEMA 可以用于分析SYS表。雖然DBMS_UTILITY.ANALYZE_SCHEMA 可以分析SYS用戶，但Oracle沒有對這些被分析的表進行衰退測試，可能會造成死鎖或效率問題。
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語句
~~~~~~~~~~~~~~~~~~~~~~
假如 C
