Oracle優(yōu)化器的示例分析

這篇文章主要介紹Oracle優(yōu)化器的示例分析，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

一、優(yōu)化器的模式

      優(yōu)化器的模式用于決定在Oracle中解析目標SQL時所用優(yōu)化器的類型，以及決定當使用CBO時計算成本值的側重點。這里的“側重點”是指當使用CBO來計算目標SQL各條執(zhí)行路徑的成本值時，計算成本值的方法會隨著優(yōu)化模式的不同而不同。

      在Oracle數據庫中，優(yōu)化器的模式是由參數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中所涉及的各個對象的統(tǒng)計信息對于RBO來說沒有任何作用。

2.CHOOSE

      CHOOSE是Oracle9i中OPTIMIZER_MODE的默認值，它表示Oracle在解析目標SQL是到底是使用RBO還是使用CBO取決于該SQL中所涉及的表對象是否有統(tǒng)計信息。具體來說就是：只要該SQL中所涉及的表對象中有一個有統(tǒng)計信息，那么Oracle在解析該SQL時就會使用CBO；如果該SQL中所涉及的所有表對象均沒有統(tǒng)計信息，那么此時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的各條執(zhí)行路徑的成本值時的側重點在于以最快的響應速度返回頭n(n=1,10,100,1000)條記錄。Oracle會把那些最快的響應速度返回頭(n=1,10,100,1000)條記錄所對應的執(zhí)行步驟的成本修改成一個很小的值(遠小于默認情況下CBO對同樣執(zhí)行步驟所計算出的成本值)。這樣Oracle就既沒有違背CBO選擇執(zhí)行計劃的總原則(成本值最小)，同樣又兼顧 了FIRST_ROWS_n(n=1,10,100,1000)的含義。

4.FIRST_ROWS

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

5.ALL_ROWS

      ALL_ROWS是Oracle 10g以及后續(xù)Oracle數據庫版本中OPTIMIZER_MODE的默認值，它表示Oracle會使用CBO來解析目標SQL，且此時CBO在計算該SQL的各條執(zhí)行路徑的成本值時的側重點在于最佳的吞吐量(即最小的系統(tǒng)I/O和CPU資源的消耗量)。

      實際上，成本的計算方法隨著優(yōu)化器模式的不同而不同，主要體現(xiàn)在ALL_ROWS和FIRST_ROWS_n(n=1,10,100,1000)對成本值計算方法的影響上。當優(yōu)化器模式為ALL_ROWS時，CBO計算成本的側重點在于最佳的吞吐量；而當優(yōu)化器模式為FIRST_ROWS_n(n=1,10,100,1000)時，CBO計算成本的側重點會變?yōu)橐宰羁斓捻憫俣确祷仡^n(n=1,10,100,1000)條記錄。這意味著同樣的執(zhí)行步驟，在優(yōu)化器模式不同時CBO分別計算出來的成本會存在巨大的差異，這也就意味著優(yōu)化器對CBO計算成本(進而對CBO選擇執(zhí)行計劃)有著決定性的影響。

二、結果集

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

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

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

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

三、訪問數據的方法

      對于優(yōu)化器而言，它在解析目標SQL、得到其執(zhí)行計劃時至關重要的一點是決定訪問數據的方法，即優(yōu)化器要決定采用什么樣的方式和方法去訪問目標SQL所需要訪問的存儲在Oracle數據庫中的數據。

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

3.1訪問表的方法

      Oracle數據庫中直接訪問表中數據的方法有兩種：一種是全表掃描；另一種是ROWID掃描。

3.1.1 全表掃描

      全表掃描是指Oracle在訪問目標表里的數據時，會從該表所占用的第一個區(qū)(EXTENT)的第一個塊(BLOCK)開始掃描，直接掃描到該表的高水位線(HWM,High Water Mark)，這段范圍內所有的數據塊Oracle都必須讀到。當然，Oracle會對這期間讀到的所有數據施加目標SQL的where條件中指定的過濾條件，最后只返回那些滿足過濾條件的數據。

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

      在Oracle中如果對目標表不停地插入數據，當分配給該表的現(xiàn)有空間不足時高水位線就會向上移動，但如果你用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索引

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

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

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

3.2.1 索引唯一性掃描

      索引唯一性掃描(INDEX UNIQUE SCAN)是針對唯一性索引(UNIQUE INDEX)的掃描，它僅僅適用于where條件里等值查詢的目標SQL。因為掃描的對象是唯一性索引，所以索引唯一性掃描的結果至多只會返回一條記錄。

3.2.2 索引范圍掃描

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

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

3.2.3 索引全掃描

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

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

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

3.2.4 索引快速全掃描

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

      索引快速全掃描與索引全掃描相比有如下三點區(qū)別：

1)索引快速全掃描只適用于CBO。

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

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

3.2.5 索引跳躍式掃描

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

Oracle中的索引跳躍式掃描僅僅適用于那些目標索引前導導列的distinct值數量較少，后續(xù)非前導列的可選擇性又非常好的情形，因為索引跳躍式掃描的執(zhí)行效率一定會隨著目標索引前導列的distinct值數量的遞增而遞減。

以上是“Oracle優(yōu)化器的示例分析”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業(yè)資訊頻道！