首页 > 代码库 > Oracle UNDO 监控
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;
当然这一特性是由隐含参数_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;
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。