oracle 10g undo表空間使用率居高不下bug

Tuesday, March 22, 2011

Collection by H.sdon

是從網上查詢而來。記錄下來。

屬於10。2的一個新特性的bug，雖然不會有什麼影響。每次監控這麼多使用率還是不是很好看。呵呵

對於UNDO表空間大小的定義需要考慮UNDO_RETNETION引數、產生的UNDO BLOCKS/秒、UNDO BLOCK的大小。undo_retention：對於UNDO表空間的資料檔案屬性為autoextensible,則undo_retenion引數必須設定，UNDO資訊將至少保留至undo_retention引數設定的值內，但UNDO表空間將會自動擴充套件。對於固定UNDO表空間，將會透過表空間的剩餘空間來最大限度保留UNDO資訊。如果FIXED UNDO表空間沒有對保留時間作GUARANTEE（alter tablespace xxx retention guarantee;），則undo_retention引數將不會起作用。（警告：如果設定UNDO表空間為retention guarantee，則未過期的資料不會被複寫，如果表空間不夠則會導致DML操作失敗或者transation掛起）

有自動Automatic Undo Retention Tuning這個特性。設定的undo_retention引數只是一個指導值,，Oracle會自動調整Undo (會跨過undo_retention設定的時間)來保證不會出現Ora-1555錯誤.。透過查詢V$UNDOSTAT（該檢視記錄4天以內的UNDO表空間使用情況，超過4天可以查詢DBA_HIST_UNDOSTAT檢視）的tuned_undoretention（該欄位在10G版本才有，9I是沒有的）欄位可以得到Oracle根據事務量（如果是檔案不可擴充套件，則會考慮剩餘空間）取樣後的自動計算出最佳的retenton時間.。這樣對於一個事務量分佈不均勻的來說,，就會引發潛在的問題--在批處理的時候可能Undo會用光，而且這個狀態將一直持續，不會釋放。

如何取消10g的auto UNDO Retention Tuning，有如下三種方法：

from metalink 420525.1：Automatic Tuning of Undo_retention Causes Space Problems

1.) Set the autoextend and maxsize attribute of each datafile in the undo ts so it is autoextensible and its maxsize is equal to its current size so the undo tablespace now has the autoextend attribute but does not autoend:
SQL> alter database datafile ''
autoextend on maxsize ;

With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the undo tablespace size, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

2.) Set the following hidden parameter in init.ora file:
_smu_debug_mode=33554432
or

SQL> Alter system set "_smu_debug_mode" = 33554432;

With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the fixed size undo tablespace, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

3.) Set the following hidden parameter in init.ora:
_undo_autotune = false

or

SQL> Alter system set "_undo_autotune" = false; 可動態調整

---另一個

但奇怪的地方在於當時該資料庫並不是特別繁忙，並不是在波峰時段。當檢視stats$undostat時，一個引數吸引了我的注意力：UNXPSTEALCNT

我們來看看UNXPSTEALCNT的解釋：

UNXPBLKREUCNT: Number of unexpired undo blocks reused by transactions

一般來說該引數都應該等於0，但是這個引數在那段時間是大於0的。因為只有當undo tablespace不夠存放undo_retention時間段內的資料的時候，才會發生unexpired undo extents stealing。再去檢視stats$rollstat，發現但是RSSIZE和undo tablespace大小是一樣的，這就說明當時undo tablespace確實不夠用了。

那麼為什麼在系統不是很繁忙的時候會出現undo不夠用的情況呢，如果說不夠用，那在波峰時段應該問題更加嚴重才對。

檢視stats$undostat.tuned_undoretention引數發現了問題所在。從10.2版本開始，oracle預設採用自動調整undo retention的方法，根據你undo tablespace的大小以及系統的繁忙程度(v$undostat中資訊）自動調整undo_retention引數，所以在10g的資料庫上你會經常發現undo tablespace永遠是滿的，因為當你undo tablespace有空閒空間時，系統自動調大undo_retention來保留更多的undo blocks。這一方法有利於時間長的查詢，但是對於典型的OLTP系統來說不太適用，因為OLTP上不太可能跑如此長時間的查詢，而且在很繁忙的OLTP上還會導致上面所遇到的問題。oracle真是吃力不討好。

出問題前一天，資料庫做維護被重啟過，因為剛起來資料庫很空閒，所以v$undostat.tuned_autoretention很大，undo tablespace被撐滿，雖然tuned_autoretention一直在降，但是還是沒有趕上系統warm up的速度，導致資料庫出現了問題。

該功能可以透過_undo_autotune引數被disable，disable後v$undostat不在更新。

_undo_autotune : enable auto tuning of undo_retention

該引數可以線上修改：

alter system set “_undo_autotune” = false;

【Oracle】-oracle 10g undo表空間使用率居高不下bug

相關文章