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

譚紅斌

（駐馬店職業(yè)技術(shù)學(xué)院，河南 駐馬店 463000）

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

譚紅斌

（駐馬店職業(yè)技術(shù)學(xué)院，河南 駐馬店 463000）

數(shù)據(jù)庫系統(tǒng)出現(xiàn)問題時，會導(dǎo)致整個系統(tǒng)不穩(wěn)定．文章分析了Orcale臨時表空間不足和批量處理時運(yùn)行速度緩慢的原因，并給出了解決辦法．

SQL；hash join；臨時表空間

1 問題的提出

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

2 問題的分析和解決

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

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

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

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

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

2.3 分析執(zhí)行計劃

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

SQL優(yōu)化器CBO之所以選擇合并連接笛卡爾乘積，原因在于CBO評估合并連接笛卡爾乘積返回的結(jié)果為1行，檢查設(shè)置該語句的三個分區(qū)表的統(tǒng)計信息，發(fā)現(xiàn)統(tǒng)計信息為0行．至此，問題已經(jīng)清楚．由于批量的流程為數(shù)據(jù)加載、運(yùn)算、數(shù)據(jù)全刪除．因此系統(tǒng)自動統(tǒng)計信息時往往在批量數(shù)據(jù)全部刪除后進(jìn)行，導(dǎo)致采集的統(tǒng)計信息的為0行，繼而CBO選擇了合并連接笛卡爾乘積，導(dǎo)致了錯誤的執(zhí)行計劃、大量的臨時表空間占用和性能低下的執(zhí)行計劃．

2.4 重寫SQL后進(jìn)行測試

根據(jù)每個系統(tǒng)每日數(shù)據(jù)并不大的情況，理想的執(zhí)行計劃應(yīng)為：子查詢中的D表和E表做hash join，所產(chǎn)生的結(jié)果集做為另外一個hash join的probe表去探測A表．將復(fù)合記錄的結(jié)果返回．由此，子查詢不應(yīng)該做view merge．我們將語句改寫，限制數(shù)據(jù)庫CBO優(yōu)化器做出錯誤的執(zhí)行計劃選擇．

語句重寫后，在很短時間內(nèi)即可完成，同時，未出現(xiàn)對臨時表空間的消耗現(xiàn)象．

以下為修改后語句的部分內(nèi)容，其執(zhí)行計劃為筆者所預(yù)期的理想路徑，執(zhí)行計劃信息如圖2所示．

圖2 修改后的語句執(zhí)行計劃信息

2.5 問題的解決

語句在修改前在執(zhí)行時間超過 1個小時仍未結(jié)束，而經(jīng)過語句重寫后，測試顯示不到1分鐘即可完成，同時也消除了對臨時表空間的過量消耗的問題．

3 結(jié)語

根據(jù)以上分析，筆者認(rèn)為要提高SQL語句的編寫效率，簡化SQL的處理邏輯，在應(yīng)用編寫時應(yīng)考慮到表的增量以及相應(yīng)的執(zhí)行計劃，在關(guān)鍵SQL語句中使用顯式執(zhí)行計劃．系統(tǒng)關(guān)鍵SQL語句應(yīng)考慮：

(1) 使用 hint穩(wěn)定執(zhí)行計劃，通過在子查詢中加入no_merge的hint防止CBO優(yōu)化器做view merge．同時，由于子查詢內(nèi)部的謂詞中已有分區(qū)鍵，且CBO可有效使用分區(qū)去除，故通過加入no_index的hint可使對單個分區(qū)掃描的索引范圍更加有效．

(2) 基于批量處理的流程，可在調(diào)用批量程序運(yùn)行的shell腳本，數(shù)據(jù)加載完畢，并對相關(guān)的三張表進(jìn)行統(tǒng)計信息的收集之后，再開始做merge的批量操作．

[1] 滕永昌．Oracle數(shù)據(jù)庫系統(tǒng)管理[M]．北京：清華大學(xué)出版社，2003：80―96．

[2] 鄭阿奇．ORACLE實用教程[M]．北京：電子工業(yè)出版社，2009：120―160．

[3] 文平．ORACLE大型數(shù)據(jù)庫系統(tǒng)在AIXUNIX上的實戰(zhàn)詳解[M]．北京：電子工業(yè)出版社，2010：66．

[4] 王彬．Oracle IIg基礎(chǔ)與提高[M]．北京：電子工業(yè)出版社，2009：230―261．

[5] 肖平．基于Oracle應(yīng)用服務(wù)器的Web開發(fā)技術(shù)[M]．北京：清華大學(xué)出版社，2010：90．

TP392

A

1006-5261(2012)02-0036-03

2012-03-29

譚紅斌（1975―），男，河南西平人，講師．

〔責(zé)任編輯 牛建兵〕