Oracle UNDO 監控，oracleundo監控

Oracle UNDO 監控，oracleundo監控
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;
 
 
 
 
 
 
 
Oracle的Undo機制是什

1. UNDO資料表空間用於存放UNDO資料。當執行DML操作時，Oracle會將這些操作的舊資料寫入UNDO段。管理UNDO資料不僅可以使用復原段，還可以使用UNDO資料表空間。
　　2. UNDO資料的作用：當使用者執行DML操作修改資料時，UNDO資料被存放在UNDO段，而新資料則被存放到資料區段中，如果事務操作存在問題，就需要回退事務，以取消事物變化。
　　例如：執行完UPDATE emp SET sal=1000 WHERE empno=7788後，發現應該修改僱員7963的工資，而不是7788.此時應該執行ROLLBACK語句。
　　3.讀一致性
　　使用者檢索資料時，ORACLE總是使使用者只能看到被提交過的資料，這是由Oracle自動提供的。當使用者修改資料，但是沒有提交時，另外一個使用者使用select語句尋找該值時，該值就是從undo資料表空間中取得的。
　　4.事務恢複
　　事務恢複是常式恢複的一部分，它是由Oracle Server自動完成的。如果在資料庫運行過程中出線曆程失敗，那麼當啟動Oracle Server時，後台進程SMON會自動執行常式恢複。執行常式恢複時，Oracle會重做所有未應用的記錄。然後開啟資料庫，回退未提交事務。
　　5.倒敘查詢
　　倒敘查詢用於取得某一特定時間點的資料庫資料。
　　6.UNDO_MANAGEMENT
　　使用初始化參數用於指定UNDO資料的管理方式。如果使用自動管理員模式，必須設定該參數為AUTO，此時採用UNDO資料表空間管理UNDO資料;如果使用手工管理員模式，必須設定該值為MANUAl，此時採用復原段管理UNDO資料。
　　7.UNDO_TABLESPACE
　　用於指定常式所要使用的UNDO資料表空間。使用自動UNDO管理員模式時，通過配置該參數可以指定常式所要使用的UNDO資料表空間。
　　使用RAC結構時，必須為每個常式配置一個獨立的UNDO資料表空間。
　　8.UNDO_RETENTION
　　該參數用於控制UNDO資料的最大保留時間，其預設值為900秒，該值時倒敘查詢可以查看到的最早時間點。
　　9.UNDO資料表空間上不能建立任何資料對象。
 
oracle undo資料表空間該怎估算，設計多大合適？

UndoSpace = UR * (UPS * DBS)
UR:undo_retention值
UPS:每秒產生的undo資料區塊數
DBS:db_block_size

undo資料表空間大小:
SELECT (UR * (UPS * DBS)) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));

如有協助請採納