最近遇到一個問題，在觀察Oracle DB 11g時，發現Undo Tablespace使用率有65%，約佔37GB，所以查了一下是哪一個Session在佔用。但是卻發現所有的Session都沒有佔用大量Undo的情形，這就奇怪了。

先用下列的SQL查詢一下Undo Tablespace的使用狀況，發現UNEXPRIED的值很高

select DISTINCT STATUS "狀態", COUNT(*) "EXTENT數量",
       SUM(BYTES) / 1024 / 1024 / 1024 "UNDO大小（GB）"
  FROM DBA_UNDO_EXTENTS
  GROUP BY STATUS;

查session使用undo空間量，發現沒有特別的session佔用大量的undo

select s.sid, substr( s.program, 1, 15 ) program,
s.machine,t.xidusn || '.' || t.xidslot || '.' || t.xidsqn tx_addr,
t.status, t.start_time, tbs.tablespace_name tbs_name,
round( t.used_ublk * tbs.block_size /1048576, 2 ) undo_size_mb,
t.used_urec
from v$transaction t, v$session s, v$parameter p, DBA_tablespaces tbs
where t.ses_addr = s.saddr
and p.name = '<UNDO_TABLESPACE_NAME>'
and p.value = tbs.tablespace_name
order by t.used_ublk desc;

在網路找了許多的文件後，原來在Oracle 10gR2之後，預設會啟用automatic undo management（AUM）功能，在Undo Tablespace對應的Data File不是自動擴充，且Undo空間又比較大的時候，tuned_undoretention的值是根據UNDO表空間大小的百分比来計算的，在一些情况下會將tuned_undoretention的值調整得特别大。換句話說，就是自動加大Undo的保留時間。

先看一下DB設定的undo_retention，得到的是10800秒；

接著依下列的SQL，看看tuned_undoretention的變化，結果出現了90031秒

select maxqueryid ,to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time,tuned_undoretention
from v$undostat order by end_time desc;

由上面的結果可以推論，因為保留時間變長了，導致現在的Undo Unexpired的值很多，不過在文件中有提到雖然Undo的空間使用率很滿，但是如果有Session遇到真的要使用空間時，會自動調整tuned_undoretention來釋放空間。

另外，由MAXQUERYID這個欄位可以得知是哪一個SQL需要較長的tuned_undoretention，由下列的SQL來找出來：

select * from v$sqltext where sql_id='2jxkb8q6sxua9' order by piece ;

查詢之後發現這個SQL就是我之前有看到因為執行很久，結果產生ora-01555的那個SQL，從v$session找一下現在是不是還有Session正在執行這個SQL，結果發現有兩個正在執行。於是我就將這兩個Session刪掉後，再重新觀察tuned_undoretention，可以發現tuned_undoretention的值變小了，而Undo可用的空間也變大了。