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

            之前的部分我介紹了3種獲得一個查詢執行時統計信息的方法,對其中一種方法詳細介紹了我們獲得了哪些信息以及如何使用它們。這篇文章中,我對于這個方法進行了擴展,來幫助我們確定和解決一些性能問題。這個方法被稱為基數反饋(Cardinality Feedback)。


            Skewed Data


            我有一個沒有正確分析過的查詢,如下所示:


            select
            t1.id,
            t2.small_vc
            from
            t1,
            t2
            where
            t1.date_ord >= trunc(sysdate) - 14
            and t1.supp_id = 1
            and t2.id = t1.id
            order by
            t1.id
            ;

            我通過設置statistics_level為all來開啟數據源執行統計信息,我在內存里獲得了以下執行計劃:


            select * from table(dbms_xplan.display_cursor(null,null,"iostats last +cost"));
            
            -----------------------------------------------------------------------------------------------------------------
            | Id  | Operation                      | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
            -----------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT               |         |      1 |        |   206 (100)|   2800 |00:00:00.23 |     799 |
            |   1 |  SORT ORDER BY                 |         |      1 |      1 |   206   (2)|   2800 |00:00:00.23 |     799 |
            |   2 |   NESTED LOOPS                 |         |      1 |      1 |   205   (1)|   2800 |00:00:00.21 |     799 |
            |   3 |    NESTED LOOPS                |         |      1 |      1 |   205   (1)|   2800 |00:00:00.15 |     560 |
            |*  4 |     TABLE ACCESS BY INDEX ROWID| T1      |      1 |      1 |   202   (1)|   2800 |00:00:00.09 |     200 |
            |*  5 |      INDEX RANGE SCAN          | T1_DATE |      1 |  14013 |    41   (3)|  14000 |00:00:00.04 |      40 |
            |*  6 |     INDEX RANGE SCAN           | T2_I1   |   2800 |      1 |     2   (0)|   2800 |00:00:00.03 |     360 |
            |   7 |    TABLE ACCESS BY INDEX ROWID | T2      |   2800 |      1 |     3   (0)|   2800 |00:00:00.02 |     239 |
            -----------------------------------------------------------------------------------------------------------------
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               4 - filter("T1"."SUPP_ID"=1)
               5 - access("T1"."DATE_ORD">=TRUNC(SYSDATE@!)-14)
               6 - access("T2"."ID"="T1"."ID")

            注意我在調用display_cursor()時添加了額外的參數”+cost”,它會報告優化器認為這些資源的成本是多少,這一小段信息作為E-Rows信息的補充非常有用。我使用了”iostats”選項而不是”allstats”,我不常用這個,但是這是一個消除內存統計信息的方法,內存統計信息會讓這個報告顯示得更寬但是只包含一點額外的信息,比如”sort order by”操作需要100KB的內存完成最優的工作區操作。


            在我們比對預估和實際數據之前,我們可以注意到明顯的問題:第4和第5行的E-rows告訴我們,Oracle期望通過索引t1_date獲得14000個rowid,但是通過rowid去訪問表只返回了一行,這個查詢最多的成本(202-41)消耗在訪問表上。優化器似乎認為選擇一個比較差的索引是執行這個查詢最好的選擇。來看看第5行的access()和第4行的filter(),我們可能決定(a)擴展列(date_ord)上存在的索引,使其變成為(date_ord,supp_id),(b)如果列(supp_id)上存在索引,擴展索引使其變成為(supp_id.date_ord),(c)在列(supp_id)上創建索引,因為這個列上應該有索引但是缺失了。


            即使沒有正確的索引,但是也不影響我們對比真實和預估的數據,因為這可能有助于我們對底層執行做出明智的決定。


            這個計劃的執行順序是5,4,6,3,7,2,1,我們也從這個順序開始比對,記住,在對比E-Rows和A-Rows之前,一定要乘以Starts的值:


            5 — index range scan — 預估14013行,執行1次;實際14000行。預測較準確。


            4 — table access by rowid — 預估1行,執行1次;實際2800行。預測誤差大。


            對于第4行我們有兩點擔心:1.為什么預測誤差會這么大?2.錯誤的預測會對整個執行計劃造成不好的影響嗎?


            6 — index range scan — 預估1行,執行2800次;實際2800行。預測準確。


            在經歷了第4行錯誤的預測后,我們又重新正確預測了第6行,這是為什么?因為即使對比E-Rows*Start的值與A-Rows的值相等,但這也只是局部間一行行的比對,局部正確的預測并不代表執行計劃整體的正確預測。


            這個例子中,如果我們通過表t2里id列的索引去訪問表的話,我們一次執行會獲得一個rowid,并且只訪問很小的buffer就能獲取一行,從預測來看是這樣的,這個預測也很準確。但是實際上我們需要執行這個操作2800次,這可能不是一個高效率的方式。


            報告中我們沒有一列是”E-Starts”,如果有的話它的值可能是1(嵌套循環中驅動表的E-Rows的值),這將幫助我們判斷預測中最重要的錯誤是發生在什么時候。當我們看到A-Rows列時,我們需要知道這些數據的大小是每次執行時的行數,還是總的執行行數。


            3 — nested loop — 預估1行,執行1次;實際2800行。預測誤差大。


            7 — table access by rowid — 預估1行,執行2800次;實際2800行。預測準確。


            2 — nested loop — 預估1行,執行1次;實際2800行。預測誤差大。


            1 — sort order by — 預估1行,執行1次;實際2800行。預測誤差大。


            我們可以總結下這個查詢執行計劃的狀態,第4和第5行的E-Rows表明優化器知道并不存在高效率的訪問t1表的路徑,當我們比較第4行中的E-Rows和A-Rows時,我們可以看到優化器對于表t1中的數據并不了解,也是從這行開始整個執行計劃的預測變得很不靠譜,因為這是最開始的誤差較大的預測。忽略這個最開始的錯誤,這行后面的每一步(連接和訪問方式)都是按照優化器的期望去執行的,每次執行會選擇選擇性高的索引去訪問表并返回小數據量的行數,但是也是從這行開始,我們會潛在的認為這個執行計劃是一個不好的執行計劃。這就引出了兩個關于將帶有數據源執行統計信息的執行計劃用作突出顯示性能問題根本原因的工具的一般要點:


            1.執行計劃中的任何地方,實際和預估的差距都可能指向數據庫基礎結構的缺陷,包括選擇性較差的索引,統計信息的缺失等。


            2.執行計劃中,最早出現實際與預估有偏差的地方,往往就是導致優化器選擇錯誤執行計劃的原因,我們首先要調查這個錯誤原因。


            在這個例子中,數據存在嚴重的傾斜。t1表中有數百個供應商ID,但是其中一小部分供應商ID占了很大一部分的數量。因為我沒有在列supp_id上創建直方圖,導致優化器認為表上不同ID的行數為(num_rows/supp_id.num_distinct),但是供應商1是我的大貨供應商之一,過去兩周供應了2,800件貨物。


            對于這類問題,沒有簡單、通用的方案,最好的策略就是妥協,但是也取決于很多的變量,所以這里就不詳細敘述了。不過,作為對主要主題的補充說明,我會提到一點,因為我沒有在這個查詢中使用綁定變量,在11.2.0.4版本中,由于t1表上有兩個謂詞,會導致優化器將首次產生的游標的’is_sharable’值設為’N’,‘use_feedback_stats’的值設為‘Y’,并且再下一次執行時重新優化這條語句。這會產生一個不同的執行計劃,計劃中包含對t1表的hash join(對t1表的訪問仍然使用相同的索引)。


            標量子查詢緩存

            我們遇到的另一類的問題是,數據源執行統計信息可以在標量子查詢方面有所幫助,不論是在select列表中或者在where子句中。下面是之前章節的一個例子:


            select
                    count(*)
            from    (
                    select  /*+ no_merge */
                            outer.*
                    from
                            emp outer
                    where
                            outer.sal > (
                                    select  /*+ no_unnest */
                                            avg(inner.sal)
                                    from
                                            emp inner
                                    where
                                            inner.dept_no = outer.dept_no
                            )
                    )
            ;    
            
            --------------------------------------------------------------
            | Id  | Operation             | Name | Rows  | Bytes | Cost  |
            --------------------------------------------------------------
            |   0 | SELECT STATEMENT      |      |     1 |       |   245 |
            |   1 |  SORT AGGREGATE       |      |     1 |       |       |
            |   2 |   VIEW                |      |   167 |       |   245 |
            |*  3 |    FILTER             |      |       |       |       |
            |   4 |     TABLE ACCESS FULL | EMP  | 20000 |   156K|    35 |
            |   5 |     SORT AGGREGATE    |      |     1 |     8 |       |
            |*  6 |      TABLE ACCESS FULL| EMP  |  3333 | 26664 |    35 |
            --------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               3 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */
                          AVG("INNER"."SAL") FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
               6 - filter("INNER"."DEPT_NO"=:B1)

            emp表上里20000個員工分布在6個部門。我在第10章節里使用這個查詢的變體,并沒有像這段SQL一樣將結果集簡化為帶有簡單count(*)的內聯no_merge()視圖,但是查詢的關鍵特性沒有改變。問題是相關聯的子查詢到底執行了多少次?原則上,是“6(部門數量)到20000(員工數量)中任意你想要的數字”??碈ost列,Oracle會基于自己的算法模型期望執行6次子查詢:245=35(第4行操作)+6*35(第6行操作)。


            所以當你增加一些新的部門,開除一些員工,雇傭一些新員工,調遣一些員工到其他部門,會有什么事情發生?對于數據量大小來說沒什么變化,但是對查詢性能來說變化很大。最開始這個查詢在0.35s內完成,當改變一些員工的dept_no值時,運行時間爆增至3分鐘,都消耗在CPU上。下面是改變后的查詢的數據源執行統計信息。


            ----------------------------------------------------------------------------------------
            | Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
            ----------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT      |      |      1 |        |      1 |00:03:09.75 |    1467K|
            |   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |00:03:09.75 |    1467K|
            |   2 |   VIEW                |      |      1 |    167 |   9997 |00:03:09.71 |    1467K|
            |*  3 |    FILTER             |      |      1 |        |   9997 |00:03:09.63 |    1467K|
            |   4 |     TABLE ACCESS FULL | EMP  |      1 |  20000 |  20000 |00:00:00.08 |     220 |
            |   5 |     SORT AGGREGATE    |      |   6671 |      1 |   6671 |00:03:09.31 |    1467K|
            |*  6 |      TABLE ACCESS FULL| EMP  |   6671 |   3333 |     22M|00:01:38.37 |    1467K|
            ----------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               3 - filter("OUTER"."SAL">)
               6 - filter("INNER"."DEPT_NO"=:B1)

            子句實際執行了6671次而不是6次,因為執行引擎不能存儲所有需要的子句結果緩存。這個例子中的查詢是人為構造的,用來演示標量子查詢的一些問題,正確的方法是阻止子查詢展開,但是實際環境中這類問題也會發生,幸運的是,我們可以知道哪里發生了問題,也可以及時重寫這些查詢。


            相同的問題也發生在select列中的標量子查詢。如果你select列中有許多標量子查詢,同時整個查詢花費了很長的時間,你需要怎么找到原因?這個時候數據源執行統計信息就能幫上大忙?;谙嗤谋?,下面是一個查詢和帶有執行信息的執行計劃:


            select
                    dept_no,
                    emp_no,
                    (select sum(sal) from emp e2 where e2.dept_no = e1.dept_no) sal_tot
            from
                    emp e1
            order by
                    dept_no, emp_no
            ;
            
            ----------------------------------------------------------------------------------------------------------------
            | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
            ----------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT   |      |      1 |        |  20000 |00:00:04.71 |     731K|       |       |          |
            |   1 |  SORT AGGREGATE    |      |   3338 |      1 |   3338 |00:00:04.59 |     731K|       |       |          |
            |*  2 |   TABLE ACCESS FULL| EMP  |   3338 |   3333 |     11M|00:01:06.77 |     731K|       |       |          |
            |   3 |  SORT ORDER BY     |      |      1 |  20000 |  20000 |00:00:04.71 |     731K|   832K|   511K|  739K (0)|
            |   4 |   TABLE ACCESS FULL| EMP  |      1 |  20000 |  20000 |00:00:00.08 |     219 |       |       |          |
            ----------------------------------------------------------------------------------------------------------------

            部門和員工不正確的組合會導致內聯標量子查詢執行3338次而不是每個部門1次,這也是一個線索,查詢應該在hash join中使用了非合并聚合視圖。


            Summary


            我們看到了數據源執行統計信息會告訴你一個操作會執行多少次,它總共做了多少的工作,以及優化器預估返回的行數跟真實返回行數的差距,特別需要記住我們需要用E-rows * Starts來跟A-Rows進行比對,同樣也認識到,”局部”的預測準確并不代表“整體”的準確。


            不過,有兩個地方可以將全局思想應用到數據源執行統計信息。第一個是找到最早的操作中,預估和實際執行的行數有很大誤差的地方,這有可能會導致后面低效的連接方式。第二個地方是實際上花費在標量子查詢上的工作量遠比預測的來的大的情況,如果沒有數據源執行統計信息,那么很有可能就不知道到底表量子查詢被執行了多少次。



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