Oracle数据库常用undo查询思路 / 开普饭

墨墨导读：最近处理了几次undo相关问题，将undo暴增后查询思路整理分享至此。

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

查询active状态的使用空间
确认使用的详细情况，比如占用高的sid与sql，以及是否存在死事务
应急处理方法

1. 查询undo active使用状态

select tablespace_name, status, round( sum( bytes ) / 1048576, 2 ) mb,count(*) extent_countfrom dba_undo_extentsgroup by tablespace_name, statusorder by tablespace_name, status;

TABLESPACE_NAME STATUS MB EXTENT_COUNT

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

UNDOTBS1 ACTIVE 17.13 19 --主要关注活动部分

UNDOTBS1 EXPIRED 15.25 64

UNDOTBS1 UNEXPIRED 8.19 11

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

select to_char(begin_time, 'hh24:mi:ss') BEGIN_TIME,to_char(end_time, 'hh24:mi:ss') END_TIME,maxquerylen,nospaceerrcnt,tuned_undoretentionfrom v$undostat;

BEGIN_TI END_TIME MAXQUERYLEN NOSPACEERRCNT TUNED_UNDORETENTION

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

17:37:31 17:43:00 1281 0 2062

17:27:31 17:37:31 978 0 1759

17:17:31 17:27:31 372 0 1153

17:07:31 17:17:31 974 0 1755

16:57:31 17:07:31 368 0 1151

16:47:31 16:57:31 968 0 1809

16:37:31 16:47:31 363 0 1205

16:27:31 16:37:31 961 0 1805

16:17:31 16:27:31 358 0 1200

16:07:31 16:17:31 957 0 1799

15:57:31 16:07:31 353 0 1195

15:47:31 15:57:31 953 0 1794

15:37:31 15:47:31 349 0 1190

15:27:31 15:37:31 948 0 1790

15:17:31 15:27:31 342 0 1185

最后一列就是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_urecfrom v$transaction t, v$session s, v$parameter p, dba_tablespaces tbswhere t.ses_addr = s.saddr and p.name = 'undo_tablespace' and p.value = tbs.tablespace_nameorder 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这种方法，如：

oradebug setorapid 'SMON's Oracle PID';oradebug event 10513 trace name context forever, level 2

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

当前数据库里的死事务可以通过查询内部表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来设置。

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

declare l_start number; l_end number;begin select ktuxesiz into l_start from x$ktuxe where KTUXEUSN = 10 and KTUXESLT = 39; ---------这里根据实际数字来填写 dbms_lock.sleep(60); ---------可以缩小这个时间，但是太小，可能会导致误差较大 select ktuxesiz into l_end from x$ktuxe where KTUXEUSN = 10 and KTUXESLT = 39; ---------这里根据实际数字来填写 dbms_output.put_line('time cost Day:' || round(l_end / (l_start - l_end) / 60, 2));end;/

5. 应急处理

如果占用undo高的是执行不合适的sql导致，那么可以临时kill掉。事后也可以让开发针对sql的提交方式进行优化。
如果是死事务，只能通过上述方法进行计算时间。无法手工干预。
如果应急期间定位不到具体问题，那最紧要的就是扩容undo表空间，确保生产事务正常运行。

作者

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

墨天轮原文链接：https://www.modb.pro/db/45680（复制到浏览器打开或者点击“阅读原文”立即查看）

Oracle数据库常用undo查询思路

1. 查询undo active使用状态

3. 确认占用sql

4. 死事务的查询

5. 应急处理

相关推荐