Oracle優化器的基礎知識(一)

一、優化器的模式

      優化器的模式用于決定在Oracle中解析目标SQL時所用優化器的類型，以及決定當使用CBO時計算成本值的側重點。這裡的“側重點”是指當使用CBO來計算目标SQL各條執行路徑的成本值時，計算成本值的方法會随着優化模式的不同而不同。

      在Oracle資料庫中，優化器的模式是由參數OPTIMIZER_MODE的值決定的，OPTIMIZER_MODE的值可能是RULE、CHOOSE、FIRST_ROWS_n(N=1,10,100,1000)、FIRST_ROWS或ALL_ROWS。

OPTIMIZER_MODE的各個可能的值的含義為如下所示：

1.RULE

      RULE表示Oracle将使用RBO來解析目标SQL，此時目标SQL中所涉及的各個對象的統計資訊對于RBO來說沒有任何作用。

2.CHOOSE

      CHOOSE是Oracle9i中OPTIMIZER_MODE的預設值，它表示Oracle在解析目标SQL是到底是使用RBO還是使用CBO取決于該SQL中所涉及的表對象是否有統計資訊。具體來說就是：隻要該SQL中所涉及的表對象中有一個有統計資訊，那麼Oracle在解析該SQL時就會使用CBO；如果該SQL中所涉及的所有表對象均沒有統計資訊，那麼此時Oracle會使用RBO。

3.FIRST_ROWS_n(n=1,10,100,1000)

      其含義是指當OPTIMIZER_MODE的值為FIRST_ROWS_n(n=1,10,100,1000)時，Oracle會使用CBO來解析目标SQL，且此時CBO在計算該SQL的各條執行路徑的成本值時的側重點在于以最快的響應速度傳回頭n(n=1,10,100,1000)條記錄。Oracle會把那些最快的響應速度傳回頭(n=1,10,100,1000)條記錄所對應的執行步驟的成本修改成一個很小的值(遠小于預設情況下CBO對同樣執行步驟所計算出的成本值)。這樣Oracle就既沒有違背CBO選擇執行計劃的總原則(成本值最小)，同樣又兼顧 了FIRST_ROWS_n(n=1,10,100,1000)的含義。

4.FIRST_ROWS

      FIRST_ROWS是一個在Oracle9i中就已經過時的參數，它表示Oracle在解析目标SQL是會聯合使用CBO和RBO。這裡聯合使用CBO和RBO的含義是指在大多數情況下，FIRST_ROWS還是會使用CBO來解析目标SQL，且此時CBO在計算該SQL的各條執行路徑的成本值時的側重點在于以最快的響應速度傳回頭幾條記錄(類似于FIRST_ROWS_n)；但是，當出現一些特定情況時，FIRST_ROWS轉而會使用RBO中的一些内置規則來選取執行計劃而不再考試成本。比如當OPTIMIZER_MODE的值為FIRST_ROWS有一個内置的規則，就是如果Oracle發現能用相關的索引來避免排序，則Oracle就會選擇該索引所對應的執行路徑而不再考慮成本，這顯然是不合理的。與這對應的，在當OPTIMIZER_MODE的值為FIRST_ROWS的情形下，你會發現索引全掃描出現的機率會比之前有所增加，這是因為走索引全面掃描能夠避免排序的緣故。

5.ALL_ROWS

      ALL_ROWS是Oracle 10g以及後續Oracle資料庫版本中OPTIMIZER_MODE的預設值，它表示Oracle會使用CBO來解析目标SQL，且此時CBO在計算該SQL的各條執行路徑的成本值時的側重點在于最佳的吞吐量(即最小的系統I/O和CPU資源的消耗量)。

      實際上，成本的計算方法随着優化器模式的不同而不同，主要展現在ALL_ROWS和FIRST_ROWS_n(n=1,10,100,1000)對成本值計算方法的影響上。當優化器模式為ALL_ROWS時，CBO計算成本的側重點在于最佳的吞吐量；而當優化器模式為FIRST_ROWS_n(n=1,10,100,1000)時，CBO計算成本的側重點會變為以最快的響應速度傳回頭n(n=1,10,100,1000)條記錄。這意味着同樣的執行步驟，在優化器模式不同時CBO分别計算出來的成本會存在巨大的差異，這也就意味着優化器對CBO計算成本(進而對CBO選擇執行計劃)有着決定性的影響。

二、結果集

      結果集(Row Source)是指包含指定執行結果的集合。對于優化器而言(無論是RBO還是CBO)，結果集和目标SQL執行計劃的執行步驟相對應，一個執行步驟所産生的執行結果就是該執行步驟所對應的輸出結果集。

      對于目标SQL的執行計劃而言，其中某個執行步驟的輸出結果就是該執行步驟所對應的輸出結果集，同時，該執行步驟所對應的輸出結果集可能就是下一個執行步驟的輸入結果集。這樣一步一步執行下來，伴随的就是結果集在各個執行步驟之間的傳遞，等目标SQL執行計劃的各個執行步驟全部執行完畢後，最後的輸出結果集就是該SQL最終的執行結果。

      對于RBO而言，我就在對應的執行計劃中看不到相關執行步驟所對應的結果集的描述，雖然結果集的概念對于RBO來說也同樣适用。

      對于CBO而言，對應執行中的Rows列反映的就是CBO對于相關執行步驟所對應輸出結果集的記錄數(即Cardinality)的估算值。

三、通路資料的方法

      對于優化器而言，它在解析目标SQL、得到其執行計劃時至關重要的一點是決定通路資料的方法，即優化器要決定采用什麼樣的方式和方法去通路目标SQL所需要通路的存儲在Oracle資料庫中的資料。

      目标SQL所需要通路的資料一般存儲在表，而Oracle通路表中資料的方法有兩種：一種是直接通路表；另一種是先通路索引，再回表(當然，如果目标SQL所通路的資料隻通過通路相關的索引就可以得到，那麼此時就不需要再回表了)。

3.1通路表的方法

      Oracle資料庫中直接通路表中資料的方法有兩種：一種是全表掃描；另一種是ROWID掃描。

3.1.1 全表掃描

      全表掃描是指Oracle在通路目标表裡的資料時，會從該表所占用的第一個區(EXTENT)的第一個塊(BLOCK)開始掃描，直接掃描到該表的高水位線(HWM,High Water Mark)，這段範圍内所有的資料塊Oracle都必須讀到。當然，Oracle會對這期間讀到的所有資料施加目标SQL的where條件中指定的過濾條件，最後隻傳回那些滿足過濾條件的資料。

      不是說全表掃描不好，事實上Oracle在做全表掃描操作時會使用多塊讀，這在目标表的資料不大時執行效率是非常高的，但全表掃描最大的問題就在于走全表掃描的目标SQL執行時間會不穩定、不可控，這個執行時間一定會随着目标表資料量的遞增而遞增。因為随着目标表資料量的遞增，它的高水位線會一直不段往上漲，是以全表掃描時所需要讀取的資料塊的資料也會不斷增加。

      在Oracle中如果對目标表不停地插入資料，當配置設定給該表的現有空間不足時高水位線就會向上移動，但如果你用DELETE語句從該表删除資料，則高水位線并不會随之往下移動。高水位線這種特性所帶來的副作用是，即使使用DELETE删光了目标表中的所有資料，高水位線還是會在原來的位置，這意味着全表掃描該表時Oacle還是需要掃描該表高水位線下所有的資料塊，此時對該表的全表掃描操作耗費的時間與之前相比并不會有明顯的改觀。

3.1.2 ROWID掃描

      ROWID掃描是指Oracle在通路目标表裡的資料時，直接通過資料所在的ROWID去定位并通路這些資料。ROWID表示的是Oracle中的資料行記錄所在的實體存儲位址，也就是說ROWID實際上是和Oracle資料塊裡的行記錄一一對應的。

      既然ROWID代表的就是表的資料行所在的實體存儲位址，那麼當Oracle知道待通路的資料行所在的ROWID後，自然就可以根據該RWOID去直接通路對應表的相關資料行，這就是ROWID掃描的含義。

      從嚴格意義上來說，Oracle中的ROWID掃描有兩層含義：一種是根據使用者在SQL語句中輸入的ROWID的值去直接通路對應的資料行記錄；另外一種方法是先去通路相關的索引，然後根據通路索引後得到的ROWID再回表去通路對應的資料行。

      對Oracle中的堆表而言，我們可以通過Oracle内置的ROWID僞列得到對應行記錄所在的ROWID值，然後還可以通過DBMS_ROWID包中的相關方法将ROWID僞列的值翻譯成對應資料行的實際實體存儲位址。

3.2 通路索引的方法

這裡提到的索引是指最常用的B*Tree索引

<a href="https://s5.51cto.com/wyfs02/M02/8D/78/wKioL1idY0aRpyc0AAO1DLIKHQk041.png" target="_blank"></a>

      Oracle資料庫的的B*Tree索引就好像一棵倒長的樹，它包含兩種類型的資料塊，一種是索引分支塊，另一種是索引葉子塊。

      索引分支塊包含指向相應索引分支塊/葉子塊的指針和索引鍵值列(這裡的指針是指相關分支塊/葉子塊的塊位址RDBA。每個索引分支塊都會有兩種類型的指針，一種是lmc，另一種是索引分支塊的索引行記錄所記錄的指針。lmc是Left Most Child的縮寫，每個索引分支塊都隻有一個lmc，這個lmc指向的分支塊/葉子塊中的所有索引鍵值列中的最大值一定小于該lmc所在索引分支塊的所有索引鍵值列中的最小值；而索引分支塊的索引行記錄所記錄的指針所指向的分支塊/葉子塊的所有索引鍵值列中的最小值一定大于或等于該行記錄的索引鍵值列的值)。這個索引列值不一定就是完整的被索引鍵值，它可能隻是被索引鍵值的字首，隻要Oracle能通過這些字首區分相應的索引分支塊/葉子塊就行，這樣Oracle就能夠既節省分支塊的存儲空間，又可以快速定位其下層的索引分支塊/葉子塊。索引分支塊最上層的那個塊就是所謂的索引根節點。在Oracle裡通路B*Tree索引的操作都必須從根節點開始，即都會經曆一個從根節點到分支塊再到葉子塊的過程。

      索引葉子塊包含被索引鍵值和用于定位該索引鍵值所在的資料行在表中實際實體存儲位置的ROWID。對于唯一性的B*Tree索引而言，ROWID是存儲在索引行的行頭，是以此時Oracle并不需要額外礁該ROWID的長度。而對于非唯一性的B*Tree索引而言，ROWID被當作額外的列與被索引的鍵值列一起存儲，是以此時Oracle既要存儲ROWID，同時又要存儲其長度，這意味着在同等條件下，唯一性B*Tree索引要比非唯一性B*Tree索引節省索引葉子塊的存儲空間。對于非唯一性索引而言，B*Tree索引的有序性展現在Oralce會按照被索引鍵值和相應的ROWID來聯合排序。Oralce裡的索引葉子塊是左右互聯的，即相當于有一個雙向指針連結清單把這些索引葉子塊互相連接配接在了一起。

3.2.1 索引唯一性掃描

      索引唯一性掃描(INDEX UNIQUE SCAN)是針對唯一性索引(UNIQUE INDEX)的掃描，它僅僅适用于where條件裡等值查詢的目标SQL。因為掃描的對象是唯一性索引，是以索引唯一性掃描的結果至多隻會傳回一條記錄。

3.2.2 索引範圍掃描

      索引範圍掃描(INDEX RANGE SCAN)适用于所有類型的B*Tree索引，當掃描的對象是唯一性索引時，此時目标SQL的where條件一定是範圍查詢(謂詞條件為BETWEEN、&lt;、&gt;等)；當掃描的對象是非唯一性索引時，對目标SQL的where條件沒有限制(可以是等值查詢，也可以是範圍查詢)。索引範圍掃描的結果可能會傳回多條記錄，其實這就是索引範圍掃描中的“範圍”二字的本質含義。

      在同等條件下，當目标索引的索引行的數量大于1時，索引掃描範圍所耗費的邏輯讀至少會比生意人索引唯一性掃描的邏輯讀多1。因為掃描結果可能會傳回多條記錄，又因為目标索引的索引行數量大于1，Oracle為了确定索引範圍掃描的掃描終點，就不得不去多次通路相關的葉子塊。

3.2.3 索引全掃描

      索引全掃描(INDEX FULL SCAN)适用于所有類型的B*Tree索引(包括唯一性索引和非唯一性索引)。所謂的“索引全掃描”，就是指要掃描目标索引所有葉子塊的所有索引行。這裡需要注意的是，索引全掃描需要掃描目标索引的所有葉子塊，但并不意味着需要掃描該索引的所有分支塊。在預設情況下，Oracle在做索引全掃描時隻需通過通路必要的分支塊定位到位置該索引最左邊的葉子塊的第一行索引行，就可以利用該索引葉子塊之間的雙向指針連結清單，從左至右依次順序掃描該索引所有葉子塊的所有索引行了。由于索引是有序的，是以索引全掃描的執行結果也是有序的，并且是按照索引的索引鍵值列來排序，這也意味着走索引全掃描能夠既達到排序的效果，又同時避免了對該索引的索引鍵值列的真正排序操作。

      預設情況下，索引全掃描的掃描結果的有序性就決定了索引全掃描是不能夠并行執行的，并且通常情況下索引全掃描使用的是單塊讀。

      通常情況下，索引全掃描是不需要回表的，是以索引全掃描适用于目标SQL的查詢全部是目标索引的索引鍵值列的情形。我們知道，對于Oracle資料庫的B*Tree索引而言，當所有索引鍵值列全為NULL值時不入索引，這意味着Oracle中能做索引全掃描的前提條件是目标索引至少有一個索引鍵值列的屬性是NOT NULL。這很顯然，如果目标索引的所有索引鍵值列的屬性均為允許NULL值，此時如果還走索引全掃描，就會漏掉目标表中那些索引值列均為NULL的記錄，即此時走索引全掃描的結果就不準了！Oracle不允許這種事情發生。

3.2.4 索引快速全掃描

      索引快速全掃描(INDEX FAST FULL SCAN)和索引全掃描極為類似，它也适用于所有類型的B*Tree索引。和索引全掃描一樣，索引快速全掃描也需要掃描目标索引所有葉子塊的所有索引行。

      索引快速全掃描與索引全掃描相比有如下三點差別：

1)索引快速全掃描隻适用于CBO。

2)索引快速全掃描可以使用多塊讀，也可以并行執行。

3)索引快速全掃描的執行結果不一定是有序的。這是因為索引快速全掃描時Orace是根據索引行在磁盤上的實體存儲順序來掃描，而不是根據索引行的邏輯順序來掃描的，是以掃描結果才不一定有序(對于單個索引葉子塊的索引行而言，其實體存儲順序和邏輯存儲順序一緻；但對于實體存儲位置相鄰的索引葉子塊而言，塊與塊之間索引行的實體存儲順序則不一定在邏輯上有序)。

3.2.5 索引跳躍式掃描

      索引跳躍式掃描(INDEX SKIP SCAN)适用于所有類型的複合B*Tree索引，它使那些在where條件中沒有對目标索引的前導列指定查詢條件，但同時又對該索引的非前導列指定了查詢條件的目标SQL依然可以使用上該索引，這就像是在掃描該索引時跳過了它的前導列，直接從該索引的非前導到開始掃描一樣(實際執行過程并非如此)，這民是索引跳躍式掃描中“跳躍”(SKIP)一詞的含義。

Oracle中的索引跳躍式掃描僅僅适用于那些目标索引前導導列的distinct值數量較少，後續非前導列的可選擇性又非常好的情形，因為索引跳躍式掃描的執行效率一定會随着目标索引前導列的distinct值數量的遞增而遞減。

 參考：《基于Oracle的SQL優化》

     本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1896698，如需轉載請自行聯系原作者