原理篇01–優化器與成本

? 優化器是資料庫最核心的功能，也是最復雜的一部分，它負責將用戶提交的SQL陳述句根據各種判斷標準，制定出最優的執行計劃，并交由執行器來最終執行，優化器演算法的好壞、能力的強弱，直接決定了陳述句的執行效率，綜合比較來說，Oracle的優化器是功能最強大的，當然，優化器本身是資料庫系統中最為復雜的一個部分，

? 成本是優化器（基于成本的優化器）中反映 SQL陳述句執行代價的一個指標，優化器通過比較不同執行計劃的成本，選擇成本最小的作為最終的執行計劃，如何理解成本、成本如何計算也就成為我 們學習基于成本的優化器的關鍵所在，

1. 優化器

? 優化器在整個SQL陳述句的執行程序中充當了非常重要的角色，
? 下圖是一個SQL陳述句從提交到最終得到結果的示意圖，從中我們可以看到優化器充當的角色及其主要功能，

? Oracle的優化器也是在不斷演變中的，在早期的版本中，Oracle使用一種基于規則的優化器，顧名思義，它是按照某種特定的規則來制定執行計劃的，這種方式比較簡單直觀，但對資料庫自身情況及SQL陳述句中物件本身的情況都沒有考慮，在后期的Oracle版本中，又推出了另外一種優化器——基于成本的優化器，

基于規則的優化器

? 基于規則的優化器（Rule Based Optimizer， RBO ）內部采用了一種規則串列，其中每一種規則代表一種執行路徑并被賦予一個等級，不同的等級代表不同的優先級別， 等級越高的規則越會被優先采用，

? Oracle會在代碼里事先給各種型別的執行路 徑定一個等級，一共有15個等級，從等級1到等級15，Oracle會認為等級值低的執行路徑的執行效率 比等級值高的執行效率高，在決定目標SQL的執行 計劃時，如果可能的執行路徑不止一條，則RBO就 會從該SQL多種可能的執行路徑中選擇一條等級最 低的執行路徑來作為其執行計劃，

·Single Row by Rowid： 根據ROWID，回傳一 條記錄，這種規則發生在SQL陳述句的WHERE部分，指定了記錄的ROWID或者使用了CURRENT OF CURSOR形式的SQL，

·Single Row by Cluster Join： 根據聚簇連接，回傳一條記錄，這種規則發生在SQL陳述句中 WHERE部分，包含了兩表關聯，且關聯欄位為一個聚簇，同時還存在一個過濾條件為一個表的唯一 索引或主鍵，

·Single Row by Hash Cluster Key with Unique or Primary Key： 根據哈希聚簇鍵，回傳一條記錄，這種規則發生在SQL陳述句的WHERE部分所包含的過濾條件中，欄位是一個哈希聚簇鍵且這個欄位為唯一或主鍵索引欄位，

·Single Row by Unique or Primary Key： 根據主鍵或唯一索引鍵值，回傳一條記錄，這種規則發生在SQL陳述句中WHERE部分，為唯一或主鍵所有欄位的等值連接條件，

·Clustered Join： 根據聚簇連接，回傳一組記錄，這種規則跟Path 2類似，只不過過濾條件中沒有唯一限制，可以回傳多條記錄，

·Hash Cluster Key： 根據哈希聚簇鍵值，回傳一條記錄，這種規則跟Path 3類似，只不過過濾條件中沒有唯一限制，可以回傳多條記錄，

·Indexed Cluster Key： 根據一個索引的聚簇鍵欄位，回傳一組記錄，

·Composite Index： 根據一個組合索引欄位，回傳一組記錄，這種規則中WHERE部分需要指定組合索引欄位且通過邏輯“與”運算子進行連接，

·Single-Column Indexes： 根據單一索引欄位， 回傳一組記錄，

·Bounded Range Search on Indexed Columns： 根據索引欄位的有限范圍搜索，回傳一組記錄，這里所說的有限范圍搜索，包括欄位的等值比較、大于等于和小于等于、BETWEEN…AND、LIKE等過濾條件，

·Unbounded Range Search on Indexed Columns： 根據索引欄位的無限范圍搜索，回傳一組記錄，這里所說的無限范圍搜索，包括欄位的大于等于、小于等于過濾條件，

·Sort Merge Join： 根據排序合并關聯，回傳一組記錄，

·MAX or MIN of Indexed Column： 獲取一個索引欄位的最大、最小值，這種規則需要遍歷整個索引，

·ORDER BY on Indexed Column： 根據一個索引欄位，進行排序操作，

·Full Table Scan： 通過全表掃描方式，獲取一個結果集，

? 隨著Oracle自身技術的發展，在一般的作業場景中，很少會涉及使用RBO的情況，CBO優化器成為首選，只有在極個別的情況下，需要手工調整行計劃時，可采取指定優化器引數或參考相關的提示，且需要注意的是，因為RBO技術出現比較早，很多新的技術其不支持，因此在很多情況下即使手工指定使用RBO優化器，也可能會失效，Oracle仍然會使用CBO優化器，

基于成本的優化器

? 基于成本的優化器（Cost Based Optimizer， CBO）在堅持實事求是原則的基礎上，通過對具有現實意義的諸多要素的分析和計算來完成最優路徑的選擇作業，成本可以理解為SQL執行的代價，成本越低，SQL執行的代價越小，CBO也就認為是一個更優異的執行路徑，

? 但是CBO仍然存在一些特殊情況，導致其可能產生較差的執行計劃，這也是以后CBO發展，需要彌補的弱點，

? CBO存在的問題主要有以下幾個方面：

• 多列關聯關系：在默認情況下，CBO認為 WHERE條件中的各個欄位之間是獨立的，并據此計算其選擇率，進而估計成本來選擇執行計劃，
• SQL無關性： CBO認為SQL陳述句運行都是相對獨立的，之間沒有任何關系；但在實際運行中可能是有關聯的，
• 直方圖統計資訊：對于文本型欄位的直方圖收集，Oracle只會提取前32位元組（對于多位元組字符集來說更加嚴重），這樣獲得的資料會失真， 可能會導致優化器獲得錯誤的執行計劃，
• 復雜多表關聯： 對于復雜的多表關聯，其可能的表間關聯順序組合隨著表的數量增加呈幾何級數增長，而CBO至多只會考慮其中根據引數_OPTIMIZER_MAX_PERMUTATIONS計算出來的有限種可能，也意味著只要該目標SQL正確的執行計劃不在上述有限種可能之中，則CBO一定會漏選最優的執行計劃，
  
  

在通常情況下，選用CBO優化器，這也是Oracle強大之所在，在極個別的情況下，也存在對CBO優化器不適合使用的情況下，原因可能是BUG或者CBO設計問題，此時可以考慮使 用RBO優化器，但即使是這種情況，也要嚴格限制特定范圍，一般只在陳述句級使用RBO優化器，

優化器相關引數

優化器相關Hint

?在SQL優化中，除了可以通過修改引數的方式干預優化器作業外，還可以使用Hint—提示的方式進行干預，而且這種方式更加精準、不影響其他SQL，故使用場景更加廣泛，

--格式范例
select /*+ all_rows */ empno,ename,sal,job from emp where empno=7369;

2 成本

?Oracle絕大多數情況下就是使用基于成本的優化器對SQL陳述句制定執行計劃的， 認識成本有利于理解優化器的行為，也更容易找出產生較差執行計劃的原因，但對于成本及其計算方法，Oracle公司并沒有開放很多資料，因而只能從一些公開的資料揣摩其作業原理、計算方法等，

成本的基本概念

?成本是指花費在單資料塊讀取上的時間，加上花費在多資料塊讀取上的時間，再加上所需的CPU 處理時間，然后將總和除以單資料塊讀取所花費的時間，也就是說，成本是陳述句的預計執行時間的總和，以單資料塊讀取時間單元的形式來表示，

計算公式

Cost=(#SRDs*sreadtim + #MRDs*mreadtim + #CPUCycles/cpuspeed )/sreadtim