| 導購 | 订阅 | 在线投稿
分享
 
 
 

DBA:臨時表遊標未釋放導致回滾段空間不足

來源:互聯網網民  2008-06-06 06:00:05  評論

這篇論壇文章(賽迪網技術社區)根據筆者在工作中遇到的臨時表遊標未釋放導致回滾段空間不足的錯誤現象及解決方法進行詳盡的介紹,更多內容請參考下文:

一大早,突然收到生産系統報出多個ORA-01650錯誤,報回滾段空間不足。

先分析一下相關背景。産生報錯的程序是一個數據處理模塊,每天會將其他系統傳過來的平面文件中的內容處理後放入數據庫中,事務量很大。我們的系統是9i,劃分了16個回滾段,其中兩個大的batch回滾段,每個batch回滾段有6G的足夠空間。而數據處理模塊會在事務中指定使用BATCH1。

再分析報錯的模塊。檢查相關的數據事務處理部分,由于業務需要保持數據的一致性,需要處理完1個文件後才能提交,中間如果出錯就要全部回滾。經過確認,這部分代碼有很長時間沒有做改動了。然後再確認數據量,可以確認,今天(周五)是一周之內文件內容最少的一天。也就是說,如果由于數據量引起錯誤,其他時間的概率應該更大。

但是,有一點需要注意。指定回滾段是針對事務的,不是針對回滾段。也就是說,我們可以指定某個事務只使用某個回滾段,但是不能保證這個回滾段只被這個事務事務。當一個事務申請使用回滾段時,如果沒有自己指定,oracle就會根據當時的各個回滾段的使用情況,分配一個最合適的回滾段給這個事務使用。因此,報回滾段空間不足的事務可能不一定就是導致回滾段空間不足的事務。還有一種可能就是,事務所指定的回滾段被其他事務所占用了。

于是我們就檢查是否還有其他事務占用了該回滾段。

SELECT s.sid, s.username, s.osuser, s.machine, s.program,

t.xidusn,t.ubafil,t.ubablk,t.used_ublk, t.log_io, t.phy_io, t.cr_get, t.cr_change,

r.name, q.sql_text

FROM v$session s,v$transaction t, v$RollName r, v$sqlarea q

WHERE s.saddr=t.ses_addr

and t.xidusn = r.usn

and s.sql_address = q.address(+)

and s.sql_hash_value = q.hash_value(+)

And r,name = 'RBS_BATCH1';

果然發現有5個事務在占用BATCH1。但是發現會話狀態爲INACTIVE。這說明它們當時並沒有運行INSERT/UPDATE/DELETE語句(曾經運行過,事務沒有結束),而是將回滾段資源hung住了。

再查下那些對象被hung在BATCH1中,

select l.session_id, l.os_user_name, l.oracle_username, o.owner, o.object_name, t.xidusn,t.ubafil,t.ubablk,t.used_ublk, t.used_urec,

t.log_io, t.phy_io, t.cr_get, t.cr_change, r.name

from v$locked_object l, dba_objects o, v$transaction t, v$RollName r

where l.object_id = o.object_id

and l.xidusn = t.xidusn

and l.xidslot = t.xidslot

and l.xidsqn = t.xidsqn

and t.xidusn = r.usn;

嗯,都是同一個對象:「TMP_CNT_GRP」。通過SID確認,確實上面的事務都是將這個對象hung在RBS中的。

經過檢查,原來這個對象是一個臨時表。我們知道,臨時表對象平時是不存在數據的。只有當一個會話使用臨時表,並向表中插入數據後,oracle才會在臨時表空間上創建它的數據對象。臨時表數據之所以只被所調用會話看到,是實際上是在每個會話中創建了一個單獨的數據對象,有各自的數據對象標號。因此盡管是同一個臨時表,每個會話只是copy一個表結構,而創建了不同的數據對象,這樣,會話之間就不會有數據幹擾。而在一個會話中,對臨時表數據對象的處理跟普通數據對象處理基本相同,其中就包括臨時表對象在事務中的數據改動也會有回滾信息的産生。

回到我們的問題中。通過V$SESSION和V$SQL_AREA查到,這些會話都是調用了一個PLSQL函數,而且都是通過java調用的。

Review代碼,終于發現潛在問題了:這個函數的結果是返回一個遊標,而遊標恰恰關聯了這張臨時表。

INSERT INTO TMP_CNT_GRP ...

SELECT...

... ...

open v_cursor FOR

select TMP.CDE,

CAR.ID,

CAR.NME,

COUNT(DISTINCT TMP.NUM) TOTAL_CNT

from TMP_CNT_GRP TMP,

CSS_CAR CAR

WHERE TMP.ID = CAR.ID

GROUP BY TMP.CDE, CAR.ID, CAR.NME;

RETURN v_cursor;

DELETE TMP_CNT_GRP;

(這段代碼其實還存在一個問題,也就是最後的DELETE語句根本不會被調用)

從這段代碼中可以看到,實際上在整個函數當中,臨時表的數據根本不會被釋放;而且也沒有提交和回滾事務(盡管這是一個會話級的臨時表)。占用的回滾段也不會被釋放。這就存在這樣的潛在問題,如果調用者不關閉會話或提交/回滾事務的話,它所占用的回滾段就不會被釋放。事實上,經過讓java開發人員檢查代碼,果然發現客戶端在打開會話後,就沒有關閉,知道客戶端本身結束。

最後的解決辦法:

1、因爲這是一個會話級的臨時表,數據在事務提交後繼續保留,因此在PLSQL函數中的insert語句後加上commit;

2、Java代碼在使用完遊標後關閉會話。

 
特别声明:以上内容(如有图片或视频亦包括在内)为网络用户发布,本站仅提供信息存储服务。
 
這篇論壇文章(賽迪網技術社區)根據筆者在工作中遇到的臨時表遊標未釋放導致回滾段空間不足的錯誤現象及解決方法進行詳盡的介紹,更多內容請參考下文: 一大早,突然收到生産系統報出多個ORA-01650錯誤,報回滾段空間不足。 先分析一下相關背景。産生報錯的程序是一個數據處理模塊,每天會將其他系統傳過來的平面文件中的內容處理後放入數據庫中,事務量很大。我們的系統是9i,劃分了16個回滾段,其中兩個大的batch回滾段,每個batch回滾段有6G的足夠空間。而數據處理模塊會在事務中指定使用BATCH1。 再分析報錯的模塊。檢查相關的數據事務處理部分,由于業務需要保持數據的一致性,需要處理完1個文件後才能提交,中間如果出錯就要全部回滾。經過確認,這部分代碼有很長時間沒有做改動了。然後再確認數據量,可以確認,今天(周五)是一周之內文件內容最少的一天。也就是說,如果由于數據量引起錯誤,其他時間的概率應該更大。 但是,有一點需要注意。指定回滾段是針對事務的,不是針對回滾段。也就是說,我們可以指定某個事務只使用某個回滾段,但是不能保證這個回滾段只被這個事務事務。當一個事務申請使用回滾段時,如果沒有自己指定,oracle就會根據當時的各個回滾段的使用情況,分配一個最合適的回滾段給這個事務使用。因此,報回滾段空間不足的事務可能不一定就是導致回滾段空間不足的事務。還有一種可能就是,事務所指定的回滾段被其他事務所占用了。 于是我們就檢查是否還有其他事務占用了該回滾段。 SELECT s.sid, s.username, s.osuser, s.machine, s.program, t.xidusn,t.ubafil,t.ubablk,t.used_ublk, t.log_io, t.phy_io, t.cr_get, t.cr_change, r.name, q.sql_text FROM v$session s,v$transaction t, v$RollName r, v$sqlarea q WHERE s.saddr=t.ses_addr and t.xidusn = r.usn and s.sql_address = q.address(+) and s.sql_hash_value = q.hash_value(+) And r,name = 'RBS_BATCH1'; 果然發現有5個事務在占用BATCH1。但是發現會話狀態爲INACTIVE。這說明它們當時並沒有運行INSERT/UPDATE/DELETE語句(曾經運行過,事務沒有結束),而是將回滾段資源hung住了。 再查下那些對象被hung在BATCH1中, select l.session_id, l.os_user_name, l.oracle_username, o.owner, o.object_name, t.xidusn,t.ubafil,t.ubablk,t.used_ublk, t.used_urec, t.log_io, t.phy_io, t.cr_get, t.cr_change, r.name from v$locked_object l, dba_objects o, v$transaction t, v$RollName r where l.object_id = o.object_id and l.xidusn = t.xidusn and l.xidslot = t.xidslot and l.xidsqn = t.xidsqn and t.xidusn = r.usn; 嗯,都是同一個對象:「TMP_CNT_GRP」。通過SID確認,確實上面的事務都是將這個對象hung在RBS中的。 經過檢查,原來這個對象是一個臨時表。我們知道,臨時表對象平時是不存在數據的。只有當一個會話使用臨時表,並向表中插入數據後,oracle才會在臨時表空間上創建它的數據對象。臨時表數據之所以只被所調用會話看到,是實際上是在每個會話中創建了一個單獨的數據對象,有各自的數據對象標號。因此盡管是同一個臨時表,每個會話只是copy一個表結構,而創建了不同的數據對象,這樣,會話之間就不會有數據幹擾。而在一個會話中,對臨時表數據對象的處理跟普通數據對象處理基本相同,其中就包括臨時表對象在事務中的數據改動也會有回滾信息的産生。 回到我們的問題中。通過V$SESSION和V$SQL_AREA查到,這些會話都是調用了一個PLSQL函數,而且都是通過java調用的。 Review代碼,終于發現潛在問題了:這個函數的結果是返回一個遊標,而遊標恰恰關聯了這張臨時表。 INSERT INTO TMP_CNT_GRP ... SELECT... ... ... open v_cursor FOR select TMP.CDE, CAR.ID, CAR.NME, COUNT(DISTINCT TMP.NUM) TOTAL_CNT from TMP_CNT_GRP TMP, CSS_CAR CAR WHERE TMP.ID = CAR.ID GROUP BY TMP.CDE, CAR.ID, CAR.NME; RETURN v_cursor; DELETE TMP_CNT_GRP; (這段代碼其實還存在一個問題,也就是最後的DELETE語句根本不會被調用) 從這段代碼中可以看到,實際上在整個函數當中,臨時表的數據根本不會被釋放;而且也沒有提交和回滾事務(盡管這是一個會話級的臨時表)。占用的回滾段也不會被釋放。這就存在這樣的潛在問題,如果調用者不關閉會話或提交/回滾事務的話,它所占用的回滾段就不會被釋放。事實上,經過讓java開發人員檢查代碼,果然發現客戶端在打開會話後,就沒有關閉,知道客戶端本身結束。 最後的解決辦法: 1、因爲這是一個會話級的臨時表,數據在事務提交後繼續保留,因此在PLSQL函數中的insert語句後加上commit; 2、Java代碼在使用完遊標後關閉會話。
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有