<form id="tznrh"><form id="tznrh"><th id="tznrh"></th></form></form>

            第9部分中我們看了一個簡單的嵌套循環連接的例子,執行計劃中基本解釋數字的方法也因為優化器使用的算法對于成本的計算與運行時執行路徑的不相符而出現問題。在本部分中,我們將查看一個示例,其中有些數字是由猜測生成的,有些是由計劃中不可見的估計生成的。


            Example


            我創建了一張自定義的emp表,包括6個部門總共20000個員工,我打算使用SQL語句列出每個部門里超過平均工資的員工。下面是SQL語句以及執行計劃。


            explain plan for select outer.* from emp outer where outer.sal > ( select /*+ no_unnest */ avg(inner.sal)
              from emp inner where inner.dept_no = outer.dept_no
            )
            ; select * from table(dbms_xplan.display); ---------------------------------------------------------------------------- | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT    |      |   167 | 12024 |   334  (12)| 00:00:02 |
            |*  1 |  FILTER             |      |       |       |            |          |
            |   2 |   TABLE ACCESS FULL | EMP  | 20000 |  1406K|    49  (15)| 00:00:01 |
            |   3 |   SORT AGGREGATE    |      |     1 |     8 |            |          |
            |*  4 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    48  (13)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */ AVG("INNER"."SAL")
                           FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
               4 - filter("INNER"."DEPT_NO"=:B1)


            你注意到了我在語句中使用了/*+no_unnest*/ hint,這不是一個高效率的執行路徑,我之所以使用它,是因為它將生成一個特定的執行計劃,該計劃將演示我想討論的幾個要點。

            執行計劃的操作順序是2,4,3,1,0:第2行對EMP表做全表掃描,并把結果集傳遞給第1行,原則上第1行會對傳遞來的結果集中的每一行進行調用一次第3行的操作;第3行會調用第4行做表掃描,它會提供每個部門里所有員工的工資,第3行根據結果集計算平均工資并將包含單行的結果集傳遞給第1行,第1行比較工資的平均值與當前行,如果大于則會輸出到客戶端。


            Filling the gaps


            你馬上可以察覺到執行計劃中存在一些空缺。Filter操作并沒有任何數字計量方面的信息,但是這個例子中我們也可以接受,因為它是第0行select操作唯一的子操作,所以它的值應該跟第0行的值相匹配。

            但是第3行的sort aggregate操作沒有成本,這樣我們就不知道優化器是如何為第1行計算成本的。因為sort aggregate操作僅僅是運行聚合運算,所以我們可以假設它的增量成本是很小的(事實上,這個查詢的10053跟蹤文件似乎表明,優化器甚至不需要做任何與排序相關的計算,這可能解釋了為什么這一行是空的)。所以我們假設第3行的成本跟它的唯一子操作的成本是一樣的,也是48。


            Guessing execution counts


            我們怎么證明第1行的cost是334?在允許四舍五入的誤差情況下(10053跟蹤文件報告47.61和48.75時分別為48,49),簡單的計算為:6*48+49=337,這跟結果比較接近,我們也可以推斷優化器假設這個子查詢會執行6次。


            注意:第2行跟第4行中同樣是對emp表進行掃描,為什么會有不一樣的成本?因為它們處理不同的列,第2行處理的列多于第4行,會導致消耗更多的CPU。對6次執行的假設將我們帶進了一個解釋執行計劃很重要的階段—優化器也不知道子查詢可能會執行幾次。選擇6到20000之間任意的數字,我可以構造一個數據集(在6個部門的基礎上),使查詢運行那么多次。實際上,在這個例子中,子查詢很可能就會執行6次,但是通過一些額外的實驗,生成隨機的部門代碼,你會最終會得到執行的次數是幾千次的例子。優化器選擇6這個倍數,是因為它通過對象的信息知道表里有6個部門,計算的理由是一種稱為標量子查詢緩存的機制。


            所以在評估執行計劃是需要注意這點:執行計劃是用來告訴你Oracle在運行時會執行哪些步驟,它并不會告訴你這些步驟執行了多少次,這些執行的次數對于查詢的執行有著重要的影響。


            Guessing Volume

            
            
            在子查詢中引入/*+push_shbq*/ hint,我們可以使用相同的例子來證明評估執行計劃時另一個很重要的點。下面的該執行計劃 ---------------------------------------------------------------------------- | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT    |      |  1000 | 72000 |    96  (13)| 00:00:01 |
            |*  1 |  TABLE ACCESS FULL  | EMP  |  1000 | 72000 |    48  (13)| 00:00:01 |
            |   2 |   SORT AGGREGATE    |      |     1 |     8 |            |          |
            |*  3 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    48  (13)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("OUTER"."SAL"> (SELECT /*+ PUSH_SUBQ NO_UNNEST */        AVG("INNER"."SAL") FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
               3 - filter("INNER"."DEPT_NO"=:B1)


            推入子查詢的目的是想讓它盡可能早的執行,但在我們的情況下不起作用,因為只有一個特定的時刻,子查詢才可以運行,但即使如此,它將導致優化器采取不同的路線執行語句。在運行時,我們的查詢所做的工作量不會改變,而且查詢返回的數據量也不會改變,但是我們來看看這個執行計劃中的數據:優化器預測會獲得1000行而不是167行,總成本是96而不是344.通過cost值,我們可以推斷優化器會預估將執行1次子查詢而不是6次(我們同樣也能看到,第1行中全表掃描預測行數為1000行而不是20000行,但這是優化器在執行子查詢之后對行數的預測,而之前的計劃是在執行子查詢之前預測行數)。

            具體的細節不是最重要的,重要的是我們可以很清楚的看到,不同的優化器代碼路徑會對cost以及數據集大小產生不同的預測。其中至少會有一個預測是錯誤的。實際數據量的變化會導致處理數據所需的實際工作量的變化,而且我們可以很容易地找到同一查詢的不同執行計劃預測不同數據集數量的情況,因此我們如何能夠確信任何預測都將與最終發生的工作相一致?這個是第二個我們在評估執行計劃時需要注意的點—對于基數的預估有很多是基于猜測的,基數對于執行計劃的效率有著很重要的作用。優化器會告訴你將要執行掃描,但是只會猜測它將獲取的數據集的大小,以及處理這些數據集的成本。


            Conclusion


            只看一個(精心設計的)例子,我們就可以看到優化器在多大程度上可以產生甚至不自洽的預測。這應該被視為一個警告,你在預測的執行計劃中看到的基數(行)、成本和時間不應該被視為可靠的預測。


            你從優化器的預測中得到的是一個用于操作查詢的機制語句,以及每次執行操作時將生成的數據量的一些信息。如果你很了解你的數據,你就會知道對數據集數量的個別估計有多精確,每個操作可能執行多少次;正是這種優化器的猜測與你對數據的理解的比較,讓你知道預測和現實之間的變化會出現在哪里。在接下來的幾部分中,我們將研究如何將預測與運行時所做的工作進行比較,并展示如何幫助我們找到提高查詢性能的方法。



            沃趣科技,讓客戶用上更好的數據庫技術!
            三分快3