merge語句導致的效能問題緊急優化

晚上正在休息的時候，突然收到一封報警郵件。
報警內容: CPU utilization is too high
------------------------------------
報警級別: PROBLEM
------------------------------------
監控專案: CPU idle time：59.11 %
------------------------------------
這個報警資訊已經非常明確，CPU使用率很緊張了。這臺伺服器上執行著Oracle和MySQL例項，所以第一感覺是不是MySQL的慢查詢導致的，結果登入到伺服器端，發現MySQL的連線數很少，沒有發現慢查詢，那麼問題很可能就和Oracle有關係了，使用top檢視，果然幾個top的程式都是帶有LOCAL=NO的字樣，很可能是應用端觸發的SQL導致。
檢視v$session的資訊，發現其中一個會話已經執行一條SQL超過了40分鐘。這個SQL是merge語句，看來merge又攤上事了。

語句的內容如下：

我們來看看語句的執行計劃情況，如下：

可以明顯看到一個全表掃描，這個表中的資料大概是700多萬，就算全表掃描也應該幾分鐘就出結果了。怎麼執行了40分鐘了還沒有任何反應。
我們來看看執行的瓶頸在哪裡。還是使用SQL Monitor來定位，一目瞭然。

可以明顯看出瓶頸在全表掃描的部分。

表中的資料有700多萬，而經過排查發現竟然沒有任何索引。所以問題的原因就更加明顯了。
我們需要再總結一下，問題的原因定位到了，執行計劃的效率極低。怎麼改進。
1.建立索引，這就無形帶來幾個問題，基於哪些列來建立索引
2.這個表中目前存在頻繁的DML操作，如何建立索引
3.為什麼執行計劃的消耗如此之大
我們來一個一個看問題，首先是建立索引，這個看起來目標明確，但是具體來做，就需要一些技巧了， 對於這類的問題，個人比較偏好dbms_sqltune.report_tuning_task，因為呼叫方便，簡單而且能很快出結果。不到1分鐘分析結果就出來了，提出了3點建議，一個就是收集統計資訊，一個是修改SQL Profile，還有一個就是建立索引，我們的目標明確，就是索引的改進，建議裡面會根據資料的選擇度來進行評估和分析，還是比較全面的。

但是建立索引的過程中，這個DDL肯定會排斥其他的DML,很容易出現這樣的錯誤。

這種情況下，新特性ddl_lock_timeout就值得推薦了，我們可以設定一個略微長一些的超時時間，讓它在後臺自己去試，很快就能得到結果。

回頭一看前兩個問題已經解釋了，那麼第3個問題，為什麼執行計劃的差別如此之大，就算全表也不至於那麼慢啊。
語句的謂詞部分會做出解釋：

可以看到走了filter過濾，using部分和表中的資料對映存在重大的偏差，內部的對映竟然是一大堆的case when的形式。
當然這個語句優化之後，效能提升也很明顯。精確到分鐘級，效果提升還是不錯的。

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