UNDO相关问题总结（一）

来源：互联网发布：centos 创建文件夹编辑：程序博客网时间：2024/05/18 18:46

第一个主题介绍undo表空间不足的问题

undo表空间不足的问题，基本可划分两类

active类型undo段使用过高，导致undo表空间不足
unexpired类型undo使用过高，导致DML运行缓慢（这种情况下不会导致undo空间无法扩展）

针对这两个情况的处理方法如下（仅供参考）：

1.通过下面语句查询当前实例undo空间的使用情况(active、unexpired类型段的占比）

select b.tablespace_name,

nvl(used_undo,0) "USED_UNDO(M)",

total_undo "Total_undo(M)",

trunc(nvl(used_undo,0) / total_undo * 100, 2) || '%' used_PCT

from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo, tablespace_name

from dba_undo_extents

where status in ( 'ACTIVE','UNEXPIRED')

group by tablespace_name) a,

(select tablespace_name, sum(bytes / 1024 / 1024) total_undo

from dba_data_files

where tablespace_name in

(select value

from v$spparameter

where name = 'undo_tablespace'

and (sid = (select instance_name from v$instance) or

sid = '*'))

group by tablespace_name) b

where a.tablespace_name (+)= b.tablespace_name

2.进一步查看active、unexpired段的具体使用情况

select tablespace_name,status,sum(bytes/1024/1024/1024) GB from dba_undo_extents group by tablespace_name,status;

3.如果是active段使用过高导致undo段无法扩展，则可以根据以下方法解决：

a.临时添加数据文件到undo表空间，暂缓问题

b.问题暂时缓解以后，需寻找问题的根本原因，分析当前session中undo段的使用情况，通过以下语句查询：

--查看系统中会话使用的回滚段

SELECT r.name rbs,

nvl(s.username, 'None') oracle_user,

s.osuser client_user,

p.username unix_user,

s.sid,

s.serial#,

p.spid unix_pid,s.MACHINE,s.PROGRAM,s.MODULE,

t.used_ublk * TO_NUMBER(x.value) / 1024 / 1024 as undo_mb ,

TO_CHAR(s.logon_time, 'mm/dd/yy hh24:mi:ss') as login_time,

TO_CHAR(sysdate - (s.last_call_et) / 86400, 'mm/dd/yy hh24:mi:ss') as last_txn,

t.START_TIME transaction_starttime

FROM v$process p,

v$rollname r,

v$session s,

v$transaction t,

v$parameter x 　　

WHERE s.taddr = t.addr 　　

AND s.paddr = p.addr 　　

AND r.usn = t.xidusn(+) 　　

AND x.name = 'db_block_size' 　　

ORDER by undo_mb desc

c.如果发现某个session大量使用undo，分析session连接信息和执行的语句（用户、应用主机，模块等等），最后调整应用

如果是oltp，一般情况下session不应该大量使用undo段（不超过100M），如果有，需要调整应用和优化SQL；
如果是olap，可能用于分析等，某个session会占用大量的undo，这种情况下，需要通过优化部分SQL减少undo的使用；

备注：session执行当前执行的可能不会占用undo段，这种情况下，需要查询session当前事务中历史执行的所有SQL，可以关联查询v$open_cursor,v$active_session_history等视图。

4.如果是unexpired类型的段使用过高，这种情况下，不会出现undo表空间无法扩展，oracle会将unexpired类型的段强制使用来确保DML操作得以继续执行。但是这种情况下oracle需要搜索最早的expired段，同时需要将expired段进行一些特殊处理，会导致整个操作过程速度下降，在大量并发DML操作下，由于裙带效应，会导致整个数据库运行缓慢，活动session数上升。

分析方法：

检查v$undostat中的unxpblkreucnt: Number of unexpired undo blocks reused by transactions，这个数值大于0，说明事务由于需要undo空间而从unexpired undo segment中重用空间的次数

处理方法：

a.临时添加数据文件到undo表空间，暂缓问题

b.问题暂时缓解以后，需寻找问题的根本原因，即什么原因导致unexpired类型段使用过高。可能导致这类问题的原因如下：

undo表空间大小设置不合理
undo_retention参数设置的过期时间过长
undo_retention设置合理，但是由于oracle自动调整的原因，导致expired类型段过高(通过查询v$undostat中的tuned_undoretention)。这种情况下考虑禁用自动调整（_undo_autotune参数）
其它oracle bug导致

注意：关于通过切换实例使用的undo表空间方法来解决这类问题，不在此文讨论范围。

至此，两类导致undo表空间不足的问题介绍完毕；下一个主题介绍单个session占用过高的undo（但是未导致undo空间不足），导致数据库性能急剧下降的问题。

0 0