基於成本的Oracle優化法則

當我們討論SQL語句的成本時，需要倍加謹慎，這是因為“成本”一詞同時存在兩方面的含義。一方面，成本可以指由諸如解釋計劃(explain plan)等工具導致的大量的執行次數；另一方面，成本可以指執行SQL語句導致的實際資源消耗。當然，從理論上講，這兩方面含義之間的關係應該比較簡單明瞭，因此也就不必過多地糾纏這兩種含義。

本書中的“成本”一詞是指優化器執行的計算導致的消耗。本書的目的在於解釋優化器執行計算過程中的主要特性，以此分析計算成本，並確定SQL語句的執行計劃(execution plan)。

與此同時，本書還介紹了一些其他問題，這些問題能夠使得優化器產生的成本似乎與實際資源消耗無關。當您閱讀這本書時，感覺到“這個查詢一定要這樣做的原因原來是這樣啊”，我們將倍感欣慰。

1.1 優化器選項

最常見的SQL語句型別是select語句—— 雖然本書的其他部分都將集中討論select語句，但此處需要強調的是，無論是哪種語句，只要是執行查詢操作，DML(比如更新)或DDL(比如索引重建)都需要通過基於成本的優化器(CBO)進行分析。

Oracle為基於成本的優化器提供了3個變體。這3個變體的程式碼中構建不同的約束，但是它們都遵循同一策略—— 即針對某個SQL語句，尋找能夠最少的資源消耗來達到目標的執行機制。這3個變體可以通過引數optimizer_mode標識：

●       all_rows：優化器將尋找能夠在最短的時間內完成語句的執行計劃(通常表示“返回所有行”)。該變體沒有在程式碼中構建特別的約束。

●       first_rows_N：N可以為1、10、100或1000(如果需要進行進一步優化，可以採用first_rows(n)提示的形式，其中n可以是任意正整數)。優化器首先通過徹底分析第一個連線順序(join order)來估計返回行的總數目。這親就可以知道查詢可能獲得的整個資料集的片斷，並重新啟動整個優化程式，其目標在於找到能夠以最小的資源消耗返回整個資料片斷的執行計劃。該選項是在Oracle 9i中引入的。

●       first_rows：在Oracle 9i中這一選項已經過時，但是出於向後相容的原因，仍然保留了這一選項。該選項的作用在於尋找能夠在最短的時間內返回結果集的第一行的執行計劃。該變體的程式碼中構建了幾個高層次約束。例如，有一個約束就是“避免歸併連線和雜湊連線，除非除此以外只能對內部(第二個)表進行全表掃描的巢狀迴圈”。這一規則傾向於促使優化器使用索引訪問路徑，偶爾會出現非常不恰當的訪問路徑。針對這一特殊問題的示例和相關內容可以在本章的聯機程式碼包中的指令碼檔案first_rows.sql中找到，從Apress網站(www.apress.com)和www.tupwk.com.cn中都可以找到這個程式碼包。

引數optimizer_mode還存在其他兩個選項(即使在Oracle 10g中也是如此)，分別為rule和choose。由於基於規則的優化(Rule Based Optimization)在多年前就已過時(只有某些內部SQL仍在使用/*+ rule */提示)，而且Oracle 10g中最終不再支援RBO，所以本書將完全忽略RBO。

對於choose模式來說，它為優化器提供了一種執行時選擇方式，可以在基於規則的優化和all_rows之間進行選擇。既然本書忽略基於規則的優化，那麼choose模式就無需多言了。在此僅僅申明以下問題，在Oracle 10g中，如果使用資料庫配置助手(Database Configuration Assistant，DBCA)來建立資料庫，或者通過呼叫指令碼catproc.sql來手動建立資料庫，那麼將自動安裝一個作業(由指令碼catmwin.sql建立，在檢視dba_scheduler_jobs中可見)，它將對缺少統計資訊或統計資訊失效的任何表每24小時生成一次統計資訊。因此，如果將optimizer_mode設定為choose，可能將觸發all_rows優化，但是您可能會發現所有缺少最新統計資訊的表將採用動態取樣。這是因為在Oracle 10g中，引數optimizer_dynamic_sampling的預設值為2(意味著針對沒有統計資訊的任何表都採用動態取樣)，而在Oracle 9i中該引數的預設值為1。

另一個與optimizer_mode有關的引數為optimizer_goal。雖然optimizer_goal只能在會話中動態地設定，而且在spfile(init.ora)中不可用，但是就優化策略而言，這兩個引數似乎沒有什麼區別。

1.2 成本的定義

網路上有關CBO的一個最常見的問題就是“成本究竟表示什麼？”針對這一問題的評論通常為“根據執行計劃，針對一個查詢的雜湊連線的成本是700萬，巢狀迴圈的成本是42—— 但是雜湊連線只需3秒就可以完成，而巢狀迴圈卻需要14個小時。”

答案很簡單：成本表示(也總是表示)優化器對執行語句所用時間的最優估計。但是面對上面所示的雜湊連線/巢狀迴圈連線示例時，怎樣才能得到正確的時間估計呢？答案可以在縮寫詞GIGO中找到，GIGO(Garbage In，Garbage Out)表示無用資訊輸入，無用資訊輸出。

出現如下問題時，CBO將產生錯誤：

●       建立成本模型所做的某些假設不合適。

●       資料分佈的相關統計資訊可用，但是容易誤導。

●       資料分佈的相關統計資訊不可用。

●       不瞭解硬體的效能特徵。

●       不瞭解當前的工作負荷。

●       程式碼中有bug。

本書將分析這些問題，並以此研究優化器的演化。但是，對於這些問題產生的影響，本書僅簡要介紹CBO在Oracle的最近版本中(也可能在以後的版本中)出現的一些變化，這些變化導致優化器的含義解釋困難得多。

在Oracle 8i中，優化器只是簡單地計算期望對I/O子系統所作的請求的數目，並選擇一個所需請求數目最小的執行計劃。這並不能說明表掃描比索引訪問路徑會消耗多得多的CPU資源，也不能說明讀128個資料塊(128-block)時間比讀單個資料塊的時間要長。也不能說明理論上讀128個資料塊實際上可以精確地轉換為25個單獨的請求，這是因為在Oracle緩衝區中已經儲存了所需資料塊的一個隨機散佈的子集。同樣它也無法說明I/O請求可以通過中間快取實現而不是通過實際讀取物理磁碟實現。

在Oracle 9i中，優化器引入了一個新的功能，稱為CPU成本計算(CPU costing)。您可以將對單資料塊和多資料塊I/O請求的典型反應時間儲存到資料庫中，同時儲存一個關於多資料塊請求的典型大小的指示器，優化器將自動地把這些因子引入到成本方程中。優化器還可以將CPU操作的數目(比如將日期列與常量進行比較)轉換為CPU時間，並將其引入到成本方程中。這些改進將確保優化器能夠更好地估計表掃描的成本，併產生更加符合實際的執行計劃，以及減少像本節前面提到的雜湊連線和巢狀迴圈連線那樣的異常。

在Oracle 10g中，引入了一個新的功能，稱為離線優化器(Offline Optimizer)。基於離線優化器可以產生並儲存關鍵的統計資訊(以profile的形式)，這些資訊能夠幫助聯機優化器(Online Optimizer)處理有關資料分佈的問題。實際上，可以通過新增一個提示，如“此處，您獲得的資訊將是您期望的15倍”，來加強某個查詢。Oracle 9i和Oracle 10g都在物件層次上收集快取統計資訊，但是，從未來的角度看，Oracle 10g擁有一對隱含引數，看起來似乎能夠引發高精度和快取記憶體識別(cache-aware)的計算。如果這一功能能夠實現，則優化器基於最近的快取成功而產生的計劃可能會更好地反映實際需要的I/O請求數目。

此外，Oracle 9i和Oracle 10g都通過檢視v$sql_plan_statistics和v$sql_plan_statistics_all收集執行時統計資訊。理論上講，這些統計資訊能夠反饋給優化器，以保證當實際的統計資訊和優化器假定的資訊區別很大時，優化器能夠有第二次機會對查詢進行優化。

如果未來的某一天推出Oracle 9i和Oracle 10g的次要版本，您可以直接看到查詢的成本並非常自信地將其轉換為近似的執行時間，這是因為優化器能夠針對您的資料恰如其時地得出在本機上的準確執行計劃。(當然，那種因進行中的行為而導致意圖每隔5分鐘就發生變化的優化器帶來的更可能是危險而非助益—— 也就是說，我們更強調優化器的預測性和穩定性，而不是常有失敗的“完美”。)

與此同時，為什麼我們如此確信成本應該可以用等價的時間表示呢？想知道答案，請參見Oracle 9i Database Performance Funing Guide and Reference Release 2(9.2)(Part A96533)，第9-22頁：

According to the CPU costing model:

Cost = (

#SRds * sreadtim +

#MRds * mreadtim +

#CPUCycles / cpuspeed

) / sreadtim

where

#SRDs - number of single block reads

#MRDs - number of multi block reads

#CPUCycles - number of CPU Cycles

sreadtim - single block read time

mreadtim - multi block read time

cpuspeed - CPU cycles per second

轉譯過來，上述程式碼的含義為：

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

CPU速度報告

儘管手冊表明CPU速度cpuspeed以每秒的週期數進行報告，但在語句中可能會出現兩個錯誤。

其中一個較為簡單的錯誤就是顯示的變數表明度量單位可能是按每秒百萬個週期進行(也就是說，CPU的速度以MHz為單位)。即使這樣，數字總是達不到期望值—— 我們在各種不同的機器上進行測試，因子在5~30之間，結果都是如此。

另外一個比較微妙的錯誤就是實際上按每秒進行幾百萬個標準Oracle操作進行值的度量，其中，標準Oracle操作是一些特殊的子程式，可能會燒掉CPU。(10.2中的10053 trace file為此提供了一個驗證示例)。

無論數字表示每秒的週期數還是每秒的運算元，它們之間的差異僅僅是簡單的比例因子。使用cpuspeed的機制並沒有改變。

為什麼Oracle會為成本計算選擇這樣一種古怪的時間單位，而不是簡單地以釐秒(1/100秒)為單位進行計算？這純粹是為了滿足向後相容性。在Oracle 8i(包括9i，如果不啟用CPU成本計算)中，成本僅僅是對I/O請求數目進行計算，這種計算方式在單資料塊和多資料塊I/O之間並沒有區別。因此，為了向後相容，從Oracle　8i升級到Oracle 9i時，如果新的程式碼以單塊讀取時間為單位來報告時間，那麼典型的(輕量級的，基於索引的)OLTP查詢的成本所產生的數字並不會改變太多。

由這個公式還能夠得出，如果啟用了CPU成本計算，全表掃描的成本大約會上升到一個倍數(mreadtim/sreadtim)。因此具有CPU 成本計算的Oracle 9i將會比Oracle 8i更加傾向於使用索引訪問路徑，這是因為Oracle 9i能夠(準確地)意識到多塊讀取花費的時間要比單塊讀取花費的時間長。如果準備將Oracle 8i升級到Oracle 9i(或從Oracle 8i升級到Oracle 10g)，則從迴歸測試那天開始就一定要確認是否啟用了CPU成本計算—— 這可能會給您帶來驚喜。

當分析這個公式時的最後一個考慮是，並沒有明確地提到與花費在I/O上的時間有關的任何元件，這些I/O可能由歸併連線、雜湊連線或排序產生。在以上3種情況中，Oracle用直接路徑進行寫入和讀取，大小通常與正常的多塊讀取大小無關—— 因此，使用mreadtim或sreadtim似乎根本不合適。

1.3 變換和成本計算

在優化中有一個非常重要的方面常常被忽略，從而容易導致混亂，特別是當同時使用不同版本的Oracle時。在進行任何成本計算之前，Oracle可能會將SQL語句變換為等價的語句—— 可能並不是合法的SQL語句—— 併為等價的語句進行成本計算。

根據Oracle版本的不同，變換可以分為3類，分別為a)不可以進行，b)如果可能的話總是可以進行，和c)可以進行，但由於成本過高而放棄。例如，參見如下的SQL語句塊(全部指令碼view_merge_01.sql可以在本章的聯機程式碼包中找到)：

create or replace view avg_val_view

as

select

id_par,

avg(val) avg_val_t1

from t2

group by

      id_par

;

select

       t1.vc1,

avg_val_t1

from

t1,

avg_val_view

where

t1.vc2 = lpad(18,32)

and avg_val_view.id_par = t1.id_par

;

需要注意的是，avg_val_view是表t2的一個聚集檢視(aggregate view)。然後查詢將t1與產生該聚集檢視的列上的t2相連線。在這種情況下，Oracle可以使用以下兩種機制之一來得到正確的結果集：例項化這個聚集檢視然後將其與表t1進行連線；將檢視定義合併到查詢中並變換為其他形式。對於Oracle 9i，存在如下兩種可能的執行計劃：

Execution Plan (9.2.0.6 instantiated view)

----------------------------------------------------------

SELECT STATEMENT ptimizer=CHOOSE (Cost=15 Card=1 Bytes=95)

HASH JOIN (Cost=15 Card=1 Bytes=95)

TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=69)

VIEW OF 'AVG_VAL_VIEW' (Cost=12 Card=32 Bytes=832)

SORT (GROUP BY) (Cost=12 Card=32 Bytes=224)

TABLE ACCESS (FULL) OF 'T2' (Cost=5 Card=1024 Bytes=7168)

Execution Plan (9.2.0.6 merged view)

----------------------------------------------------------

SELECT STATEMENT ptimizer=CHOOSE (Cost=14 Card=23 Bytes=1909)

SORT (GROUP BY) (Cost=14 Card=23 Bytes=1909)

HASH JOIN (Cost=8 Card=32 Bytes=2656)

TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=76)

TABLE ACCESS (FULL) OF 'T2' (Cost=5 Card=1024 Bytes=7168)

從以上這兩個執行計劃中可以看出，上面的示例首先令Oracle對錶t2進行聚集並將其與表t1連線在一起，但是它也允許Oracle首先連線這兩個表然後進行聚集。合併檢視的“等價”程式碼類似如下所示：

select

t1.vc1,

avg(t2.val)

from

t1, t2

where

t1.vc2 = lpad(18,32)

and t2.id_par = t1.id_par

group by

t1.vc1, t1.id_par

;

那麼，這兩種方式哪一種更好，優化器又會選擇哪種執行計劃呢？這兩種方式哪一種更好的答案取決於資料的分佈。

●       如果存在一種非常有效的方式能夠從t1中讀取資料並傳給t2，且t2中針對t1的每一行都對應著兩行，向t2中的每一行所新增的額外資料量又非常小，那麼首先進行連線然後進行聚集可能是一種較好的方法。

●       如果不存在一種有效的方式能夠從t1中讀取資料並傳給t2，而且t2中針對t1的每一行都對應著很多行，向t2中所新增的額外資料量又非常大，那麼首先進行聚集然後進行連線則是一種較好的方法。

●       我們只能介紹在兩種極端情況下應該選擇哪一種方式，並不能給出當介於這兩種情況之間時究竟該選擇哪種方式。但是優化器卻能夠給出一個相當好的選擇。

優化器會選擇哪種執行計劃的答案取決於Oracle的版本。如果執行的是Oracle 8i，那麼Oracle將直接對檢視進行聚集然後執行連線—— 不考慮其他方式。如果執行的是Oracle 9i，Oracle將開啟檢視並連線然後進行聚集—— 也不考慮其他方式。如果執行的是Oracle 10g，Oracle將首先分別計算這兩種方式的成本，然後選擇成本較低的方式。可以通過執行指令碼檔案view_merge_01.sql，並設定event 10053(後面的章節將會介紹)來產生一個關於優化器成本計算的追蹤檔案(trace file)。

優化程式碼的演化

當使用不同版本的Oracle時，您會注意到某些機制的示例，這些機制能夠通過隱含引數在系統或者會話層次上啟用或者禁用；此外還存在許多機制，可以通過SQL層次上的提示來啟用或者禁用。

優化器程式碼演化的通用路徑如下所示：在首次釋出時禁用，通過未公開的引數進行隱藏；在第二次釋出時悄悄地啟用但並沒有計算成本；第三次釋出時正式啟用並計算成本。

在上面的示例中，優化器根據隱含引數_complex_view_merging的值來確定開啟聚集檢視和將其合併到查詢的其餘部分的先後順序，在Oracle 8i中，該引數的預設值為false，但是在Oracle 9i以後的版本中，該引數的預設值為true。可以通過修改該引數的值來強迫Oracle 8i執行復雜檢視合併—— 儘管您可能會發現在某些情況下還需要使用merge()提示來執行合併操作。同樣，也可以通過修改該引數的值以使得Oracle 9i和Oracle 10g不執行復雜檢視合併操作，但是，在某些情況下使用no_merge()提示來完成這一目的更為合理—— 前面演示的獲取較優的執行計劃的過程中就使用了該提示。

很長時間以來，優化器中還有很多功能能夠在對查詢進行優化之前執行某些其他的操作—— 謂詞推進(predicate pushing)、非巢狀子查詢(subquery unnesting)以及星型變換(star transformation)(這也許是查詢變換最生動的示例)。通過傳遞閉包(transitive closure)產生謂詞已經有多年的歷史了，從約束中產生謂詞也是多個不同Oracle版本中都存在的功能。所有這些(此處並沒有提及顯式的查詢重寫功能)，還有其他一些沒有注意到的操作，使得我們如果不對SQL進行深入細緻的分析，要準確地理解複雜SQL的含義將更加困難—— 可以通過10053 trace對SQL進行分析。

幸運的是，由於explain plan所提供的資訊已經足以表明變換已經發生，因此通常無需對SQL進行詳細分析。通常情況下可以採用一對提示或者通過檢查與優化器相關的引數來分析變換是強制的還是可選的，是否計算了成本以及可以進行何種程度上的控制。

1.4 所見未必即所得

當我們傾力分析基於成本的優化器的工作原理時，會遇到一個問題，即所見到的與所得到的並非總能保持一致。

操作複雜度可以分為以下3種不同的層次：

●       首先，執行計劃能夠在執行時實現優化器的意圖，並基於該模型計算成本。

●       其次，執行引擎啟動並執行優化器指定的模型—— 但是實際的機制並不總是與模型相一致(有時候模型並沒有很好地描述實際發生的事情)。

●       最後，在某些情況下，隨著輸入資料分佈的不同，執行模型所需的資源也有很大程度上的不同。

換句話說，優化器的執行計劃可能並不完全是執行時的執行路徑，不當的資料選擇可能會影響到執行時執行路徑的速度。

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