調整oracle的一個pga引數,讓一個sql語句執行飛快

 

調整oracle的一個pga引數，讓一個sql語句執行飛快

 

最近有一個專案，專案經理反饋說某個功能模組在測試機器上3~5s就可以開啟；但是在正式機器上需要10~20分鐘才能開啟。

專案經理懷疑是正式系統的機器硬體資源有問題。

我接到專案經理的反饋，笑而不語，反問他怎麼可能會懷疑我們正式系統的機器有問題呢。要知道，正式機器的記憶體、cpu個數、磁碟讀寫速度，那都比測試機器快好多倍。

於是，我讓他操作操作一下那個慢的功能模組，我好去oracle裡面抓一下sql語句。

最終，我透過awr、ash報告，找到了正式機器上的那個sql語句，並找到了相應等待事件：

 

透過上圖可以看出，在專案經理執行那個慢的功能模組的過程中，有兩個等待事件比較嚴重：direct path read temp和direct path write temp。

相應的慢sql是如下圖：

 

 

上圖的sql語句太複雜，看不懂。

 

當然，也可以在專案經理執行那個功能模組的過程中，在oracle裡面執行如下語句，看資料庫當前正在跑什麼語句，並對應的等待事件是什麼：

 select b.sid,a.sql_text,b.status,b.last_call_et,b.event from v$sql a,v$session b where a.sql_id=b.sql_id;

 

 

可以看到，上圖顯示的結果和awr、ash得到的結果一樣。

 

 

好啦，慢sql和等待事件都找到了。那我就把這個sql語句分別在測試oracle和正式oracle裡面跑一遍吧，我就不信，測試oracle比正式oracle快。

 

等我執行一對比，讓我傻眼了，還真是，測試oracle 3~5s就能返回結果，而正式oracle 10分鐘才返回結果。這不科學！

 

在正式oracle中執行那個sql語句過程中，我發現，正式oracle伺服器的cpu和記憶體利用率都不高，反而磁碟io非常高。

執行iostat –x 1

 

透過上圖可以看出，正式oracle的%util已經到了95%，磁碟利用率非常高，說明磁碟io出現問題了！！！

 

為了能找到問題的原因，剛才不是說測試oracle執行快，而正式oracle慢嗎。那我們就看看這個sql語句在兩個資料庫的執行計劃。

下圖是測試庫的執行計劃：

 

 

下圖是正式庫的執行計劃：

 

我去，執行計劃完全不一樣。

要知道，測試庫和正式庫的表和資料完全一致啊，可為什麼會出現兩種執行計劃呢。

 

最終我發現了兩個資料庫的版本和作業系統版本都不一樣：

測試庫：oracle 11.2.0.1&?os 5.5

正式庫：oracle 11.2.0.4&?os 6.5

 

 

難道是版本不一樣導致的嗎？

 

這時有點一頭霧水，我於是諮詢了我的弟弟（順便打個廣告，他現在和人合夥開了個培訓機構，有興趣的可以訪問 甲骨人瞭解）。他說你手工收集一下統計資訊，於是我手工執行了一下dbms_stats.gather_schema系統包，可是執行計劃還是那樣。

這時我不知所措了。

 

對了，剛才不是說有兩個等待事件direct path read temp和direct path write temp嗎。

於是，死磕這兩個等待事件。

百度和谷歌了很長時間，都說的意思是pga空間不夠，發生了讀臨時表空間temp的情況，解決辦法就是加大pga空間。

 

 

我的解決辦法是，我將workarea_size_policy    設定為manual：

 

 

在執行那個sql語句，速度從10分鐘降到3~5s了。

 

在看他的執行計劃，和測試oracle的執行計劃一模一樣了：

 

 

 

完。