Oracle数据库常用undo查询思路

数据和云

发布于 2021-03-30 16:01:41

7220

发布于 2021-03-30 16:01:41

文章被收录于专栏：数据和云数据和云

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

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

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

1. 查询undo active使用状态

select tablespace_name, status, round( sum( bytes ) / 1048576, 2 ) mb,
count(*) extent_count
from dba_undo_extents
group by tablespace_name, status
order 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_undoretention
from 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_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这种方法，如：

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;
/