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

            這個系列目前為止,我們討論了關于如何解釋一個執行計劃以及理解優化器對于成本和基數預測的含義。最終我們來看看,當查詢執行時,Oracle給我們的執行計劃中優化器的預測與實際工作的匹配程度。


            有2種方法可以查看具體的信息,1.啟用隱含參數”_rowsource_execution_statistics”,之后調用dbms_xplan.display_cursor()展示具體的信息;2.啟用sql_trace,之后使用tkprof來查看具體的信息;這篇文章會使用第1種方法。


            Rowsource Execution Statistics


            在執行語句時,Oracle能夠積累關于計劃中每個操作所做的工作量的信息,包括調用每個操作的時間,buffer的訪問量,訪問的是當前模式還是一致性模式,物理讀和寫,每個操作的總時間還有workarea使用的信息。這些信息在動態視圖v$sql_plan_statistics_all中是可見的,在11.2.0.4中的描述如下:


            1: ADDRESS               RAW(8)
            2: HASH_VALUE            NUMBER
            3: SQL_ID                VARCHAR2(13)
            4: PLAN_HASH_VALUE       NUMBER
            5: CHILD_ADDRESS         RAW(8)
            6: CHILD_NUMBER          NUMBER
            7: TIMESTAMP             DATE
            8: OPERATION             VARCHAR2(30)
            9: OPTIONS               VARCHAR2(30)
            10: OBJECT_NODE          VARCHAR2(40)
            11: OBJECT#              NUMBER
            12: OBJECT_OWNER         VARCHAR2(30)
            13: OBJECT_NAME          VARCHAR2(30)
            14: OBJECT_ALIAS         VARCHAR2(65)
            15: OBJECT_TYPE          VARCHAR2(20)
            16: OPTIMIZER            VARCHAR2(20)
            17: ID                   NUMBER
            18: PARENT_ID            NUMBER
            19: DEPTH                NUMBER
            20: POSITION             NUMBER
            21: SEARCH_COLUMNS       NUMBER
            22: COST                 NUMBER
            23: CARDINALITY          NUMBER
            24: BYTES                NUMBER
            25: OTHER_TAG            VARCHAR2(35)
            26: PARTITION_START      VARCHAR2(64)
            27: PARTITION_STOP       VARCHAR2(64)
            28: PARTITION_ID         NUMBER
            29: OTHER                VARCHAR2(4000)
            30: DISTRIBUTION         VARCHAR2(20)
            31: CPU_COST             NUMBER
            32: IO_COST              NUMBER
            33: TEMP_SPACE           NUMBER
            34: ACCESS_PREDICATES    VARCHAR2(4000)
            35: FILTER_PREDICATES    VARCHAR2(4000)
            36: PROJECTION           VARCHAR2(4000)
            37: TIME                 NUMBER
            38: QBLOCK_NAME          VARCHAR2(30)
            39: REMARKS              VARCHAR2(4000)
            40: OTHER_XML            CLOB
            41: EXECUTIONS           NUMBER
            42: LAST_STARTS          NUMBER
            43: STARTS               NUMBER
            44: LAST_OUTPUT_ROWS     NUMBER
            45: OUTPUT_ROWS          NUMBE
            46: LAST_CR_BUFFER_GETS  NUMBER
            47: CR_BUFFER_GETS       NUMBER
            48: LAST_CU_BUFFER_GETS  NUMBER
            49: CU_BUFFER_GETS       NUMBER
            50: LAST_DISK_READS      NUMBER
            51: DISK_READS           NUMBER
            52: LAST_DISK_WRITES     NUMBER
            53: DISK_WRITES          NUMBER
            54: LAST_ELAPSED_TIME    NUMBER
            55: ELAPSED_TIME         NUMBER
            56: POLICY               VARCHAR2(10)
            57: ESTIMATED_OPTIMAL_SIZE  NUMBER
            58: ESTIMATED_ONEPASS_SIZE  NUMBER
            59: LAST_MEMORY_USED     NUMBER
            60: LAST_EXECUTION       VARCHAR2(10)
            61: LAST_DEGREE          NUMBER
            62: TOTAL_EXECUTIONS     NUMBER
            63: OPTIMAL_EXECUTIONS   NUMBER
            64: ONEPASS_EXECUTIONS   NUMBER
            65: MULTIPASSES_EXECUTIONS  NUMBER
            66: ACTIVE_TIME          NUMBER
            67: MAX_TEMPSEG_SIZE     NUMBER
            68: LAST_TEMPSEG_SIZE    NUMBER

            如果仔細看這些列,會發現,前40行就是重復v$sql_plan里的列,基本的內存里執行計劃的信息,第41 - 55行是來自v$sql_plan_statistics的非鍵列,第56-68行來自v$sql_workare,最后一組列是v$sql_plan_statistics_all中報告的有用的信息,即使你沒有開啟rowsource execution statistics。


            Workarea


            在我們繼續研究v$sql_plan_statistics_all之前,我們很有必要利用一下v$sql_workarea中的有用信息,這些信息在故障排除時往往被忽略。我們從一個對sys對象簡單的查詢開始,之后調用dbms_xplan.display_cursor(),這里并沒有開啟rowsource execution statistics:


            alter session set statistics_level = typical;
            
            select ct, count(*) from (
            select obj#, count(*) ct from sys.source$ group by obj#
            )
            group by ct order by ct
            
            select * from table(dbms_xplan.display_cursor(null,null,"allstats last"))
            
            SQL_ID  33vchu53wj57a, child number 0
            -------------------------------------
            select ct, count(*) from (select obj#, count(*) ct from sys.source$
            group by obj#) group by ct order by ct
            
            Plan hash value: 2879348764
            
            ---------------------------------------------------------------------------------
            | Id  | Operation               | Name      | E-Rows |  OMem |  1Mem | Used-Mem |
            ---------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT        |           |        |       |       |          |
            |   1 |  SORT GROUP BY          |           |   5513 | 36864 | 36864 |32768  (0)|
            |   2 |   VIEW                  |           |   5513 |       |       |          |
            |   3 |    HASH GROUP BY        |           |   5513 |    37M|  8100K| 2719K (0)|
            |   4 |     INDEX FAST FULL SCAN| I_SOURCE1 |    670K|       |       |          |
            ---------------------------------------------------------------------------------
            
            Note
            -----
               - Warning: basic plan statistics not available. These are only collected when:
                   * hint "gather_plan_statistics" is used for the statement or
                   * parameter "statistics_level" is set to "ALL", at session or system level

            在調用時,我使用了(null,null,allstats last)參數,這個參數應該會打印出上一次執行的數據源執行統計數據,但是我設置了statistics_level值為”typical”,所以除了與wokarea (PGA)使用相關的統計信息外,沒有其他可用的執行統計信息。Note部分也輸出了一個warning,統計數據不可用,并且告訴我們兩種可以獲得的方法,其實還有第三種,就是將隱含參數”_rowsource_execution_statistics”設置為true。

            通過上一次執行的計劃可以得到以下工作區相關的信息:


            OMem:Oracle認為的所有操作都可以在內存里完成的內存大小。


            1Mem:只需要寫入一次數據到臨時空間時的內存大小。


            Used_Mem:上一次執行我們實際使用的內存。


            如果我們使用 allstats 而不是 allstats last ,我們會得到子游標首次執行以來所有的活動總和,下面是我的例子:


            ---------------------------------------------------------------------------------
            | Id  | Operation               | Name      | E-Rows |  OMem |  1Mem |  O/1/M   |
            ---------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT        |           |        |       |       |          |
            |   1 |  SORT GROUP BY          |           |   5513 | 36864 | 36864 |     2/0/0|
            |   2 |   VIEW                  |           |   5513 |       |       |          |
            |   3 |    HASH GROUP BY        |           |   5513 |    37M|  8100K|     2/0/0|
            |   4 |     INDEX FAST FULL SCAN| I_SOURCE1 |    670K|       |       |          |
            ---------------------------------------------------------------------------------
            

            最后一列會輸出每個操作是最優操作的次數,有多少是屬于one-pass(內存不足,寫入一次臨時空間),有多少是屬于multi-pass(內存不足,多次寫入臨時空間)。這個例子中(一共執行兩次),兩次都是最優執行,沒有操作溢出到磁盤上。


            我這個版本的Oracle也存在一些bug,如果第3行的HASH GROUP BY有幾次one-pass操作,則O/1/M列就會為空。當然,這種情況也可以通過一些線索發現,比如Used-Tmp列或者Max-Tmp列,為了更好地利用v$sql_workarea視圖和dbms_xplan.display_cursor()的組合,你可以使用以下查詢語句來查詢這個視圖:


            select
                sql_id, child_number, estimated_optimal_size,
                total_executions, optimal_executions, onepass_executions, multipasses_executions,
                max_tempseg_size
            from
                v$sql_workarea
            where
                max_tempseg_size > 65535
            or      onepass_executions > 10
            or      multipasses_executions > 0
            ;

            執行這個查詢后,你可以通過dbms_xplan.display_cursor()查詢任何列出的sql_id和child_number來獲得可能對系統資源造成巨大壓力的語句。這個查詢會對系統造成不小的壓力,所以建議不要頻繁執行。


            Execution Stats


            我通過設置statistics_level的值為all來開啟數據源執行統計數據,我的測試里有兩個明顯的改變。第一點,執行時間從0.12s延長至3.73s(大部分消耗在CPU上),第二點,dbms_xplan.display_cursor()的輸出生成了更多的列。


            ------------------------------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation               | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
            ------------------------------------------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT        |           |      1 |        |    563 |00:00:03.73 |    1623 |   1632 |     16 |       |       |          |         |
            |   1 |  SORT GROUP BY          |           |      1 |   5513 |    563 |00:00:03.73 |    1623 |   1632 |     16 | 37888 | 37888 |32768  (0)|         |
            |   2 |   VIEW                  |           |      1 |   5513 |   5514 |00:00:03.71 |    1623 |   1632 |     16 |       |       |          |         |
            |   3 |    HASH GROUP BY        |           |      1 |   5513 |   5514 |00:00:03.69 |    1623 |   1632 |     16 |    36M|  8304K|  421K (0)|    1024 |
            |   4 |     INDEX FAST FULL SCAN| I_SOURCE1 |      1 |    670K|    670K|00:00:01.84 |    1623 |   1616 |      0 |       |       |          |         |
            ------------------------------------------------------------------------------------------------------------------------------------------------------

            額外的CPU消耗是與平臺有關的,并且與調用O/S計時函數所花費的時間有關;出于這個原因,你可能希望對自己的平臺進行一些測試,并希望使用另一種啟用統計信息的機制。就像我們看到的之前的執行計劃的Note部分,可以在語句中添加/*+gather_plan_statistics*/ hint來實現,但是,當統計信息由hint驅動時,計時是通過抽樣完成的,在我的例子中,查詢花費0.18秒(而不是0.12或3.75秒)。這里有一個陷阱;下面是我將statistics_level設置成typical并且使用hint的輸出。


            ------------------------------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation               | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
            ------------------------------------------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT        |           |      1 |        |    563 |00:00:00.15 |    1623 |     42 |     16 |       |       |          |         |
            |   1 |  SORT GROUP BY          |           |      1 |   5513 |    563 |00:00:00.15 |    1623 |     42 |     16 | 37888 | 37888 |32768  (0)|         |
            |   2 |   VIEW                  |           |      1 |   5513 |   5514 |00:00:00.21 |    1623 |     42 |     16 |       |       |          |         |
            |   3 |    HASH GROUP BY        |           |      1 |   5513 |   5514 |00:00:00.17 |    1623 |     42 |     16 |    36M|  8304K|  431K (0)|    1024 |
            |   4 |     INDEX FAST FULL SCAN| I_SOURCE1 |      1 |    670K|    670K|00:00:02.31 |    1623 |     26 |      0 |       |       |          |         |
            ------------------------------------------------------------------------------------------------------------------------------------------------------

            Reads值的變化是因為數據在緩存里了,但是通過觀察兩個執行計劃的A-Time列,它應該是子操作的累加,但是很明顯這里出現了問題。這個執行計劃非常簡單,每個操作只會調用唯一的子操作,所以執行計劃中的累加也十分簡單,但是索引快速全掃描顯示花費了2.31s,總共的時間也才0.15s,當我們從下往上看執行計劃時,時間也是時長時短。所以當你使用/*+gather_plan_statistics*/ hint時,不要相信A-Time這列的值。

            我們剛剛著重介紹了A-Time這列,下面是其他列的描述:

            Starts:操作真實發生的執行次數。

            E-rows:操作每次執行預估的行數。

            A-rows:這個操作累加的行數。

            A-Time:這個操作累計花費的時間-包括它的子操作。

            Buffers:這個操作累計訪問的buffer-包括它的子操作。

            Reads:這個操作累計從磁盤讀取的數據塊-包括它的子操作。

            Writes:這個操作累計寫到磁盤的數據塊-包括它的子操作。

            在解釋帶有執行統計信息的計劃時,需要記住兩個關鍵點;第一個是理解Oracle 向上累加的含義,第二個是starts列的意義。


            Interpretation


            這篇文章我提供的例子并不能很好的幫助我們理解這兩點,我會在下面幾篇文章中使用更好的例子,但至少我們可以看到一些是累加操作的特征。


            一個很好的閱讀執行計劃的規則是“子操作先執行”,一個父操作會依次調用它的子操作,它的子操作也可能調用它自己的子操作返回數據給它的父操作。這樣我們就很容易理解,操作所做的工作包括操作本身所做的工作(例如,對輸入的數據進行排序),以及每個子操作為其提供數據所做的工作。


            上面的例子中每個操作都只有一個子操作,但是我們也能通過第3跟第4行看到簡單的‘父+子’計算方法?!県ash group by”操作本質上并不會使用buffer cache,所以第3行并不會記錄Buffers的值,但是第4行中使用了buffer,并將數據傳遞給第3行進行聚合,所以第3行才有了Buffers的值。


            另一方面,如果一個”hash group by”操作溢出到磁盤上,它會寫入到臨時表空間上,之后再重新讀取回來。第3行顯示有16個塊被寫入并且讀取了42個塊,為什么讀取的會大于寫入的,多余出來的26個塊的讀,是在第4行對i_source1做索引快速全掃描時發生的。


            從第3行起就沒有資源的增量使用了,因為”sort group by”操作都是在內存里完成的,所以不需要物理讀和寫。還有很重要的Starts列,在這個執行計劃中也不明顯,因為每個操作只執行了一次。我們會在下篇文章中我們會仔細研究這部分,但是這個示例中的大部分操作,E-rows和A-rows的值是十分匹配的,我們應該感到高興,因為優化器通過我們的查詢,對數據處理做了一個比較好的預測。但是這兩列值的相近是因為Start列為1:E-rows表示每次操作執行預估的行數,所以每次我們去判斷優化器預測的準確度時,我們需要比對A-rows和E-rows*Start的值,我們同樣也需要注意,在什么情況下比對是有用的,在什么情況下比對是無意義的。



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