迄今見到最長的SQL...濫用Union

上週，Production DB A的user 反應他們作業的速度變得很慢。

這臺DB用的是IBM X3850 16Core 2.93Ghz 16GB Memory AMS Storage，平時Performance表現十分良好，甚至可以支援一部分Report的作業。

[@more@]

SSH連線上去之後，開啟TOP,IOSTAT觀察：

IO在正常範圍，最大IOWAIT不超過5%。

CPU Utilization 比一般要高，時有衝到50%整的現象。

對SMP System而言，這可以認為有Process持續消耗大量CPU。

這是初步印象。

連入Oracle,觀察了下v$session_wait：

select * from v$session_wait where event not in

(select event from stats$idle_event)

發現很多Latch Free.

進一步鑑別：

SELECT l.name,s.* FROM v$session_wait s,v$latchname l

WHERE s.event='latch free' and s.P2=l.LATCH#

發現幾乎一半是Library Cache, 一半是Library Cache Pin。

Client還能繼續執行，排除Compile造成objects被長期鎖住的問題。

那就應該是shared_pool本身出現效能問題。

但這臺DB 的通常應用很穩固，經過一年多調整，已經相當穩定，所以判斷為有新的應用上線。

查了下正在執行的SQL和Library Cache Pin 的Session在執行的SQL:

發現了一個令人驚訝的SQL:

從v$sqltext中Copy到TXT上竟然有140KB.

仔細察看是 N個SQL Union在一起，看來是某個程式自動生成的動態SQL。通知AP人員全部停止該應用後速度恢復正常。