複雜SQL效能優化的剖析(一）(r11筆記第36天)

今天本來是處理一個簡單的故障，但是發現是一環套一環，花了我快一天的時間。

開始是早上收到一條報警：

報警內容: CPUutilization is too high
------------------------------------
報警級別: PROBLEM
------------------------------------
監控專案: CPU idle time：59.94 %
------------------------------------
報警時間:2017.01.06-06:35:22開始也沒有在意，準備花幾分鐘處理完事，但是發現這個坑越來越大。對於問題的處理，我覺得還是能夠刨坑問底，你能說服自己，弄明白了，碰到類似的問題就會得心應手。

    我發現資料庫的負載有了較大的提升，檢視快照級別的DB time負載如下：

BEGIN_SNAP   END_SNAP SNAPDATE            DURATION_MINS     DBTIME
---------- ---------- --------------------------------- ----------
     19028      19029 06 Jan 2017 00:00              60        104
     19029      19030 06 Jan 2017 01:00              60        233
     19030      19031 06 Jan 2017 02:00              60        331
     19031      19032 06 Jan 2017 03:00              60        409
     19032      19033 06 Jan 2017 04:00              60        465
     19033      19034 06 Jan 2017 05:00              60        513
     19034      19035 06 Jan 2017 06:00              60        538
     19035      19036 06 Jan 2017 07:00              59        591
     19036      19037 06 Jan 2017 08:00              60        614
     19037      19038 06 Jan 2017 09:00              60        622
     19038      19039 06 Jan 2017 10:00              60        665
這個情況不容樂觀，很快就定位到是一個SQL語句引起的。

可以看到語句的執行計劃發生了改變，本來執行2秒的語句，現在執行近5000多秒。這個差距實在是有些大了。

語句的結構如下：

merge into xxxx using

(
select xxxx from h1_first_dev d, ua_td_active_log a
where d.dt = to_date(:1, 'yyyy-mm-dd')
and a.dt = :2
and d.deviceid is not null
and (d.deviceid = a.idfa or d.deviceid = a.mac or
a.idfa =lower(utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5(input_string => d.deviceid))))
...
union
select xxxx from h1_first_dev d, ua_td_active_log a
where d.dt = to_date(:1, 'yyyy-mm-dd')
and a.dt = :2
and d.deviceid is not null
and (d.deviceid = a.idfa or d.deviceid = a.mac or
and (a.idfa =lower(utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5(input_string => nvl(case when instr(d.deviceid, ':') > 0 then replace(substr(substr(d.deviceid, 19, 15), 1, 15), chr(2), '') else replace(substr(d.deviceid, 1, 15), chr(2), '') end, 'null')))))
....
)  

on(xxxx)  when matched xxx

看起來語句結構也蠻複雜，而且呼叫了幾個大家平時很少見到的包，這個時候就有兩個問題需要解釋清楚。

1. 之前為什麼沒有這個問題

2. 問題的解決方法是什麼

檢視資料庫層面的活躍會話情況，可以看到有大量的會話被阻塞了，而且看阻塞的頻率，語句大概是10分鐘執行一次。

我對比了變化前後的執行計劃情況。

一個重要的變化是兩個執行計劃的表關聯方式不同，左邊的效率較差，使用了Nested Loop Join,右邊的部分效率較高，使用了Hash Join

當然語句的執行計劃很長，我點到為止，重點是如果單單是表關聯方式發生變化，這個肯定說明不了Nested Loop Join比Hash Join要好。

簡單總結了一下索引的掃描方式，也有了不同的結果。

IDX_H1_FIRST_DT_DEV_DEVICEID(Nested Loop Join)  使用了index range scan
IDX_H1_FIRST_DEV_APPKEY_DT(Hash Join)  使用了 index skip scan

不過這個地方我排查了直方圖等資訊，沒有發現異常，對於NL Join和Hash Join,我可以簡單通過如下的資料來論證。

 SQL> select count(*)from mbi.h1_first_dev where dt between sysdate-1 and sysdate;
  COUNT(*)
----------
      3330

所以說這種的情況下，NL Join是完全沒有問題的，支援綽綽有餘。

那麼這個問題我們怎麼分析呢，我們分而治之。裡面有幾個子查詢，我們可以拆開來看。

在子查詢的執行計劃中，我竟然看到了“MERGE JOIN CARTESIAN”的字樣。

哪裡來的笛卡爾積？

我拿出一個子查詢來解釋。

select xxxx from h1_first_dev d, ua_td_active_log a
where d.dt = to_date(:1, 'yyyy-mm-dd')
and a.dt = :2
and d.deviceid is not null
and (d.deviceid = a.idfa or d.deviceid = a.mac or
a.idfa =lower(utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5(input_string => d.deviceid))))

繫結變數值都是日期相關的。

Name    Position    Type    Value   
:1    1    VARCHAR2(32)    2017-01-06
:2    2    VARCHAR2(32)    2017-01-06
:3    3    VARCHAR2(32)    2017-01-06
:4    4    VARCHAR2(32)    2017-01-06

可以看出h1_first_dev的欄位dt是日期型，ua_td_active_log的dt是字元型。

在這種情況下就是兩個結果集的運算了。

本來根據時間條件，可以從兩個表裡各篩取出很小的一部分資料。

但是兩個表的欄位型別又不相同，一個date型，一個varchar2，而且沒有對映關係，最要命的是後面的條件and (d.deviceid = a.idfa or d.deviceid = a.mac or ... 最後兩個表的關聯關係被這個條件給徹底破壞了。

所以如此一來，原本3000條左右的資料的關聯，硬是給對映成了4千萬資料的關聯（表裡的資料有4千萬），想想就是心塞。

我用下面的一個圖來表達我的這種心情吧。

而且裡面竟然還用到了幾個比較少見的包dbms_obfuscation_toolkit utl_raw       導致CPU解析非常繁忙。

檢視SQL Monitor報告，基本都被PL/SQL的包給攻佔了。

所以這個問題解決起來其實還是要花一些功夫的。我對比模擬測試了一下。取得增量資料，然後執行同樣的SQL

SQL> create table H1_FIRST_DEV as select * from mbi.H1_FIRST_DEV where dt=to_date('2017-01-06','yyyy-mm-dd');   
SQL> create table UA_TD_ACTIVE_LOG as select * from mbi.UA_TD_ACTIVE_LOG where dt='2017-01-06';

沒有任何索引，也沒有收集統計資訊，同樣的語句消耗在1秒內。
SQL> @sqltune1.sql
no rows selected
Elapsed: 00:00:00.60  

而更為奇怪的是返回竟然是0行。

這個時候回頭來看開始效能較差的執行計劃統計資訊，就能理解了。

所以這個問題的解決方案可以穩定執行計劃，讓dt的過濾優先。或者是重新修改SQL語句，把一些邏輯做精簡和改進，比如PL/SQL呼叫的包體可以通過資料過濾來處理，畢竟是很小的一部分資料。
    當然和開發的同學溝通後，我發現問題還沒想象的那麼簡單，因為還有幾個更有挑戰的SQL要優化，下一篇來談談怎麼把一個平均執行20秒的核心SQL優化為2秒，關聯的表都是千萬級別。

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