Oracle UNDO 监控

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;
 

 

***************************************************************************************

1. FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU)
   Applies to:
   Oracle Server – Enterprise Edition – Version: 9.0.1.4 to 11.1.0.6
   Information in this document applies to any platform.
   Oracle RDBMS Server
   Purpose
   This article is intended for Database Administrators with  knowledge on Automatic Undo Management (AUM).
   And we have covered major questions related to Automatic Undo Management (AUM) which will be helpful during creation (or) monitoring the undo tablespaces.
   Questions and Answers
   What is Undo?
   Oracle maintains information to nullify changes made to the database. Such information consists of records of the actions of transactions, collectively known as undo. Oracle uses the undo to do the following: 

   -  Rollback an active transaction 

   -  Recover a terminated transaction 

   -  Provide read consistency 

   -  Recovery from logical corruptions

   What is AUM / SMU?
   Automatic Undo