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

            現在是時候將更多的關注從主體轉移到執行計劃給到我們的數字方面的信息。在這個章節,我們將只研究優化器做出的預測(解釋計劃),推遲對實際運行時數據(v$sql_plan_statistics_all)的調查。


            Getting Started


            作為參考的例子,我們從一個非常簡單的查詢和計劃開始:


            explain plan for
            select
                    t1.id, t2.id
            from
                    t2, t1
            where
                    t2.n1 = 15
            and     t1.n1 = t2.n2
            ;
            
            select * from table(dbms_xplan.display(null, null, "projection"));
            
            --------------------------------------------------------------------------------------
            | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
            --------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT             |       |   225 |  4500 |    35   (6)| 00:00:01 |
            |*  1 |  HASH JOIN                   |       |   225 |  4500 |    35   (6)| 00:00:01 |
            |   2 |   TABLE ACCESS BY INDEX ROWID| T2    |    15 |   180 |    16   (0)| 00:00:01 |
            |*  3 |    INDEX RANGE SCAN          | T2_I1 |    15 |       |     1   (0)| 00:00:01 |
            |   4 |   TABLE ACCESS FULL          | T1    |  3000 | 24000 |    18   (6)| 00:00:01 |
            --------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               1 - access("T1"."N1"="T2"."N2")
               3 - access("T2"."N1"=15)
            
            Column Projection Information (identified by operation id):
            -----------------------------------------------------------
               1 - (#keys=1) "T2"."ID"[NUMBER,22], "T1"."ID"[NUMBER,22]
               2 - "T2"."ID"[NUMBER,22], "T2"."N2"[NUMBER,22]
               3 - "T2".ROWID[ROWID,10]
               4 - "T1"."ID"[NUMBER,22], "T1"."N1"[NUMBER,22]


            如果你之前接觸過這個系列的前幾章,你能很快的找到這個語句執行的順序,3,2,4,1,0:我們先執行索引范圍掃描(第3行),之后在t2表中找到所需要的數據(第2行),并在內存中建立hash表,之后對t1表做全表掃描(第4行)并探測內存表,找到匹配的數據(第1行)并傳遞給最終用戶(第0行)。這篇文章中我們將花時間解釋數據方面的數據---Rows,Bytes,Cost(%CPU)和Time列。

            一個很重要的點需要記住,就是執行計劃預測的值只是針對那一行的(當你從視圖v$sql_plan_statistics_all查看行源執行統計信息時,情況就不一樣了),第二點,同樣也很重要,從子操作到父操作,Cost和Time的計量不斷疊加,最后一點,有很多缺陷使得對這些數據做出不準確的解釋---四舍五入使問題變得模糊,一些數字丟失了,一些數字出現在錯誤的行上,計劃中的一些數字與在相應的10053中找到的數字不一致。由于各種原因,你可能會發現在某些情況下(我將在后面的幾期文章中向您展示一些非常簡單的情況),我所給出的描述似乎是不完整的。


            Basic explanation


            這里是這些列的簡單說明:


            Rows預測的一個操作需要向它父操作傳遞的行數。


            Bytes預測的一個操作需要向它父操作傳遞的字節數。


            Cost 此操作一次執行所需的資源預測—包括此操作一次完成所需的每個派生操作的每次執行所需的資源。括號中的數字(%CPU)是可以歸因于CPU活動的資源使用的百分比。正如我們將在下面看到的,有兩種解釋成本的方法。


            Time 僅執行此操作一次所需的預計運行時間(小時:分鐘:秒)。與成本一樣,一次執行操作的時間包括完成一次操作所需的所有子操作的所有執行所花費的時間。 


            我一直重復使用“預測”這個詞和“一次執行”這個短語,但重要的是要記住預測可能(通常)是錯誤的;優化器所做的預測的一部分——有時是隱藏的一部分——是“這個操作將執行多少次”,所以一個操作的一次執行的數字成為理解整個計劃的總數的一個重要方面。 


            我認為有兩種方法可以解釋“成本”一欄。多年來,我一直認為優化器的cost數字代表了預期的運行時間。如果你從執行計劃輸出中獲取cost數據,將其乘以系統統計表(aux_stats$)中的單個塊讀取時間(sreadtim),然后—考慮到舍入—你生成的結果將是執行計劃中報告的時間?;蛘?,如果你不想相信cost代表時間,你可以通過對比這個查詢對資源的影響相當于多少(實際的)單塊隨機讀取。


            Example


            了解了上面的內容,我們可以在例子上應用檢測。這是個十分簡單的例子,因為每行都只執行一次,我們按照執行的順序來,3,2,4,1,0.


            第三行:索引范圍掃描---預測會有15行返回,但是沒有返回字節數。我調用dbms_xplan.display請求"projection"的信息,告訴我們計劃中每一行生成列的一些信息。在第3行中我們看到返回的唯一列被描述為T2.ROWID[ROWID,10]。我不清楚為什么不將它的大小150(15*10)顯示出來,有可能是因為rowid是偽列,而且在dba_tab_columns中沒有這個條目,所以無法將avg_col_len這個值給到優化器。范圍掃描的成本是1---這是個很小的索引(1個根塊和7個葉子塊),優化器假設根塊已經被緩存了,之后需要1次物理讀找到葉子塊。預測的時間就是1*sreadtim-在我的例子中是6毫秒,四舍五入是1秒。


            第二行:通過rowid訪問表---優化器認為從第三行獲得的15個rowid可以讓我們從表中獲取15行,因為沒有任何的過濾條件。根據bytes列的180,我們可以知道每行平均的大小為12字節。檢查projection信息,我們可以看到列的信息為"T2"."ID"[NUMBER,22], "T2"."N2"[NUMBER,22],數據字典中每行的平均大小為4字節,那這里為什么會顯示180而不是120呢,也許優化器在計算中錯誤的包括列T2.N1列,我們在第三行中使用到這個列。這個操作的cost是16,再第三行中再加上15,因為優化器認為這15行分散在15個塊中,這些塊受到物理讀請求的影響。預測時間是16*sreadtim,即96毫秒,也就是1秒。


            第四行:對t1表的全表掃描---這個表中有3000行而且沒有過濾任何一行。每行平均8字節,所以bytes列顯示24000,我們檢查projection信息時,會發現返回"T1"."ID"[NUMBER,22], "T1"."N1"[NUMBER,22],而且數據字典里顯示所有的列的avg_row_len是4,這個例子中的算法是符合我們的預期的。cost是18,意味著資源需求等同于18個隨機單塊讀,即使我們知道Oracle會使用多塊讀掃描表,但是優化器內部還是會有內置的算法在單塊讀和多塊讀之間調整。在這個例子中,我們看到6%的cost是由于CPU的使用,這表明掃描表并不只消耗磁盤資源,檢查表中的每一行也會消耗大量CPU。如果我們希望有更多的細節,可以直接到plan_table表中查看cost,io_cost和cpu_cost。


            第一行:hash join---這里優化器正確的預測了行數,這個連接確實返回了225行(t2中的每一行對應t1中的15行)。但是對于bytes的計算存在異常,預測顯示平均每行有20字節,但是projection信息中可以看到,只有平均長度為4的兩列,優化器似乎將所有的列(5列)都考慮進來列。cost的計算是所有子操作的相加,所以第一眼看過去,你覺得會是第2行跟第4行的cost相加,但是16+18=34,不是35.這個差異的解釋來自于hash join自身的工作。如果我們查看cost,io_cost,cpu_cost,我們可以看到。


               ID OBJECT_NAME       COST   IO_COST  CPU_COST
               -- --------------- ------ --------- ---------
                2 T2                  16         16   120643
                4 T1                  18         17  1108201
                1                     35         33  2131094


            可以看到IO的cost是很簡單的累加,hash join自身并不引進任何的IO,但是第2行跟第4行的CPU的cost相加為1228844,與第1行的cost不匹配,這說明hash join自身的cost為902250.通過縮放和四舍五入,使得最終的IO cost成本增加了2,hash join的額外CPU也解釋了為什么成本的CPU組件仍然是6%:(35-33)/35=0.057。


            如果你想知道我為什么要對字節列中的奇怪之處大驚小怪,我可以給你兩個原因,首先,對于這個簡單的例子來說,可以很方便的知道計劃中包含的信息是否有可能是錯誤的,其次,優化器決定是否使用hash join,根據數據量的大小決定使用哪些表作為內存中的表,哪些是探測表。bytes列的細節可以解釋為什么Oracle會選擇錯誤的執行計劃。


            summary


            我們通過一個簡單的查詢,針對計劃中每一行的數字,了解它是怎么來的,代表什么含義。我們看到cost和time是怎么累加的,還了解了成本是如何由I/O成本和CPU成本組成的,并注意到CPU成本可能是總成本的一個重要部分。



            | 譯者簡介

            林錦森·沃趣科技數據庫技術專家
            沃趣科技數據庫工程師,多年從事Oracle數據庫,較豐富的故障處理、性能調優、數據遷移及備份恢復經驗。



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