Oracle回滚大事务引起undo表空间暴增【回滚delete大事务引起的异常】

由于业务调整需要，需要删除大量的历史冗余数据，执行delete语句后，发现执行效率很慢，并且对当前数据库的IO有较大的消耗影响，此时查看数据库的资源占用情况：
undo表空间中常用的操作：
（1）查看undo表空间undotbs1的属性：

SQL> select b.tablespace_name,AUTOEXTENSIBLE,RETENTION from dba_tablespaces a,dba_data_files b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and b.TABLESPACE_NAME='UNDOTBS1';

TABLESPACE_NAME AUT RETENTION
---------------------- --- ----------------
UNDOTBS1 NO NOGUARANTEE

（2）查看各个回退段的使用信息：

select a.name,b.extents,b.rssize,b.writes,b.xacts,b.wraps
from v$rollname a,v$rollstat b where a.usn=b.usn;

（3）确定哪些用户正在使用undo段：

SQL> select a.username,b.name,c.used_ublk from v$session a,v$rollname b,v$transaction c
         where a.saddr=c.ses_addr and b.usn=c.xidusn;
USERNAME NAME USED_UBLK
---------- ----------------------- ----------
NDMC _SYSSMU1_1255220753$ 1
NDMC _SYSSMU5_1255220754$ 1

（4）每秒生成的UNDO量，可以通过如下SQL得出:

SQL> SELECT (SUM(undoblks))/ SUM((end_time - begin_time) * 86400) FROM v$undostat;

(SUM(UNDOBLKS))/SUM((END_TIME-BEGIN_TIME)*86400)
--------------------------------------------------------------
          7.97590055

（5）当前undo表空间使用状态发现:

SQL> SELECT DISTINCT STATUS,SUM(BYTES),COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
STATUS SUM(BYTES) COUNT(*)
--------- ---------------- ----------
UNEXPIRED 3225157632 5667
EXPIRED 1063518208 1588
ACTIVE 1048576 1

（6）查看活动事务v$transaction

SQL> SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK
     FROM V$SESSION A, V$TRANSACTION B WHERE A.SADDR=B.SES_ADDR;
       SID USERNAME XIDUSN USED_UREC USED_UBLK
       ---------- ------------ ---------- ---------- ----------
       407 NDMC 15 3 1
       
SQL> SELECT XID AS "txn_id", XIDUSN AS "undo_seg", USED_UBLK "used_undo_blocks",
     XIDSLOT AS "slot", XIDSQN AS "seq", STATUS AS "txn_status"
     FROM V$TRANSACTION;
     txn_id undo_seg used_undo_blocks slot seq txn_status
     ---------------- ---------- ---------------- ---------- --------- -------------
     14001600733A0C00 20 1 22 801395 ACTIVE

–查看未过期事务的占用情况：

 select BEGIN_TIME,
       END_TIME,
       MAXQUERYLEN,
       MAXQUERYSQLID,
       TUNED_UNDORETENTION
  from DBA_HIST_UNDOSTAT Order By begin_time Desc;

–死事务监控
由于执行进度太慢，并且对线上数据库造成一定的性能压力，于是人为KILL掉delete着过亿数据的事务，导致事务回滚一直在漫长进行着，UNDO表空间存在大量的活跃事务，查看UNDO表空间，该事务占用了大量UNDO块，此时处境比较尴尬，因为UNDO的回滚段正在读取，无法快速释放，尽管重启数据库，回滚还是依然需要进行的，只能等它慢慢处理完，因为这个是数据库的内部回滚操作！

SQL> select a.username,b.name,c.used_ublk from v$session a,v$rollname b,v$transaction c where a.saddr=c.ses_addr and b.usn=c.xidusn;
 
USERNAME NAME USED_UBLK
------------------------------ ------------------------------ ----------
RPT _SYSSMU4_1605577464$ 1845022
SQL> SELECT DISTINCT STATUS,SUM(BYTES),COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
 
STATUS SUM(BYTES) COUNT(*)
--------- ---------- ----------
UNEXPIRED 83165184 21
EXPIRED 12713984 44
ACTIVE 3421785292 917

–查看该死事务在回滚段中的位置

SQL> SELECT XID AS "txn_id",
  2 XIDUSN AS "undo_seg",
  3 USED_UBLK "used_undo_blocks",
  4 XIDSLOT AS "slot",
  5 XIDSQN AS "seq",
  6 STATUS AS "txn_status"
  7 FROM V$TRANSACTION;
 
txn_id undo_seg used_undo_blocks slot seq txn_status
---------------- ---------- ---------------- ---------- ---------- ----------------
04000300FB660000 4 1838729 3 26363 ACTIVE

–通过查看内部表可看到回滚剩余估计时间（需用SYS执行）：

SQL> select ADDR, KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESIZ
  2 from x$ktuxe where KTUXEUSN = 4 and KTUXESLT = 3;
 
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
---------------- ---------- ---------- ---------- ----------
000000004E8419F0 4 3 26363 1836424 --剩余时间

–用下列过程可统计回滚剩余时间：

declare
  l_start number;
  l_end number;
begin
  select ktuxesiz
    into l_start
    from x$ktuxe
   where KTUXEUSN = 4
     and KTUXESLT = 3;
  dbms_lock.sleep(60); ---可以缩小这个时间，但是太小，可能会导致误差较大
  select ktuxesiz
    into l_end
    from x$ktuxe
   where KTUXEUSN = 4
     and KTUXESLT = 3;
  dbms_output.put_line('time est Day:' ||
                       round(l_end / (l_start - l_end) / 60 / 24, 2));
end;
 /
time est Day:.62
 
PL/SQL procedure successfully completed

晕! 62天，还要活吗？
所以回滚的代价是非常昂贵的，切勿以身试法！经过本地教训后，最稳妥的做法是分批delete操作，这样虽然麻烦一点，但不会造成UNDO严重损耗，并且不会造成巨大的回滚代价！