Oracle臨時表空間不足和批處理緩慢問題探討

2012-01-12 09:03:14譚紅斌

天中學刊 2012年2期

譚紅斌

（駐馬店職業技術學院，河南駐馬店 463000）

譚紅斌

（駐馬店職業技術學院，河南駐馬店 463000）

數據庫系統出現問題時，會導致整個系統不穩定．文章分析了Orcale臨時表空間不足和批量處理時運行速度緩慢的原因，并給出了解決辦法．

SQL；hash join；臨時表空間

1 問題的提出

筆者所在學院的學生管理系統后臺使用的是Oracle數據庫．在每天凌晨批處理期間，ALERT日志頻繁報出臨時表空間不足的告警信息，導致批量程序運行失敗．在對臨時表空間進行多次擴容后（從40 GB增加到 60 GB，再增加到 100 GB），問題仍然無法有效地解決．即使在批量執行成功的時候，也需要執行很長時間才能完成．為了解決此問題，急需對該問題進行全面排查，以找到解決辦法．

2 問題的分析和解決

根據以往的經驗，筆者分析認為造成這一問題的原因可能是服務器硬件存在性能問題、操作系統設置有問題或者是應用本身的 SQL語句編寫有問題．為此，筆者衡量服務器的運算能力以及該數據庫的負載，使用測試工具測試，并未發現服務器及Oracle本身的瓶頸問題．因此，把問題定位在應用邏輯方面，并做了如下詳細的測試．

2.1 編寫記錄使用臨時表空間SQL語句的腳本

查看數據庫 AWR報告，發現該報告未能準確體現出占用臨時表空間最高的SQL語句．為此，筆者編寫了一個記錄使用臨時表空間SQL語句的shell腳本，用來記錄使用臨時表空間最高的SQL語句，并將其保存在特定的表中．該腳本的部分SQL語句編寫如下：

2.2 抓取最消耗臨時表空間的SQL語句

在臨時表空間已增加到 100 GB的情況下，重新進行批量運行，出現臨時表空間不足錯誤時，筆者根據編寫的監控腳本，順利抓取到了最消耗臨時表空間的兩個語句．其中，語句1消耗約40 GB臨時表空間，語句2消耗約60 GB臨時表空間．由于語句1和語句2的語法結構完全一致，只是批量處理的數據不一樣，故這兩個語句可用同一語句描述，具體如下：

2.3 分析執行計劃

分析語句發現，執行計劃使用了hash join連接技術，當驅動表無法在內存中裝載時，驅動表一次或多次被置換到臨時表空間，發生one-pass或muti-pass的現象，導致臨時表空間的消耗和執行效率的低下．由于語句1和2結構完全一致，因此對語句1分析和優化即可，根據監控腳本所抓取到的SQL語句及其id，我們得到了如圖1所示的執行計劃信息．由圖1可以看出，該執行計劃的問題在于出現了“merge join cartesian”合并連接笛卡爾乘積，而其乘積結果作哈希連接的驅動表．由于PGA（程序全局區）中的hash area無法完全裝載該表，導致出現 one-pass、muti-pass，大量占用臨時表空間，程序運行效率低下．該合并連接笛卡爾乘積將產生約1 700萬條記錄，而PGA大小為 1 628 M，hash area最多能使用 1628*5% = 80 M，顯然80 M的hash area無法裝載更多記錄的運算結果，繼而大量占用臨時表空間，導致程序運行效率低下．

SQL優化器CBO之所以選擇合并連接笛卡爾乘積，原因在于CBO評估合并連接笛卡爾乘積返回的結果為1行，檢查設置該語句的三個分區表的統計信息，發現統計信息為0行．至此，問題已經清楚．由于批量的流程為數據加載、運算、數據全刪除．因此系統自動統計信息時往往在批量數據全部刪除后進行，導致采集的統計信息的為0行，繼而CBO選擇了合并連接笛卡爾乘積，導致了錯誤的執行計劃、大量的臨時表空間占用和性能低下的執行計劃．