[轉載] Oracle優化器參考

本文講述了Oracle優化器的概念、工作原理和使用方法，兼顧了Oracle8i、9i以及最新的10g三個版本。理解本文將有助於您更好的更有效的進行SQL優化工作。

RBO優化器

      RBO是一種基於規則的優化器，隨著CBO優化器的逐步發展和完善，在最新的10g版本中Oracle已經徹底廢除了RBO。正在使用Oracle8i或9i的人們或多或少的都會碰到RBO，因此在詳細介紹CBO之前，我們有必要簡單回顧一下古老的RBO優化器。
      在RBO中Oracle根據可用的訪問路徑和訪問路徑的等級來選擇執行計劃，等級越高的訪問路徑通常執行SQL越慢，如果一個語句有多個路徑可走，Oracle總是選擇等級較低的訪問路徑。

RBO訪問路徑

 1級：用Rowid定位單行
      當WHERE子句中直接嵌入Rowid時，RBO走此路徑。Oracle不推薦直接引用Rowid，Rowid可能會由於版本的改變而變化，行遷移、行連結、EXP/IMP也會使Rowid發生變化。
 2級：用Cluster Join定位單行
      兩個表做等值連線，一方的連線欄位是Cluster Key，且WHERE中存在可以保證該語句僅返回一行記錄的條件時，RBO走此路徑。
 3級：用帶用唯一約束或做主鍵的Hash Cluster Key定位單行
 4級：用唯一約束的欄位或做主鍵的欄位來定位單行
 5級：Cluster Join
 6級：使用Hash Cluster Key
 7級：使用索引Cluster Key
 8級：使用複合索引
 9級：使用單欄位索引
 10級：用索引進行有界限範圍的查詢
     如，column >[=] expr AND column 或column LIKE ‘c%’
 11級：用索引欄位進行無界限的查詢
     如，WHERE column >[=] expr 或 WHERE column  12級：排序合併連線
 13級：對索引欄位使用MAX或MIN函式
 14級：ORDER BY索引欄位
 15級：全表掃描
      如果可以使用索引RBO會盡可能的去用索引而不是全表掃描，但是在下列一些情況RBO只能使用全表掃描：
     如果column1和column2是同一個表的欄位，含有條件column1 < column2或column1 > column2或column1 <= column2或column1 >= column2，RBO會用全表掃描。
     如果使用column IS NULL或column IS NOT NULL或column NOT IN或column != expr或column LIKE ‘%ABC’時，不論column有無索引，RBO都使用全表掃描。
     如果expr = expr2，expr表示式作用了一個欄位上，無論該欄位有無索引，RBO都會全表掃描。
 NOT EXISTS子查詢以及在檢視中使用ROWNUM也會造成RBO進行全表掃描。
     以上就是RBO的全部可用訪問路徑。RBO優化器死板的根據規則來選擇執行計劃顯然不夠靈活，在RBO中也無法使用物化檢視等Oracle提供的新特性，在Oracle8i時CBO已經基本成熟，因此Oracle強烈建議改用CBO優化器。下文將全面介紹CBO優化器。

CBO優化器結構

      CBO是基於成本的優化器，它根據可用的訪問路徑、物件的統計資訊、嵌入的Hints來選擇一個成本最低的執行計劃。

 CBO主要包含以下元件：

l 查詢轉換器（Query Transformer）
l 評估器（Estimator）
l 計劃生成器（Plan Generator）

查詢轉換器

      查詢語句的形式會影響所產生的執行計劃，查詢轉換器的作用就是改變查詢語句的形式以產生較好的執行計劃。
     從Oracle 8i開始就有四種轉換技術：檢視合併（View Merging）、謂詞推進（Predicate Pushing）、非巢狀子查詢（Subquery Unnesting）和物化檢視的查詢重寫（Query Rewrite with Materialized Views）。
      檢視合併：如果SQL語句中含有檢視，經分析後會把檢視放在獨立的“檢視查詢塊”中，每個檢視會產生一個檢視子計劃，當為整個語句產生執行計劃時，檢視子計劃會被直接拿來使用而不會照顧到語句的整體性，這樣就很容易導致不良執行計劃的生成。檢視合併就是為了去掉“檢視查詢塊”，將檢視合併到一個整體的查詢塊中，這樣就不會有檢視子計劃產生，執行計劃的優良性得到提升。
      謂詞推進：不是所有的檢視都能夠被合併，對於那些不能被合併的檢視Oracle會將相應的謂詞推進到檢視查詢塊中，這些謂詞通常是可索引的或者是過濾性較強的。
      非巢狀子查詢：子查詢和檢視一樣也是被放於獨立查詢塊中的，查詢轉換器會將絕大多數子查詢轉換為連線從而合併為同一查詢塊，少量不能被轉換為連線的子查詢，會將它們的子計劃安照一個高效的方式排列。
      物化檢視的查詢重寫：當query_rewrite_enabled=true時，查詢轉換器尋找與該查詢語句相關聯的物化檢視，並用物化檢視改寫該查詢語句。

關於“窺視”（Peeking）

      在Oracle9i中為查詢轉換器增加了一個功能，就是當使用者使用繫結變數時，查詢轉換器可以“偷窺”繫結變數的實際值。
      我們知道使用繫結變數雖然可以有效的減少“硬分析”，但它帶來的負面影響是優化器無法根據實際的資料分佈來優化SQL，很有可能本可以走索引的SQL卻做了全表掃描。“窺視”正是為了解決這個問題，但是它並沒有徹底的解決，Oracle只允許第一次呼叫時進行“窺視”，接下來的呼叫即使繫結變數的值發生了變化，也仍然是使用第一次生成的執行計劃，這就造成了一個錯誤的執行計劃會被多次使用，10g中的“窺視”也是如此。

評估器

      評估器通過計算三個值來評估計劃的總體成本：選擇性（Selectivity）、基數（Cardinality）、成本（Cost）。
     選擇性：是一個大於0小於1的數，0表示沒有記錄被選定，1表示所有記錄都被選定。統計資訊和直方圖關係到選擇性值的準確性。如：name=’Davis’，如果不存在統計資訊評估器將根據所用的謂詞來指定一個預設的選擇性值，此時評估器會始終認為等式謂詞的選擇性比不等式謂詞小；如果存在統計資訊而不存在直方圖，此時選擇性值為1/count(distinct name)；如果存在統計資訊也存在直方圖，選擇性值則為count（name）where name=’Davis’ / count（name）where name is not null。
      基數：通常表中的行數稱為“基礎基數”（Base cardinality）；當用WHERE中的條件過濾後剩下的行數稱為“有效基數”（Effective cardinality）；連線操作之後產生的結果集行數稱為“連線基數”（Join cardinality）；一個欄位DISTINCT之後的行數稱為“DISTINCT基數”；“GROUP基數”（Group cardinality）比較特殊，它與基礎基數和DISTINCT基數有關，例如：group by colx則GROUP基數就等於基礎基數，但是group by colx,coly的GROUP基數則大於max ( distinct cardinality of colx , distinct cardinality of coly )且小於min ( (distinct cardinality of colx * distinct cardinality of coly) , base cardinality)。
      成本：就是度量資源消耗的單位。可以理解為執行表掃描、索引掃描、連線、排序等操作所消耗I/O、CPU、記憶體的數量。

計劃生成器

      計劃生成器的作用就是生成大量的執行計劃，然後選擇其中總體成本最低的一個。
      由於不同的訪問路徑、連線方式和連線順序可以任意組合，雖然以不同的方式訪問和處理資料，但是可以產生同樣的結果，因此一個SQL可能存在大量不同的執行計劃。但實際上計劃生成器很少會試驗所有的可能存在的執行計劃，如果它發現當前執行計劃的成本已經很低了，它將停止試驗，相反當前計劃的成本如果很高，它將繼續試驗其他執行計劃，因此如果能使計劃生成器一開始就找到成本很低的執行計劃，則會大量減少所消耗的時間，這也正是我們為什麼用HINTS來優化SQL的原因之一。

優化器模式及優化目標

      除了上述的CBO優化器外，Oracle還有一種基於規則的RBO優化器，在8i以後Oracle就不再發展RBO了，有很多新特性在RBO中也不被支援，在最新的10g中RBO已被徹底廢除。在10g前RBO與CBO共存，使用者可以通過設定初始化引數OPTIMIZER_MODE來決定到底使用哪個優化器，也可以用ALTER SESSION來改變當前SESSION中OPTIMIZER_MODE的值。除此之外在SQL中嵌入HINTS可以指定具體某個SQL使用哪個優化器。
      CBO雖然是基於成本的優化器，但仍然允許以“時間”或者說“響應速度”為優化目標，通過設定OPTIMIZER_MODE或者對具體語句嵌入HINTS都可以指定優化目標。

 OPTIMIZER_MODE選項如下：

l ALL_ROWS
l FIRST_ROWS_n
l FIRST_ROWS
l CHOOSE
l RULE

CHOOSE

      僅在9i及之前版本中被支援，10g已經廢除。8i及9i中為預設值。
      這個值表示SQL語句既可以使用RBO優化器也可以使用CBO優化器，而決定該SQL到底使用哪個優化器的唯一因素是，所訪問的物件是否存在統計資訊。如果所訪問的全部物件都存在統計資訊，則使用CBO優化器優化SQL；如果只有部分物件存在統計資訊，也仍然使用CBO優化器優化SQL，優化器會為不存在統計資訊物件依據一些內在資訊（如分配給該物件的資料塊）來生成統計資訊，只是這樣生成的統計資訊可能不準確，而導致產生不理想的執行計劃；如果全部物件都無統計資訊，則使用RBO來優化該SQL語句。

RULE

      僅在9i及之前版本中被支援，10g已經廢除。
      不論是否存在統計資訊，都將使用RBO優化器來優化SQL。

ALL_ROWS

      在10g中為預設值。
      不論是否存在統計資訊，都使用CBO優化器，且把CBO的優化目標設定為“最小的成本”。

FIRST_ROWS

      CBO儘可能快速的返回結果集的前面少數行記錄。
不論是否存在統計資訊，都使用CBO優化器，FIRST_ROWS導致CBO使用“試探法”來產生執行計劃，這種方式其成本可能會稍大一些。

FIRST_ROWS_n

       不論是否存在統計資訊，都使用CBO優化器，並以最快的速度返回前n行記錄，n可以是1,10,100,1000。

影響優化器模式及目標的HINTS：

 l RULE：意義同OPTIMIZER_MODE=RULE區別在於HINTS作用在語句級，10g中該HINTS已被廢棄。
l CHOOSE：意義同OPTIMIZER_MODE=CHOOSE，10g中已被廢棄。
l FIRST_ROWS：意義同OPTIMIZER_MODE=FIRST_ROWS，10g中已被廢棄。
l ALL_ROWS：意義同OPTIMIZER_MODE=ALL_ROWS。
l FIRST_ROWS(n)：意義同OPTIMIZER_MODE=FIRST_ROWS_n。
l CPU_COSTING：啟用CPU成本計算，也就是在總成本中考慮CPU的成本，預設是啟用的。該HINTS是10g中新增加的。
l NO_CPU_COSTING：關閉CPU成本計算，也就是在總成本中不考慮CPU的成本，只計算I/O的成本。該HINTS也是10g中新增加的。

訪問路徑

      訪問路徑就是從資料庫裡檢索資料的方式。優化器首先檢查WHERE子句和FROM子句的條件，確定有哪些訪問路徑是可用的。然後優化器使用這些訪問路徑或各訪問路徑的聯合，產生一組可能存在的執行計劃，再通過索引、欄位、表的統計資訊評估每個計劃的成本，最後優化器選擇成本最低的執行計劃所對應的訪問路徑。
      如果SQL語句的FROM子句無SAMPLE或SAMPLE BLOCK，優化器在選擇訪問路徑的時候會優先考慮語句中的HINTS。

 優化器可用的訪問路徑如下：

l 全表掃描（Full Table Scans）
l Rowid掃描（Rowid Scans）
l 索引掃描（Index Scans）
l 簇掃描（Cluster Scans）
l 雜湊掃描（Hash Scans）
l 表取樣掃描（Sample Table Scans）

全表掃描

      全表掃描將讀取HWM之下的所有資料塊，所有行都要經WHERE子句過濾看是否滿足條件。當Oracle執行全表掃描時會按順序讀取每個塊且只讀一次，如果能夠一次讀取多個塊，可以有效的提高效率，初始化引數DB_FILE_MULTIBLOCK_READ_COUNT用來設定在一次I/O中可以讀取多少個資料塊。 通常我們認為應該避免全表掃描，但是在檢索大量資料時全表掃描優於索引掃描，這正是因為全表掃描可以在一次I/O中讀卻多個塊，從而減少了I/O的次數。在使用全表掃描的同時也可以使用並行來提高掃描的速度。

CBO優化器何時會選擇全表掃描

1） 無合適的索引。
2） 檢索表中絕大多數的資料。
3） 表非常小。比如，表中的塊小於DB_FILE_MULTIBLOCK_READ_COUNT，只需一次I/O。如果這樣的表被頻繁使用應該alter table table_name storage(buffer_pool keep)。
4） 高並行度。如果在表級設定了較高的並行度，如alter table table_name parallel(degree 10)，通常會使CBO選擇全表掃描。通常建議在語句級用HINTS來實現並行，如/*+full(table_name) parallel(table_name degree)*/。
5） 太舊的統計資料。如果表沒有進行過分析或很久沒有再次分析，CBO可能會錯誤的認為表含有及少的資料塊。
6） 在語句中嵌入了全表掃描的HINTS。

 

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