【恩墨學院】一次由查詢轉換引起的效能問題的分析

【恩墨學院】一次由查詢轉換引起的效能問題的分析

作者介紹

郭成日  雲和恩墨北區技術工程師

專注於SQL稽核和最佳化相關工作。曾經服務的客戶涉及金融保險、電信運營商、政府、生產製造等行業。

在最佳化器進行查詢轉換的時候，如果將內嵌檢視裡推入連線謂詞，檢視裡的結果集會更小，最佳化器就有可能會選擇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列的單列索引。最後，收集統計資訊。

案列1：GROUP BY 與 Join Predicate Pushing

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 使用了更多的查詢轉換。每次的版本更新都會帶來查詢轉換領域的不斷更新。

內嵌檢視的查詢塊（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語句的調優，會覺得比較困難。下面請再看一個例子。

案列2：UNION ALL 與 Join Predicate Pushing

對存在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最佳化也會變得加更體系化。

恩墨學院隸屬於雲和恩墨（北京）資訊科技有限公司，致力於提供專業高水準的與大資料培訓服務，挖掘培養大資料與資料庫人才。恩墨學院提供包括個人實戰技能培訓、個人認證培訓、企業內訓在內的全方位大資料和資料庫技術培訓。ACE級別超強師資，配備專業實驗室，沉浸式學習與訓練，專業實驗室、配備專業助教指導訓練。能迅速融入專家圈子，業內資源豐富，迅速積累職場人脈。課程包括：班、Oracle 、Oracle OCP考試等。