標籤：style 使用 os io for art 問題 cti
Oracle 10g及後續版本較以前版本有一新特性即自動調整undo retention時間，大大簡便了管理，對於自動擴充（autoextend on）的undo資料表空間，參數undo_retention設定成為Oracle自動調節undo retention的最低閥值。對於非自動擴充（autoextend off），非guarantee 的undo 資料表空間，Oracle會根據undo資料表空間大小和v$undostat的曆史資訊（是否統計undo資訊是由隱含參數 _collect_undo_stats決定的，預設情況為TRUE）最大可能性保留undo資訊。以最大化的減少類似ORA-01555 等錯誤發生。在這種情況下的UNDO RETENTION就基本沒有用處了。預設情況下 _UNDO_AUTOTUNE =TRUE, 開啟UNDO自動最佳化功能。經過最佳化的UNDO RETENTION可以在V$UNDOSTAT的 TUNED_UNDORETENTION 中看到， 一般oracle每10分鐘寫一條unod資料表空間使用方式記錄到V$UNDOSTAT, 包括 TUNED_UNDORETENTION 。
當然這一特性是由隱含參數_undo_autotune控制的，預設情況下設定為TRUE，部分特殊情況下會將其設為FALSE，如startup upgrade。
如果參數設為false，oracle 不會根據資料表空間大小等自己調整undo retention大小，undo retention設定小時容易出現ora-01555 錯誤。比如資料表空間足夠大，但還是會出現ora-01555。
在Oracle 10g版本中可以使用V$UNDOSTAT視圖用於監控執行個體中當前事務使用UNDO資料表空間的情況。視圖中的每行列出了每隔十分鐘從執行個體中收集到的統計資訊。每行都表示了在過去7*24小時裡每隔十分鐘UNDO資料表空間的使用方式，事務量和查詢長度等資訊的統計快照。
UNDO資料表空間的使用方式會因事務量變化而變化，一般我們在計算時同時參考UNDO資料表空間的平均使用方式和峰值使用方式。
以下SQL語句用於計算過去7*24小時中UNDO資料表空間的平均使用量：
col UNDO_RETENTION for a15
col DB_BLOCK_SIZE for a15
select ur undo_retention, dbs db_block_size, ((ur * (ups * dbs))+(dbs * 24))/1024/1024 as "M_bytes"
from (select value as ur from v$parameter where name = ‘undo_retention‘),
(select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups from v$undostat),
(select value as dbs from v$parameter where name = ‘db_block_size‘);
以下SQL語句則按峰值情況計算UNDO資料表空間所需空間：
col UNDO_RETENTION for a15
col DB_BLOCK_SIZE for a15
select ur undo_retention, dbs db_block_size, ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
from (select value as ur from v$parameter where name = ‘undo_retention‘),
(select (undoblks / ((end_time - begin_time) * 86400)) ups from v$undostat where undoblks in (select max(undoblks) from v$undostat)),
(select value as dbs from v$parameter where name = ‘db_block_size‘);
需要注意因RAC情況下一般存在2個UNDO資料表空間，視乎實際情況分別在2個執行個體中執行以上查詢。
一般來說為了儘可能維護日常業務的正常運行，我們建議按照峰值情況估算和分配UNDO資料表空間的大小，雖然這樣存在儲存空間上的浪費，但是可以避免UNDO資料表空間不足所帶來的問題。
同時我們也可以使用DBA_UNDO_EXTENTS視圖即時監控UNDO資料表空間的使用方式：
select sum(bytes)/1024/1024 MB, status, tablespace_name
from dba_undo_extents
group by status, tablespace_name order by 3, 2;
該查詢將返回以STATUS分組的各狀態復原資訊所使用的空間量，一般存在三種STATUS狀態：EXPIRED，UNEXPIRED，ACTIVE。ACTIVE表示目前仍活躍的事務相關復原資訊，UNEXPIRED表示雖然事務已經結束但復原資訊保留的時間仍未超過執行個體參數UNDO_RETENTION所設定的值，EXPIRED表示復原資訊保留時間已超過UNDO_RETENTION所設定的值。
在UNDO資料表空間未啟用guarantee選項的情況下（當前使用方式），新事務的復原空間分配遵循以下依據：
a) 尋找不存在ACTIVE區間的復原段，若沒有則建立一個新的復原段，若空間不允許產生新段，則返回錯誤。
b) 如果有一個復原段被選中，但是其中閒置空間並不足以儲存該事務的復原資訊，那麼它將嘗試建立區間，如果資料表空間上沒有空間，那麼將會進入下一步。
c) 如果建立新區間失敗，它將會搜尋其他復原段中的EXPIRED區間並重用。
d) 如果其他復原段中沒有EXPIRED區間可使用，那麼它會繼續搜尋其他復原段中UNEXPIRED區間並重用，注意事務不會重用本復原段中的UNEXPIRED區間，故UNEXPIRED的復原空間僅部分可以為Oracle重用；若仍得不到所需則返回錯誤。
當我們觀察到ACTIVE復原資訊所佔用空間很大時，說明系統目前啟動並執行事務繁忙。因目前未啟用UNDO資料表空間的guarantee選項，故EXPIRED的全部復原空間與UNEXPIRED的部分復原空間可以為Oracle複用，在即時監控時主要觀察ACTIVE狀態復原資訊使用的空間即可。
在系統相關業務不變的情況下，我們通過計算UNDO資料表空間的峰值使用方式即可最大程度完善UNDO資料表空間的配置；而當系統處於業務調整階段，如新的業務加入或業務時段調整情況下，則需要進一步即時監控UNDO資料表空間使用方式，以滿足動態調整需求。
以下是監控UNDO 的指令碼。
v$undostat
select INST_ID, to_char(BEGIN_TIME,‘YYYY/MM/DD HH24:MI:SS‘) "BEGIN TIME", END_TIME, UNDOBLKS, TXNCOUNT, UNXPBLKRELCNT, ACTIVEBLKS, UNEXPIREDBLKS, EXPIREDBLKS
from gv$undostat order by 2;
查看某個事務正在用哪個undo segment
select s.sid,s.serial#,s.sql_id,v.usn,segment_name,r.status, v.rssize/1024/1024 mb
From dba_rollback_segs r, v$rollstat v,v$transaction t,v$session s
Where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr
order by segment_name ;
col UNDO_RETENTION for a15
col DB_BLOCK_SIZE for a15
select ur undo_retention, dbs db_block_size, ((ur * (ups * dbs))+(dbs * 24))/1024/1024 as "M_bytes"
from (select value as ur from v$parameter where name = ‘undo_retention‘),
(select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups from v$undostat),
(select value as dbs from v$parameter where name = ‘db_block_size‘);
col UNDO_RETENTION for a15
col DB_BLOCK_SIZE for a15
select ur undo_retention, dbs db_block_size, ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
from (select value as ur from v$parameter where name = ‘undo_retention‘),
(select (undoblks / ((end_time - begin_time) * 86400)) ups from v$undostat where undoblks in (select max(undoblks) from v$undostat)),
(select value as dbs from v$parameter where name = ‘db_block_size‘);
UNDO 中各種extent 的情況
select sum(bytes)/1024/1024 MB, status, tablespace_name
from dba_undo_extents
group by status, tablespace_name order by 3, 2;
查看undo segemnts 大小 dba_segments
col SEGMENT_NAME for a30
col OWNER for a8
col TABLESPACE_NAME for a20
select tablespace_name, owner, segment_name, bytes/1024/1024 mb
from dba_segments where tablespace_name like ‘UNDOTBS%‘;
查看undo segemnts 大小 v$rollstat
select segment_name, v.rssize/1024/1024 mb
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
undo segemnt extent info
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
查看某個事務的UNDO 情況
select s.sid, s.serial#, t.XIDUSN, t.STATUS, t.USED_UBLK
from v$transaction t, v$session s
where t.addr = s.taddr;
查看某個事務的UNDO 詳細情況
set lines 199
col STATUS for a8
col USERNAME for a6
col name for a25
col substr(s.program,1,78) for a30
SELECT r.name ,
d.tablespace_name,
s.sid,
s.serial#,
s.username,
t.status,
t.cr_get,
t.phy_io,
t.used_ublk,
t.noundo,
substr(s.program,1,78)
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r,Dba_Rollback_Segs d
WHERE t.addr=s.taddr
and t.xidusn=r.usn
AND d.segment_name= r.name
ORDER BY t.cr_get,t.phy_io;
