231
一次由查詢轉換引起的性能問題的分析
在優化器進行查詢轉換的時候，如果將內嵌視圖裏推入連接謂詞，視圖裏的結果集會更小，優化器就有可能會選擇Nested Loops Join 與 Index Range Scan 的方式加快數據的顯示。但如果內嵌視圖中存在GROUP BY，此時會發生什麼情況？在10g和11g裏麵，Oracle的處理方式有哪些不同？
為了解答以上問題，首先需要對SQL的基本語法與查詢轉換（QueryTransformation）的概念與原理有一定了解。
Oracle優化器可以分為下麵兩種：
Logical Optimizer：是對用戶編寫的SQL使其轉換成讓優化器更容易理解的方式的過程。也叫查詢轉換（QueryTransformation）。Physical Optimizer：對經過Logical Optimizer 轉換的SQL，計算成本（Cost），評估並選擇最優成本的執行計劃的過程。也叫查詢優化（Query Optimization）。
基於預估行數或成本選擇最優的執行計劃的技術，即物理優化器（PhysicalOptimizer） 相關的技術介紹有很多。比如，選擇索引還是選擇全表掃描、選擇Nested Loops Join 還是選擇Hash Join 等等都屬於這個範疇。使用提示（Hint）改變執行計劃也屬於這個範疇。但是，相對於查詢轉換（Query Transformation）相關技術的介紹，與其重要度相比相關技術的介紹就比較少。今天要說明的就是查詢轉換相關的問題。
這次的問題是內嵌視圖中存在GROUP BY時，連接條件謂詞無法推入到內嵌視圖裏。（該問題隻是在Oracle 10g裏的限製，Oracle 11g已經不存在此問題。）客戶正好使用的是10g係統，通過此次問題的分析，可以掌握查詢轉換問題的基本思路與方法。
生成如下表 T1、T2、T3，並插入數據，每個表插入10,000行數據。之後，分別對3個表創建第一個字段C1列的單列索引。最後，收集統計信息。
Oracle 版本是 10.2.0.1的情況。
下麵我們看一下內嵌視圖裏存在GROUPBY時，連接條件謂詞無法推入的情況。
以上執行計劃分析如下：
- ID：6，從這裏可以看到優化器進行視圖合並（View Merge）失敗，這是因為內嵌視圖裏存在ORDER BY的緣故。
- 連接謂詞"T1"."C2"=1，沒有進入到內嵌視圖內部，即謂詞推入失敗。優化器把V1視圖看成完全獨立的查詢塊（Query Block）。
- 所以，內嵌視圖內部對表T3進行了Full Table Scan，以及對其結果進行Hash Join。
優化器進行視圖合並失敗時，首先會嚐試進行連接條件謂詞推入（JoinPredicate Pushing）。上麵的SQL文本裏可以看到，T1、T2、V1之間存在連接，且存在"T1"."C2"=1，所以滿足謂詞推入的條件。如果，根據條件"T1"."C2"=1能過濾出很少的結果集，那麼謂詞推入的效果是相當明顯，進一步就可以選擇Nest Loops Join ，從而可以用最少的資源得到想要的結果集。
是什麼原因導致了謂詞推入失敗？
下麵我們看下在Oracle 11g的情況。
可以看到，執行計劃完全改變：
- ID：6，可以看到VIEW PUSHED PREDICAT，說明優化器進行視圖合並（View Merge）失敗。
- 但是，從VIEW PUSHED PREDICAT字麵可以理解，連接條件謂詞推入成功。
- ID：9的Predicate Information 裏可以看到access("C1"="T2"."C1")，這也進一步說明外麵的條件已經進入到視圖內部。
- 所以，因謂詞推入的緣故，對表T3出現了INDEX RANGE SCAN，以及對T3的結果集的處理外部選擇了NESTED LOOPS JOIN。
這個是因為Oracle 版本的升級解決了老版本優化器的一些限製的典型案例。這種新功能增加往往會帶來隱含參數的增加。此處也不例外，請記住這個隱含參數_optimizer_extend_jppd_view_types。如下，可以看到這個隱含參數。
我們可以利用提示（Hint）關閉這個參數，看看會產生什麼結果。
可以看到，關閉相應隱含參數的時候，執行計劃回到了Oracle 10g時候的樣子，即沒有進行連接條件的謂詞推入。
可以親眼確認查詢轉換過程的方法是使用10053的事件。通過10053事件我們推測一下在Oracle10g和Oracle11g裏區別是什麼。
首先，可以通過Legend了解到產生了哪種查詢轉換。
可以看到，Oracle 11g裏比Oracle 10g 使用了更多的查詢轉換。Oracle每次的版本更新都會帶來查詢轉換領域的不斷更新。
內嵌視圖的查詢塊（SEL$2）裏存在ORDER BY語句，所以視圖合並失敗。這個是兩個版本都相同的地方。但是，有趣的是使用的方式不同，Oracle 10g裏使用了CVM（Complex ViewMerge），Oracle11g裏使用的是SVM（Simple Viewer Merge），說明因版本的升級Oracle裏視圖合並的基準改變了。
下麵的信息可以明確的看到，Oracle 10g裏嚐試把主查詢塊（SEL$1）裏存在的連接謂詞（Join Predicate）推入到內嵌視圖的查詢塊（SEL$2）裏，但是因為GROUP BY 語句失敗了。
但是，從下麵信息中可以看到，在Oracle 11g裏連接謂詞推入（Join Predicate Push）成功了。這時，通過CBQT（Cost Based Query Transformation）即基於代價的查詢轉換計算成本（Cost Based），之後判斷是否使用連接謂詞推入。
從上麵的信息中可以看到，連接謂詞推入成功。
這個問題雖然看起來很簡單， 但是裏麵使用到的知識點非常得多。對查詢轉換沒有一個基本的理解與認識，對複製SQL語句的調優，會覺得比較困難。下麵請再看一個例子。
對存在UNION ALL 語句的視圖進行連接謂詞推入，也是一個比較常見的情況。
首先，看一個連接謂詞推入成功的例子。
可以看到，執行計劃裏的信息如下：
- ID：3，可以看到包含UNION ALL 的內嵌視圖優化器嚐試進行視圖合並（View Merge）失敗。
- ID：4，可以看到從UNION ALL PUSHED PREDICATE，連接條件謂詞推入成功。
- 所以，因謂詞推入的成功緣故，ID：5和6出現了不是FullTable Scan的 Index RangeScan，出現了更加優越的執行計劃。
也就是說，內嵌視圖外麵的條件"C1"="T3"."C1"推入到UNION ALL視圖內部，從而產生了更加優越的執行計劃。因為根據條件T3.C2 BETWEEN 1 AND 5 會對T3表產生少量的結果集。所以，後麵跟著出現了 Nested Loops Join的情況，這個的前提是連接條件"C1"="T3"."C1"能夠推入到視圖裏，並且選擇了Index Range Scan的時候。
如果，使用提示 NO_PUSH_PRED ，防止連接條件謂詞推入的發生會怎麼樣？
下麵請再看一下：
從執行計劃中可以看到，沒有對UNOIN ALL 視圖的謂詞推入。所以，在ID：5和6選擇了非 IndexRange Scan的 Full TableScan。之後，外麵也是選擇了非NestedLoops Join的 Hash Join。
是否產生連接謂詞推入，要看其成本（Cost）是多少，決定權在CBQT（Cost Based Query Transformation）。如果，優化器判斷連接謂詞推入的成本更高，即使能做謂詞推入也不會選擇謂詞推入。下麵我們再看一個例子。把條件換成T3.C2 BETWEEN 1 AND 100 的時候，被選擇的行數會增加，有可能會判斷出Nested Loops Join的成本會更高。所以，連接謂詞推入有可能不會出現。
此次，還可以繼續做幾個有趣的測試。一般提示FIRST_ROWS是為了讓執行計劃產生能夠快速顯示頭幾行的執行計劃而使用。所以，一般內部會傾向於選擇Nested Loops Join而非Hash Join，也傾向於選擇Index Range Scan 而非Table Full Scan。所以，上麵的例子如果加上FIRST_ROWS提示的時候，會有很大幾率選擇連接謂詞推入。結果也確實如下所示。
那麼，如果使用提示FIRST_ROWS(1)會如何呢？
FIRST_ROWS（N）提示與FIRST_ROWS不同，是要求快速顯示頭N行時，產生的執行計劃。與FIRST_ROWS提示不同，FIRST_ROWS（N）提示是基於成本的，而非基於規則，即計算N行成本以後選擇執行計劃。所以，與FIRST_ROWS相比更靈活，但也更不好預測其執行計劃的結果。下麵可以看到，選擇了一個全新的執行計劃。
如果，正確理解了連接謂詞推入的原理的話，可以使用如下提示LEADING、USE_NL得到相同的結果。選擇的路線（PUSH_PRED 與 LEADING + USE_NL）不一樣，但是目的地（因謂詞推入而選擇Index Range Scan與Nested Loops Join）是相同的。
我們看了幾個在查詢轉換（Query Transformation）中非常典型的連接謂詞推入的例子。希望在實踐中不斷進行嚐試，來加深對查詢轉換的理解。優化過程中，經常會問自己為什麼不選擇索引呢？今後，希望再加上兩個問題問自己，為什麼不進行謂詞推入呢？為什麼不進行視圖合並呢？對查詢轉換的認識越深，對執行計劃的理解也會變得越來越深，對SQL優化也會變得加更體係化。
原文發布時間為：2017-11-21
本文作者：郭成日
本文來自雲棲社區合作夥伴“數據和雲”，了解相關信息可以關注“數據和雲”微信公眾號
最後更新：2017-11-23 02:03:52