delete 大表后kill session是否会recover?

简单介绍下情景: DELETE 一张大表数据,未执行完,就把session 杀掉了。
kill session 后。smon占CPU 仍然超过60%,难道是在回滚数据?

我的测试过程:

session 1:
SQL> select count(*) from amy;

COUNT(*)
———-
1047140

SQL> select * from v$mystat where rownum<2;

SID STATISTIC# VALUE
———- ———- ———-
46 0 0
session 2:
SQL> select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME
———- ———- ——————————
37 127 SYS
46 15 RHYS
48 11 SYS
50 23 SYS
然后再rhys下删除amy表:
SQL> delete from amy;
delete from amy
*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-00028: your session has been killed

SQL>
在session 2 然后kill 该session:
SQL> alter system kill session ‘46,15’;

System altered.

SQL> select usn,xacts,latch,extents,gets,waits,hwmsize,status,curext,curblk from v$rollstat where xacts>0;

USN XACTS LATCH EXTENTS GETS WAITS HWMSIZE STATUS CUREXT CURBLK
———- ———- ———- ———- ———- ———- ———- ————— ———- ———-
3 1 0 106 82251 10 402776064 ONLINE 102 393

SQL>
select usn,xid,slt,state,undoblocksdone,undoblockstotal,cputime,parentslt,rcvserSQL> vers from v$fast_start_transactions;

USN XID SLT STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME PARENTSLT RCVSERVERS
———- —————- ———- —————- ————– ————— ———- ———- ———-
3 0300000068050000 0 RECOVERING 4479 14624 16 0 1
3 03001F0065050000 31 RECOVERED 3953 3953 58 1

SQL> r
1* select usn,xid,slt,state,undoblocksdone,undoblockstotal,cputime,parentslt,rcvservers from v$fast_start_transactions

USN XID SLT STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME PARENTSLT RCVSERVERS
———- —————- ———- —————- ————– ————— ———- ———- ———-
3 0300000068050000 0 RECOVERING 4479 14624 17 0 1
3 03001F0065050000 31 RECOVERED 3953 3953 58 1
结论:没错,是需要回滚,有时候大的事务回滚导致很严重的性能问题,有时候为了加快速度,设置fast_start_parallel_rollback 为high

标签