long长事务回滚的模拟与定位

大型事务的回滚 大型事务的回滚产生非常大的代价，不仅锁定需要的资源，并且消耗的CPU和IO，尤其是IO将极为密集。尤其在KILL大型事务之前检查事务究竟有多大可能是必要的，同时我们也需要知道回滚已经进行了多少程度。
一、模拟：

1.删除500多万的数据。


SQL> conn hr/hr;
Connected.
SQL>  create table  test as select * from dba_objects;

Table created.

SQL> insert into test select * from test;

87055 rows created.

SQL> insert into test select * from test;

174110 rows created.

SQL> insert into test select * from test;

348220 rows created.

SQL> insert into test select * from test;

696440 rows created.

SQL> insert into test select * from test;

1392880 rows created.

SQL> select count(*) from test;

  COUNT(*)
----------
   2785760

SQL> insert into test select * from test;

2785760 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
   5571520

SQL> analyze table test compute statistics;

Table analyzed.

SQL>  
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        30

SQL>

--模拟删除，不commit
SQL> delete test;
 
5571520 rows deleted.



--另开窗口，查询kill掉会话

SQL>  select sid,serial#,sql_id,event,blocking_session from v$session where sid=30;

       SID    SERIAL# SQL_ID        EVENT                                                            BLOCKING_SESSION
---------- ---------- ------------- ---------------------------------------------------------------- ----------------
        30        165 7qqwcq9td6akt log buffer space                                                               11

SQL> select sql_text from v$sql where sql_id='7qqwcq9td6akt';

SQL_TEXT
----------------------------------------------------------------------
 delete test

SQL> alter system kill session '30,165' immediate;

System altered.

--回到原来窗口验证：
SQL>  select count(*) from test;
 select count(*) from test
        *
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 28346
Session ID: 30 Serial number: 165


二、定位：
查看回滚进度：
可以通过以下两个视图查看回滚的进度，通过单位时间内恢复的undo block来估算恢复时间：
1.    通过x$ktuxe
alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';
select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ,sysdate from x$ktuxe where KTUXECFL='DEAD' and KTUXESIZ >0;


SQL> alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';

Session altered.

SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ,sysdate from x$ktuxe where KTUXECFL='DEAD' and KTUXESIZ >0;

ADDR               KTUXEUSN   KTUXESLT   KTUXESQN   KTUXESIZ SYSDATE
---------------- ---------- ---------- ---------- ---------- -------------------
00007F170E8AAC20          5         11       7784      84438 2017-12-09 14:19:22

SQL>

可以通过KTUXESLT ,KTUXESQN这两个字段，然后用以下脚本回滚得出大概需要的时间：
set serveroutput on
declare
  l_start number;
  l_end   number;
begin
  select ktuxesiz
    into l_start
    from x$ktuxe
   where KTUXEUSN = 5
     and KTUXESLT = 11;
  dbms_lock.sleep(60);
  select ktuxesiz
    into l_end
    from x$ktuxe
   where KTUXEUSN = 5
     and KTUXESLT = 11;
  dbms_output.put_line('time est Day:' ||
                       round(l_end / (l_start - l_end) / 60 / 24, 2));
end;
/

time est Day:.01

PL/SQL procedure successfully completed.

SQL> SQL>  

2.    通过v$fast_start_trancsations 状态为recovering表示恢复中;
select USN,SLT,SEQ,STATE,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL,CPUTIME,XID,sysdate from V$FAST_START_TRANSACTIONS;

SQL> select USN,SLT,SEQ,STATE,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL,CPUTIME,XID,sysdate from V$FAST_START_TRANSACTIONS;

       USN        SLT        SEQ STATE            UNDOBLOCKSDONE UNDOBLOCKSTOTAL    CPUTIME XID              SYSDATE
---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------------- -------------------
         5         11       7784 RECOVERING               123491          179829        120 05000B00681E0000 2017-12-09 14:20:25
         
通过如下视图观察回滚是串行还是并行回滚的，如下图应是并行恢复的，
V$FAST_START_SERVERS provides information about all the recovery slaves performing parallel transaction recovery.

通过xid字段与v$fast_start_trancsations关联。
select * from v$fast_start_servers  where xid in (select XID from V$FAST_START_TRANSACTIONS);

SQL> select * from v$fast_start_servers  where xid in (select XID from V$FAST_START_TRANSACTIONS);

STATE       UNDOBLOCKSDONE        PID XID
----------- -------------- ---------- ----------------
RECOVERING          133950         20 05000B00681E0000

3.查看回滚对象
通过dump undo block方式查看回滚的对象：

首先通过v$fast_start_trancsations的usn字段查询到使用的回滚段。

SQL> select * from v$rollname where usn=5;

       USN NAME
---------- ------------------------------
         5 _SYSSMU5_898567397$
         
dump这个undo block，因为dump的文件很大，在查询出对象的object_id后，kill掉这个dump会话。
alter system dump undo block "" XID ;

SQL> alter system dump undo block "_SYSSMU5_898567397$" xid 5 11 7784;
 
。。。。。。。。。


[oracle@wang trace]$ ls -lrt
total 635992
-rw-r----- 1 oracle oinstall        81 Apr 27  2017 DBdb_ora_9045.trm
-rw-r----- 1 oracle oinstall        59 Apr 27  2017 DBdb_mman_9065.trm
-rw-r----- 1 oracle oinstall        60 Apr 27  2017 DBdb_ora_9084.trm
-rw-r----- 1 oracle oinstall       111 Apr 27  2017 DBdb_ora_9099.trm
.......................................
-rw-r----- 1 oracle oinstall     12306 Dec  9 14:23 DBdb_ora_28113.trm
-rw-r----- 1 oracle oinstall 536798705 Dec  9 14:23 DBdb_ora_28113.trc
[oracle@wang trace]$
[oracle@wang trace]$
[oracle@wang trace]$
[oracle@wang trace]$ grep objn DBdb_ora_28113.trc | head -5
* Rec #0x11  slt: 0x0b  objn: 90373(0x00016105)  objd: 90373  tblspc: 4(0x00000004)
* Rec #0x10  slt: 0x0b  objn: 90373(0x00016105)  objd: 90373  tblspc: 4(0x00000004)
* Rec #0xf  slt: 0x0b  objn: 90373(0x00016105)  objd: 90373  tblspc: 4(0x00000004)
* Rec #0xe  slt: 0x0b  objn: 90373(0x00016105)  objd: 90373  tblspc: 4(0x00000004)
* Rec #0xd  slt: 0x0b  objn: 90373(0x00016105)  objd: 90373  tblspc: 4(0x00000004)
[oracle@wang trace]$
[oracle@wang trace]$

可以查询到objn为121192,对应的就是dba_objects的object_id，即mosongtao.rollback_test，正是前边测试的对象。再查询v$session_longops配合username,last_update_time,target，可以大概定位到执行sql_id。

注意：在查询到object_id后手工停掉dump undo block 动作

SQL> alter system dump undo block "_SYSSMU5_898567397$" xid 5 11 7784;
 
^C^C^C^C^C^C
alter system dump undo block "_SYSSMU5_898567397$" xid 5 11 7784
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> SQL> SQL> SQL>               


4.查询sql
SQL> select SID,TARGET,SQL_ID,START_TIME,LAST_UPDATE_TIME from v$session_longops where target like '%TEST%' order by LAST_UPDATE_TIME desc;

       SID TARGET                                                           SQL_ID        START_TIME   LAST_UPDATE_
---------- ---------------------------------------------------------------- ------------- ------------ ------------
        30 HR.TEST                                                          7qqwcq9td6akt 09-DEC-17    09-DEC-17

SQL> select sql_text from v$sql where sql_id='7qqwcq9td6akt';

SQL_TEXT
-------------------------------------------------------------------------
 delete test

SQL>