最近处理了几次undo相关问题，将undo暴增后查询思路整理如下：

• 查询active状态的使用空间

• 确认使用的详细情况，比如占用高的sid与sql，以及是否存在死事务

• 应急处理方法

1. 查询undo active使用状态

UNEXPIRED已经释放，在保留期内。无需手动处理，在undo表空间紧张时，undo会自动调整undo保留期的，参考如下：

最后一列就是undo的保留期，可以看到会根据使用情况自动调整。

2. 确认占用的sid与使用大小

select s.sid, substr( s.program, 1, 15 ) program,

s.machine,

t.xidusn || '.' || t.xidslot || '.' || t.xidsqn tx_addr,

t.status, t.start_time, tbs.tablespace_name tbs_name,

round( t.used_ublk * tbs.block_size /1048576, 2 ) undo_size_mb,

t.used_urec

from v$transaction t, v$session s, v$parameter p, dba_tablespaces tbs

where t.ses_addr = s.saddr

and p.name = 'undo_tablespace'

and p.value = tbs.tablespace_name

order by t.used_ublk desc;

?

?

SID PROGRAM MACHINE TX_ADDR STATUS START_TIME TBS_NAME UNDO_SIZE_MB USED_UREC

?

?

----- --------------- ---------------- ------------- ---------- ------------------ ------------ ------------ ----------

?

?

1 sqlplus@localho localhost.localdomain 7.31.582 ACTIVE 02/03/20 11:09:45 UNDOTBS1 15.84 199906

Active总共17M，SQLPLUS就占用了15.85M。

3. 确认占用sql

SELECT S.SID,

S.USERNAME,

U.NAME,

Q.SQL_TEXT,

Q.HASH_VALUE,

T.UBABLK

FROM V$TRANSACTION T,

V$ROLLSTAT R,

V$ROLLNAME U,

V$SESSION S,

V$SQL Q

WHERE S.TADDR = T.ADDR

AND T.XIDUSN = R.USN

AND R.USN = U.USN

AND Q.HASH_VALUE =

DECODE(S.SQL_HASH_VALUE,

NULL, S.PREV_HASH_VALUE,

S.SQL_HASH_VALUE)

ORDER BY S.USERNAME;

?

SID USERNAME NAME SQL_TEXT HASH_VALUE UBABLK

----- --------------- ------------------------------ ---------------------------------------- ---------- ----------

1 SYS _SYSSMU7_831131319$ update t1 set id=9 71029355 5213

本次模拟对一张10w数据的表进行update。通过以上信息可以看到，sys用户通过本地sqlplus登陆，执行updata语句。

但有时候会发现，明明active的空间已经很大，但是v$transaction却查不到，需要关注死事务的产生。

4. 死事务的查询

http://blog.itpub.net/22034023/viewspace-710505/

死事务出现在异常关闭数据库或者事务进程不正常结束，比如KILL -9，shutdown abort的情况下。即使是DBA也只能等待smon清理完成，没有好的办法。当然禁用smon这种方法，如：

?

这种在生产中绝对是谨慎谨慎，除非有着非常深入的理解和实践。

当前数据库里的死事务可以通过查询内部表x$ktuxe来获得。

select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL='DEAD';

?

?

ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ

---------------- ---------- ---------- ---------- ----------

00002B92FF5D5F68 15 12 314961 43611

KTUXESIZ代表需要回滚的回滚块数。

死事务的回滚进程数可以通过参数fast_start_parallel_rollback来设置。

可以根据以下算法来粗略地估算回滚需要的时间，这里是小时：

?

5. 应急处理

如果占用undo高的是执行不合适的sql导致，那么可以临时kill掉。事后也可以让开发针对sql的提交方式进行优化。

如果是死事务，只能通过上述方法进行计算时间。无法手工干预。

如果应急期间定位不到具体问题，那最紧要的就是扩容undo表空间，确保生产事务正常运行。

作者

王茂材，云和恩墨技术顾问，从事Oracle DBA工作5年，维护过200+ 套Oracle数据库，涉及能源、医疗、体彩、银行、运营商等行业数据库的维护和操作。对Oracle数据库具备扎实的理论基础与丰富的实践经验，擅长故障处理、迁移、备份恢复、SQL优化等。

墨天轮原文链接：https://www.modb.pro/db/45680