Oracle性能診斷藝術-第7章

第7章

SQL優化技術

每

當查詢優化器（query optimizer）不能自動産生高效的執行計劃時，就需要一些手工的優化技術。為此，Oracle提供了多項技術，表7-1對它們進行了彙總。本章的目的不僅是要詳述這些技術，還将解釋每一項技術能做什麼，以及在哪些情況下可以利用它們。為了從衆多技術中做出選擇，絕對有必要先問自己如下三個基礎的問題。

q   這條SQL語句是已知并且确定不變的嗎？

q   即将采用的措施會影響到單個會話（甚至整個系統）的某一條SQL語句還是全部SQL語句？

q   有可能改變這條SQL語句嗎？

我來解釋一下為什麼這三個問題如此重要。首先，有時一些SQL語句在運作時才被生成并且常常是随每次執行而變化，它們幾乎是不可知的。在另外一些情況下，查詢優化器無法正确地處理被大量SQL語句所使用的特定結構（例如，WHERE子句中的一個無法使用索引的限制條件）。在這些情況下，你就要采用一些技術在會話級或系統級，而不是SQL語句級來解決問題。這個事實導緻了兩個主要的問題。一方面，就像在表7-1中概括的那樣，有些技術隻能夠對某些特定的SQL語句使用，而幾乎不可能适用于會話級和系統級。另一方面，在第5章中已經解釋過，隻要資料庫沒什麼問題，而且查詢優化器的配置也能正确執行，你通常隻需針對一小部分的SQL語句進行優化。是以，要避免使用那些會影響查詢優化器能夠自動為其提供高效執行計劃的SQL語句的技術。

其次，無論何時，處理一個無法控制SQL語句的應用程式（因為代碼如同在一個封裝好的應用程式中那樣不可得；或是因為SQL語句是在運作時産生的），你都無法采用需要改變代碼的技術。總的說來，你的選擇通常是受限的。

表7-1　SQL調優技術及其影響

技　　術	系統級	會話級	SQL語句級	可用性
調整通路結構	ü	所有版本
調整SQL語句	ü*	所有版本
提示	ü*	所有版本
調整執行環境	ü	ü*	所有版本
SQL性能概要	ü	從10g†開始
存儲綱要	ü	所有版本
SQL計劃基線	ü	從11g‡開始

*          為了使用這項技術需要調整SQL語句。

†          需要調優包，是以需要企業版。

‡          需要企業版。

本章的目的不是講述如何找出一個給定SQL語句的最優執行計劃，比如，解釋在哪種情況下哪個特殊的通路路徑或連接配接方法應該被采用——這些分析放在第四部分。本章的唯一目标是描述可用的SQL優化技術。值得一提的是，除了改變通路結構和改變運作環境外，所有的SQL優化技術都是基于這樣一個事實：由于查詢優化器自身的局限導緻它不能識别出一個高效執行計劃。當然，前提是所有配置都已正常運作。在本章中，将假設所有初始化參數都已正确配置，并且所有必要的系統和對象統計資訊都已就緒。

接下來，描述SQL優化技術的每一節都以相同的方式展現：首先是一個簡短的介紹，接着叙述這項技術的運作機制以及何時應該使用它。最後，每一節的結尾都将介紹該項技術的缺陷和謬誤。

7.1　改變通路結構

這項技術不和任何特性相聯系，而僅僅是基于一個事實，SQL語句的響應時間往往不僅取決于這些資料是如何存儲的，而且也取決于這些資料是如何通路的。

l        7.1.1　運作機制

當你質疑一條SQL語句的性能時，首先需要做的是檢查目前的通路結構，基于從資料字典中獲得的資訊，回答下面幾個問題。

q   語句中涉及的表的結構類型是什麼？是堆表、索引組織表，還是外部表？以及表是否存儲在聚簇中？

q   包含所需資料的物化視圖可用嗎？

q   在表、聚簇和物化視圖中存在哪些索引？這些索引建立在哪些字段上，這些字段的順序又如何？

q   所有這些資料段[①]是如何被分區的？

接下來需要評估，為了高效地處理你正在調優的這條SQL語句，目前可用的通路結構是否合适。比如，在分析過程中，你或許會發現再加一個額外的索引，對支援WHERE子句高效運作是非常必要的。假如你正在研究下面這條查詢語句的性能：

通常，查詢優化器會考慮采用下面的兩種執行計劃來運作它。第一種采用全表掃描來通路，第二種通過一個索引來通路。當然，後者必須是在索引存在的前提下。

關于這個話題我們不在此深入下去，因為将在第四部分仔細探讨，包括詳細分析何時以及如何使用這些不同的通路結構。此刻，唯一重要的是，認識到這是一項基本的SQL優化技術。

l        7.1.2　何時使用

如果沒有采用必要的通路結構，要優化一條SQL語句并使其高效執行幾乎是不可能的。是以，隻要你能改變通路結構，就應該考慮使用這項技術。不幸的是，并不是每次都有這樣的機會，比如，你正在使用一個封裝的應用程式，而賣家又沒有提供對改變通路結構的支援。

l        7.1.3　缺陷和謬誤

當打算改變通路結構時，最重要的是仔細考慮可能的副作用。一般來說，每個改變通路結構的做法都是一把雙刃劍。事實上，采取這種調優所帶來的影響不太可能僅僅作用于一條單一的SQL語句，很難找到不是這種情形的例子。例如，如同上例的第二個通路結構中那樣，添加了索引，那麼就必須考慮，索引會減慢對索引表進行INSERT和DELETE操作的速度，以及對索引列進行更新（UPDATE）操作的速度。你還需要檢查是否有足夠的空間可用于添加通路結構。綜合考慮，在改變通路結構之前權衡利弊是十分必要的。

7.2　修改SQL語句

SQL是一種非常強大且靈活的查詢語言。常常可以通過各種不同的方法送出一個完全相同的請求。對開發者來說，這是非常有用的。然而，對查詢優化器來說，要為所有不同種類的SQL語句都提供高效的執行計劃是一個真正的挑戰。記住，靈活是性能之敵！

l        7.2.1　運作機制

假設你正用scott模式來執行一個查詢以找出所有還沒有員工的部門。在depts_wo_emps.sql這個腳本中有四條SQL語句，都能傳回你需要的資訊：

這些SQL語句的目的是相同的，它們傳回的結果也完全一樣。是以，你可能希望查詢優化器也都能夠提供相同的執行計劃。然而，事實并非如此。實際上，隻有第二和第四條語句使用了相同的執行計劃，其他的都不盡相同。請注意，這些執行計劃是在Oracle 10gR2中産生的，在其他版本中可能會有差異。

基本上，雖然通路資料的方法總是相同的，但對資料進行組合以産生結果集的方法卻大相徑庭。在這個特殊的例子中，兩張表資料量都很小，可能讓你注意不到這三種執行計劃所導緻的性能上的真實差異。但是，如果處理的表很大，就不是這個結果了。一般來講，隻要處理大批量的資料，執行計劃上的任何微小差異都有可能導緻響應時間和資源利用發生巨大的改變。

認識到完全一樣的資料可以用多種不同的方法抽取是此處的關鍵點。無論何時，隻要你準備對一條SQL語句進行調優，都要問自己是否有等價的SQL語句存在。如果有，仔細比較它們，進而确定誰能提供最佳的性能。

l        7.2.2　何時使用

隻要能夠把SQL語句修改得更好，就應該考慮使用這項技術，我實在想不出不這麼做的理由。

l        7.2.3　缺陷和謬誤

SQL語句由代碼組成。編寫代碼的首要原則是讓其可維護。要做到這一點，首先意味着代碼需要有可讀性和簡潔性。不幸的是，對于SQL語言來說由于上文中提及的原因，最簡潔可讀的寫法，并不總能帶來最高效的執行計劃。是以，在某些情況下你可能必須為了性能而犧牲可讀和簡潔，盡管隻是在真正必要和有益的情況下才需要這樣做。

7.3　提示

根據韋氏線上詞典的解釋，提示（hint）是指間接的或概要性的建議。根據Oracle的說法，提示的定義則稍有不同。簡單來說，提示是為了影響查詢優化器的決定而添加到SQL語句中的訓示。換句話說，提示是這樣一種東西，它對行為産生推動，而不僅僅是給出建議。在我看來，Oracle選擇“hint”一詞來對此特性命名似乎不是很恰當。不管怎樣，名稱并不重要，提示能為你做什麼卻很重要，隻是不要讓名稱誤導了你。

注意   因為提示僅是一個訓示，是以并不意味着查詢優化器總會利用它。但是，換一個角度來看，不能僅僅因為查詢優化器沒有采納一個具體的提示，就認為提示僅僅是一個建議。在接下來的描述中将看到這樣一些例子，它們使用了幾乎無關或無效的提示。是以，根本沒有影響到查詢優化器産生的執行計劃。

l        7.3.1　運作機制

接下來的小節描述提示是什麼，有哪些種類，以及如何使用它們。在詳細閱讀後面的内容之前，有一件重要的事情需要注意，就是使用提示也許不像你想象得那麼容易。實際上，在開發中經常見到有人錯誤地使用它。

1．提示是什麼

當優化一條SQL語句的時候，查詢優化器可能不得不考慮大量不同的執行計劃。理論上，它需要考慮所有可能的執行計劃。而現實中，除了一些簡單的SQL語句外，為了使優化耗時保持在适度的範圍内，考慮太多的組合方案是不可行的。随之而來的結果是，查詢優化器未經檢驗就較早地排除了一部分執行計劃。當然，完全忽略這部分計劃的決定可能帶來嚴重後果，而且查詢優化器的可信度也會遭到質疑。

無論何時，當指定了一個提示，你的目标是減少查詢優化器要評估的執行計劃的數目。基本上，通過使用提示你可以告訴優化器，在優化某條指定的SQL語句時，應該考慮哪些操作，哪些則不必考慮。例如，假設優化器将為下面這條SQL語句産生一個執行計劃。

如果emp表是堆表（heap table），并且列empno為索引列，那麼查詢優化器将至少考慮兩種執行計劃。第一種是通過一個全表掃描來讀入emp表全部記錄：

第二種是基于WHERE子句中的謂詞（empno=7788）來進行索引查找，再通過索引傳回的rowid來通路表。

在這個例子中，為了控制查詢優化器給出的執行計劃，可以添加一個提示來指定使用全表掃描還是索引掃描來進行通路。你需要了解的最重要的一點是，不能夠告訴查詢優化器“我想在emp表上進行全表掃描，你幫我找一個滿足我需求的執行計劃”。但你可以這麼說：“如果你需要在全表掃描和索引掃描間做出選擇，那麼就選全表掃描吧。”這是一個很微小但卻是實質性的差異。當查詢優化器要在多個執行計劃間做出選擇的時候，提示可以幫你影響它的決定。

為了進一步強調這個要點，讓我們基于圖7-1中的決策樹舉一個例子。請注意，即使這個查詢優化器以決策樹的形式運作，這也是一個具有普遍性的例子，它并不和Oracle直接相關。在圖7-1中，目的是從根節點（1）開始向下延伸，直到葉節點（111～123）為止。換句話說，目标是選擇一條從A點到B點的路徑。假設，因為某種原因，必須經過節點122。為此，用Oracle的說法，可以添加兩個提示來剪去從節點12到節點121和123之間的分枝。這樣，隻剩下一條從節點12到節點122的路。但是，這還不足以保證路徑一定經過節點122。實際上，如果節點1穿過了節點11而不是節點12，上面的兩個提示将毫無作用。是以，為了保證經過節點122，你需要添加額外的提示來剪去從節點1到節點11間的分枝。

圖7-1　修剪決策樹

一些類似的事情也會發生在查詢優化器的身上。實際上，隻有當提示緻力于讓查詢優化器接受一個不得不采納的決定時，它才會被評估。提示要不多也不少。因為一旦你指定了某個提示，就可能被迫添加更多的提示來確定其工作。并且在實踐中，随着産生的執行計劃的複雜性的增加，要讓找到的提示能帶來期望中的執行計劃會變得越來越困難。

2．指定提示

提示是Oracle為了不破壞和其他資料庫引擎之間對SQL語句的相容性而提供的一種擴充功能。Oracle決定把提示當作一種特殊的注釋來添加。它和注釋之間的差別如下。

q   提示必須緊跟着DELETE、INSERT、MERGE或UPDATE關鍵字。換句話說，提示不能像注釋那樣在SQL語句内随處可加。

q   在注釋分隔符之後的第一個字元必須是加号（+）。

提示中的文法錯誤不會報錯，如果解析器不能解析它，就會把它看作是一個真正的注釋。也可以把注釋和提示混在一起寫。下面是兩個例子，展示了如何對前一小節中讨論過的emp表的查詢強制采用全表掃描：

3．提示的分類

關于提示分類有多種方法（或觀點），就我個人而言，我喜歡使用下面的分法。

q   初始化參數提示可以覆寫在系統級或會話級定義的部分初始化參數。我将下列提示歸于此類：all_rows，cursor_sharing_exact，dynamic_sampling，first_rows，gather_plan_statistics，no_cpu_costing，optimizer_features_enable，opt_param，(no_)result_cache，以及rule。我将在7.4節講述這些提示，而gather_plan_statistics提示則放在第6章。請注意，這些提示被指定時，它們都會覆寫在執行個體級或會話級設定的值。

q   查詢轉化提示在邏輯優化階段控制查詢轉化技術的使用。我将下列提示歸于此類：(no_)elimi- nate_join，no_expand，(no_)merge，(no_)outer_join_to_inner，(no_)push_pred，(no_)push_ subq，no_query_transformation，(no_)rewrite，(no_)unnest，no_xmlindex_rewrite，no_xml_ query_rewrite，以及use_concat。我将在本章的稍後介紹其中部分提示，其他放在第10、11章叙述。

q   通路路徑提示控制通路資料的方法（例如，是否使用索引）。我将下列提示歸于此類：cluster，full，hash，(no_)index，index_asc，index_combine，index_desc，(no_)index_ffs，index_join，(no_)index_ss，index_ss_asc，以及index_ss_desc。我将在第9章探讨通路方法時，一起介紹這部分提示。

q   連接配接提示不僅控制連接配接的方法，而且控制連接配接表的順序。我将下列提示歸于此類：leading，(no_)nlj_batching，ordered，(no_)star_transformation，(no_)swap_join_inputs，(no_)use_hash，(no_)use_merge，use_merge_cartesian，(no_)use_nl，以及use_nl_with_index。我将在第10章中把它們和連接配接方法一起叙述。

q   并行處理提示控制如何使用并行處理。我将下列提示歸于此類：(no_)parallel，(no_)parallel_ index，pq_distribute，以及(no_)px_join_filter。我将在第11章中随并行處理一起講述它們。而關于提示pq_distribute的一個可能用法，将放到第10章中和partition-wise連接配接一起說明。

q   其他提示控制沒有歸到前幾種分類的其他一些特性的使用。我将下列提示歸于此類：(no_)append，(no_)cache，driving_site，model_min_analysis，(no_)monitor，以及qb_name。我将在本章稍後介紹qb_name提示，其餘的放到第11章。

雖然我在本書中叙述和展示了大量關于這些提示的例子，但這仍不足以使本書成為關于提示的萬能參考或文法大全。可以在SQL Reference手冊的第2章中找到那些内容。

值得一提的是，有許多的提示（它們以no_為字首）可以禁用某個操作或特性。這是很令人欣喜的，因為很多時候禁用一項操作或特性比啟用它們更容易。

剛才列出的提示還不齊全。事實上，還有其他一些提示并沒有在文檔中提及，它們隻在Oracle内部使用。你将在7.5節中看到一些案例。從Oracle 11g以後，你可以查詢v$sql_hint視圖來得到一個較齊全的清單。

4．提示的有效域

簡單的SQL語句隻有一個單獨的查詢塊。當使用視圖或類似于子查詢、内聯視圖（in-line view）、集合操作符這樣的結構時，就會出現多個查詢塊。譬如，下面這個查詢有兩個查詢塊（我使用分解的子查詢替代一個真正的視圖隻是出于舉例的考慮）。第一個是引用了dept表的主查詢，第二個是引用了emp表的子查詢：

初始化參數提示對整個SQL語句都有效。所有其他的提示僅僅對單個查詢塊起作用。僅僅對單個查詢塊起作用的提示，必須在它控制的查詢塊内指定。例如，如果你想對上面查詢中的兩個表都指定通路路徑提示，則一個提示必須被加到主查詢上，另外一個要加到子查詢上。這兩個提示的有效域都被嚴格限制在它們所在的查詢塊内。

這個規則也有例外，即全局提示。通過它們，可以使用點号（.）來引用包含在其他查詢塊（假設這些塊已被命名）中的對象。比如，在下面這條SQL語句中，主查詢就包括了一個為子查詢準備的提示。請注意在引用時，子查詢名是如何被使用的。

全局提示的文法可以支援兩層以上的引用（比如，在一個視圖中引用另一個視圖）。對象間必須用點号（.）分隔。

既然WHERE子句中的子查詢是沒有命名的，它們的對象就不能被全局提示引用。為了解決這個問題，在Oracle 10g中，有另一種方法可以達到相同的目的。事實上，大多數提示都可以接受一個參數，指明它在哪個查詢塊中有效。通過這個方法，提示需要在SQL語句的開始就明确地給出，且隻需要指明它所作用的查詢塊。為了配合這個動作，不僅查詢優化器可以給每個查詢塊生成一個查詢塊名，而且還可以用提示qb_name手動為每個查詢塊命名。舉例，在下面這個查詢中，這兩個查詢塊分别叫做main和sq。然後，在提示full裡，通過字首“at”符号（@）來引用它。注意在主查詢中是如何指明對子查詢中表emp的通路路徑的。

上面這個例子展示了如何給查詢塊命名。現在來看如何使用查詢優化器産生的命名。首先，必須知道它們叫什麼。為此，可以使用SQL語句EXPLAIN PLAN和dbms_xplan包，就像下面這個例子中展示的這樣。注意傳遞給函數display的alias選項是為了保證查詢塊名和别名被輸出。

系統産生的查詢塊名是由一個字首加一個由字母和數字組成的字元串構成。字首是基于子查詢中操作的類型。表7-2對此做了彙總。字元串是一個查詢塊的編号，它是按照SQL語句解析階段查詢塊出現的位置（從左到右）來進行的。在上面的例子中，主查詢塊被命名為SEL$2，子查詢塊被命名為SEL$1。

表7-2　查詢塊名的字首

前　　綴	用于語句
CRI$	CREATE INDEX語句
DEL$	DELETE語句
INS$	INSERT語句
MISC$	多類SQL語句，比如LOCK TABLE
MRG$	MERGE語句
SEL$	SELECT語句
SET$	集合操作，比如UNION和MINUS
UPD$	UPDATE語句

就像下面展示的這樣，使用系統産生的查詢塊名的方法和使用使用者自定義查詢塊名的方法沒什麼不同。

還要做最後一個說明，關于查詢轉換階段産生的查詢塊的命名。由于在解析階段SQL語句中還不包含它們，是以也就不能像其他部分那樣給它們編号。在這種情況下，查詢優化器為它們生成一個八個字元的哈希值。下面這個例子說明了這種情況。這裡，系統産生的查詢塊名為SEL$5DA710D3。

在上面的輸出中，可以注意到一種有意思的情況：當這樣的轉換發生時，執行計劃中的某些行有兩個查詢塊名。它們都可以在提示中使用。然而，從查詢優化器的角度來說，轉換之後生成的查詢塊名（這裡是SEL$5DA710D3）隻當有一個完全相同的轉換發生時，才會再次被用到。

l        7.3.2　何時使用

提示有雙重目的。第一，當查詢優化器不能自動産生一個高效的執行計劃時，它可以作為過渡。那時，可以用它得到一個更高效的執行計劃。需要重點強調的是，提示能解一時之圍，但不要把它當作一個長期的解決方案。不過在有些情況下，它卻也是解決問題的唯一途徑。第二，提示能産生可替代的執行計劃，是以它對評估查詢優化器的決定很有效。這樣，你就可以用它做一些假設分析。

l        7.3.3　缺陷和謬誤

每當你希望通過通路路徑提示（access path hint）、連接配接提示（join hint）或并行執行提示（parallel processing hint）産生一個特定的執行計劃時，必須很小心地使用足夠多的提示來達到執行計劃的穩定性。這裡的穩定性是指即使對象統計資訊發生改變，甚至在某種程度上通路結構也發生了改變時，執行計劃也保持不變。為了獲得特定的執行計劃，通常不僅要給SQL語句中的每個表加上通路路徑提示，同時還要再加上幾個連接配接提示來控制連接配接的方法和順序。注意，其他類型的提示（比如，初始化參數提示和查詢轉換提示）通常并不存在這樣的問題。

執行SQL語句時，解析器會檢查提示的文法。盡管這樣做了，但是提示出現文法錯誤時仍不會抛出錯誤。這暗示着解析器将這些錯誤的提示看作是注釋了。從一方面看，如果這是打字錯誤引起的就很令人讨厭。從另一方面看，這樣又是有好處的，不會因為改變了一個提示中引用的通路結構（比如，提示index會引用一個索引名）或更新到一個新的資料庫版本，而破壞已部署好的應用程式。是以，我很歡迎一種确認提示有效性的方法。比如，通過EXPLAIN PLAN語句，它用簡單易行的方式為可能的錯誤提供了警告（比如，在dbms_xplan的輸出中有一個新的note）。其他我所知道的能夠部分達到此目的的方法隻有10132事件。事實上，在Oracle 10g中，此事件産生的輸出文檔的末尾有一部分内容專門講提示。通過它可以檢查兩件事情。第一，每一個用到的提示都會被列出來，如果哪個提示漏了，意味着它沒有被識别。第二，檢查是否有一些資訊指明出現了提示錯誤（如果出錯，err值将大于0）。注意下面的輸出，兩個初始化參數提示是互相沖突的。

當使用這個方法時要十分小心，那些文法正确但引用對象錯誤的提示是不會被報告出錯的。是以，這不是一個可以讓人高枕無憂的權威性檢查。

在使用提示時最易犯的一個錯誤與表的别名相關。正确的規則是，當在提示中使用表時，隻要表有别名就應該使用别名而不是表名。在下面這個例子中，可以看到如何給表emp添加别名（e）的。此時，當提示full通過表名引用表時，提示就無效了。注意在第一個例子中，索引掃描是如何替代了原先期望的全表掃描的：

還有一個應該被檢查但卻經常被遺忘的事情是資料庫更新對提示的影響。雖然提示的作用是在查詢優化器不能自動産生高效執行計劃時提供便捷的解決方法，但是它的影響卻取決于查詢優化器所使用的決策樹（參考圖7-1）。無論何時，當使用了提示的查詢語句在另一個版本的資料庫（是以，也就是在另一個查詢優化器版本）執行時，它們都需要進行仔細地校驗。換句話說，當校驗一個遷到新版本的應用程式時，最佳的做法是重新校驗和測試所有包含提示的SQL語句。

因為視圖可能在不同的環境中使用，通常不推薦在視圖中使用提示。如果你不得不在視圖中添加提示，需要確定所用的提示對所在的子產品都是合理的。