記一次資料庫的優化之隱式轉換的破壞力

lnwxzyp發表於2013-04-07
資料庫優化
    今天QQ突然閃動，發現是一個地市的經支人員說他們的資料庫很慢，並給我發來了一段SQL
SELECT C.BSS_ORG_ID_2, 
       A.BILLING_CYCLE_ID, 
       ...
       SUM(a.charge) / 10000 charge
  FROM 
       DCS_AGGR2013 A, 
       MONTH_USER B, 
       QUDAO_YX_DATE C
 WHERE A.SERV_ID = B.SERV_ID
   AND B.SERV_ID = C.SERV_ID
   AND A.BILLING_CYCLE_ID = '11303'
   AND B.CITY_ITEM = '1'
   AND b.product_id IN (9, 10, 11, 12, 13, 14, 350000004)
   AND C.BSS_ORG_ID_2 IN ('352', '10015599')
 GROUP BY C.BSS_ORG_ID_2, A.BILLING_CYCLE_ID;
說是執行了很久都沒有結束，我馬上用sqldeveloper登上資料庫看了一下執行計劃，發現這個語句擺了很大的一個烏龍，所有加了引號的等式的欄位實際上是number型欄位，不過這個問題到不大，因為從實際的執行計劃來看，這些後邊加了引號的，實際上應該是內部做了轉換，轉成了數字型，這個對於語句幾乎沒有影響。可當看到b.product_id IN (9, 10, 11, 12, 13, 14, 350000004)這一句的時候問題就來了，執行計劃的截圖如下
記一次資料庫的優化之隱式轉換的破壞力
所有的product_id欄位都被轉換成了to_number(product_id)，這樣勢必會造成額外的CPU計算，但是看了三個表，除了DCS_AGGR2013有1個多GB，另外兩個表只有3~4百M，按說這樣大小的三張表還不至於拖垮資料庫吧，於是我就給地市的人員反饋了讓他按照我指出來的情況，重新修改一下語句，他的第一反應是即便是對一個欄位進行了to_number也不應該影響的這麼嚴重，CPU一直在60%以上，實際上剛開始我也認同他的說法。確實覺得一個sql語句不至於對系統造成太大的問題，於是要來了伺服器的使用者和密碼登陸到資料庫上取了一份當天0點至當前的war報告下來，copy到本機後，用chrome瀏覽器開啟，發現居然有亂碼，查詢了資料庫的字符集設定是ZHS16GBK沒有問題，百思不得其解，後來才注意到可能是瀏覽器的encoding的設定問題（此係後話按下不表），然後重新取了一份還是一樣，不過從awr上發現執行時間最長的sql是系統的job AUTO_SPACE_ADVISOR_JOB在收集物件的使用資訊造成的，查詢了dba_scheduler_jobs後發現果然還在執行，於是果斷的給disable掉。
begindbms_scheduler.disable('SYS.AUTO_SPACE_ADVISOR_JOB'); end;
然後給地市的反饋說我禁用掉了自動空間顧問的job，地市的人居然說既然已經禁用掉了為什麼CPU還這麼高？還這麼慢？ 
我  &……%&……*& 一陣無語後還是耐心給解釋了說這個原因可能有很多，不是發一個啥子命令出來 資料庫就運轉如飛了。
然後接著看到最消耗CPU的sql，前三條裡面居然有兩個就是他發給我的那個語句還包括了一些中文亂碼，因為是亂碼，所以我還把sql_id拿到sqldeveloper裡面去查了一下才得以確認
select * from v$sql where sql_id = 'apx6wum2544pc';
接著看到第一條最消耗CPU資源的是一個儲存過程，這個儲存過程當中居然就是很多個他發個我的那種類似的sql的集合，於是把這個發現也提交給他，自己也仔細看了一下這個過程，還是跟那個SQL一樣的語句，在看一下之前的執行計劃，這一次沒有用工具來看，而是開啟了sqlplus，我還比較喜歡在sqlplus下看執行計劃
SQL> set autot trace exp
SQL> select C.BSS_ORG_ID_2,
            A.BILLING_CYCLE_ID,
            sum(a.charge)/10000 charge
       from DCS_AGGR2013 A,
            MONTH_USER  B,
            QUDAO_YX_DATE C
      WHERE A.SERV_ID=B.SERV_ID
        AND B.SERV_ID=C.SERV_ID
        AND A.BILLING_CYCLE_ID=11303
        AND B.CITY_ITEM=1
        and b.product_id in ('9','10','11','12','13','14','350000004')
        AND C.BSS_ORG_ID_2 IN (352,10015599)
      GROUP BY C.BSS_ORG_ID_2,A.BILLING_CYCLE_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 658912135
-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                        |     1 |    63 |  40432  (2)| 00:08:06 |
|   1 |  HASH GROUP BY         |                        |     1 |    63 |  40432  (2)| 00:08:06 |
|*  2 |   HASH JOIN            |                        |     1 |    63 |  40431  (2)| 00:08:06 |
|*  3 |    TABLE ACCESS FULL   |    MONTH_USER          | 56887 |   888K|   7622  (2)| 00:01:32 |
|   4 |    MERGE JOIN CARTESIAN|                        |   230K|    10M|  32806  (2)| 00:06:34 |
|*  5 |     TABLE ACCESS FULL  |     DCS_AGGR2013       |     1 |    22 |  16704  (2)| 00:03:21 |
|   6 |     BUFFER SORT        |                        |   460K|    10M|  16102  (1)| 00:03:14 |
|*  7 |      TABLE ACCESS FULL |          QUDAO_YX_DATE |   460K|    10M|  16102  (1)| 00:03:14 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SERV_ID"=TO_NUMBER("A"."SERV_ID") AND
              "B"."SERV_ID"=TO_NUMBER("C"."SERV_ID"))
   3 - filter("B"."CITY_ITEM"=1 AND ("B"."PRODUCT_ID"='10' OR "B"."PRODUCT_ID"='11' OR
              "B"."PRODUCT_ID"='12' OR "B"."PRODUCT_ID"='13' OR "B"."PRODUCT_ID"='14' OR
              "B"."PRODUCT_ID"='350000004' OR "B"."PRODUCT_ID"='9'))
   5 - filter("A"."BILLING_CYCLE_ID"=11303)
   7 - filter("C"."BSS_ORG_ID_2"=352 OR "C"."BSS_ORG_ID_2"=10015599)
Note
-----
   - dynamic sampling used for this statement
從執行計劃當中，可以看到B表的SERV_ID和A、C表的SERV_ID做關聯的時候，其他兩個表的SERV_ID居然是to_number，一開始在sqldeveloper當中檢視執行計劃的時候給忽略了，馬上把這個問題反饋了出來，然後建議讓他把A、C兩個表重建一下，不要把SERV_ID設定成了varchar2型了。這個時候去檢視了一下伺服器，發現CPU一下降到了5%，問了之後原來他把那個過程給停掉了，看來導致CPU上升的元凶果然就是這個儲存過程裡面的語句，最主要的就是不恰當的欄位型別的隱式轉換，另外還發現了一句  
dynamic sampling used for this statement
實際是用到了動態取樣，這就說明表沒有收集統計資訊，正好地市的人又給我反饋說他把這個三個表關聯的SQL改成了兩個關聯產生結果集後再跟第三個表做關聯這樣不到十分鐘就出來了，於是我考慮給表收集一下統計資訊
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME          => 'STAT',  
    TABNAME          => 'DCS_AGGR2013', 
    ESTIMATE_PERCENT => 50,  
    METHOD_OPT       => 'FOR ALL INDEXED COLUMNS',
    DEGREE           => 4,  
    CASCADE          => TRUE);
END;
三個表都收集之後，再看執行計劃：
Execution Plan
----------------------------------------------------------
Plan hash value: 3658125727
-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                        |     1 |    64 |  48642  (2)| 00:09:44 |
|   1 |  HASH GROUP BY         |                        |     1 |    64 |  48642  (2)| 00:09:44 |
|*  2 |   HASH JOIN            |                        |     1 |    64 |  48641  (2)| 00:09:44 |
|   3 |    MERGE JOIN CARTESIAN|                        |  5427 |   254K|  41018  (2)| 00:08:13 |
|*  4 |     TABLE ACCESS FULL  |     DCS_AGGR2013       |     1 |    22 |  24936  (2)| 00:05:00 |
|   5 |     BUFFER SORT        |                        | 10854 |   275K|  16082  (1)| 00:03:13 |
|*  6 |      TABLE ACCESS FULL |          QUDAO_YX_DATE | 10854 |   275K|  16082  (1)| 00:03:13 |
|*  7 |    TABLE ACCESS FULL   |    MONTH_USER          | 56894 |   888K|   7622  (2)| 00:01:32 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SERV_ID"=TO_NUMBER("A"."SERV_ID") AND
              "B"."SERV_ID"=TO_NUMBER("C"."SERV_ID"))
   4 - filter("A"."BILLING_CYCLE_ID"=11303)
   6 - filter("C"."BSS_ORG_ID_2"=352 OR "C"."BSS_ORG_ID_2"=10015599)
   7 - filter("B"."CITY_ITEM"=1 AND ("B"."PRODUCT_ID"='10' OR "B"."PRODUCT_ID"='11' OR
              "B"."PRODUCT_ID"='12' OR "B"."PRODUCT_ID"='13' OR "B"."PRODUCT_ID"='14' OR
              "B"."PRODUCT_ID"='350000004' OR "B"."PRODUCT_ID"='9'))

可以看到跟前面的比較之下Rows和Bytes列評估出來的行數都不太相同，最後根據filter，給他建議讓建立索引並在建立之後重新收集統計資訊，此次優化就告一段落了，完了這個老兄還一個勁的問 你今天處理完了，系統是不是就會好起來了，我說這個主要還是取決於你們對程式碼的編寫情況了，最好在寫的時候多看看執行計劃有沒有什麼異常的儘量都處理一下。

the end.

後記：話說那個awr報告在chrome上開啟中文變亂碼的問題，實際上是encoding的設定問題，修改預設值需要在Customize and control Google Chrome=>Settings=>Show advanced settings...=>Web content=>Customize fonts=>Encoding選擇Chinese Simplified (GBK) 
或者直接在位址列輸入 chrome://settings/fonts 然後Encoding中選擇Chinese Simplified (GBK)  再次開啟awr報告，亂碼就解決了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12216142/viewspace-757920/，如需轉載，請註明出處，否則將追究法律責任。

相關文章