Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 请教一个关于undo回滚的问题

ligle

22 积分	0 好友	0 主题

发消息

请教一个关于undo回滚的问题

1^#

发表于 2012-4-3 10:39:13 | 查看: 9020| 回复: 10

Hello,Maclean

突然想起之前遇到的一个疑问，想和你交流下....望指教~

在10G和9i中均遇到过
database instance中执行了几个update/delete语句，并且操作的数据量也相当的大，不知为何原因，执行人员ctrl+c或者kill 掉了执行update/delete语句的会话进程，此时，该事务进入到rollback阶段，通过查询dba_undo_extents，如：
SQL> select owner,segment_name,status,sum(bytes/1024/1024) MB from dba_undo_extents where status='ACTIVE' group by owner,segment_name,status order by MB desc;

OWN SEGMENT_NAME                STATUS          MB
--- ------------------------------ --------- ----------
SYS _SYSSMU200$                   ACTIVE 30042.1875
SYS _SYSSMU85$                   ACTIVE    8679.4375
SYS _SYSSMU42$                   ACTIVE    8375.3125
SYS _SYSSMU252$                   ACTIVE    8278.9375
SYS _SYSSMU281$                   ACTIVE    7941.0625

这些会话依然在占用着大量undo,并且几个小时后之后依然维持这个状态，没什么太大变化。这个是时候，应该或者可以做些什么呢？谢谢！

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-4-3 12:20:39

ODM DATA:

监控一个大事务的回滚

我们在大的事务失败时往往面临长时间的回滚，在回滚期间表会被加以TM-3 SX sub-exclusive锁，此时一般我们是无法针对表实施DDL操作的。长时间的大事务回滚可能耗尽我们的耐心，不过我们还是有办法预估何时回滚能够完成的，参考中的脚本<Script:when transaction will finish rollback>中的脚本，注意该脚本需要访问x$ktuxe内部视图，所以需要以sysdba身份方能执行。

SQL> select * from v$lock where type in ('TM','TX');

ADDR    KADDR          SID TY       ID1       ID2    LMODE REQUEST    CTIME    BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0ED0F30C 0ED0F33C       9 TM    13865       0       3       0    3757       0
2C3975FC 2C39763C       9 TX    65557       677       6       0    3757       0

SQL> select object_name,object_type from dba_objects where object_id=13865;

OBJECT_NAM OBJECT_TYPE
---------- -------------------
SAMPLE    TABLE

因为表上存在TM-3锁，所以此时是无法对表执行需要持有TM-6 exclusive排它锁的DDL操作的

SQL> drop table sample;
drop table sample
         *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> select * from sample where rownum=1 for update nowait;
select * from sample where rownum=1 for update nowait
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

rollback完成之前相关行上的row level lock不会被释放，因此dml操作会被block

SQL> select xidusn,xidslot,xidsqn,status from v$transaction;

XIDUSN XIDSLOT    XIDSQN STATUS
---------- ---------- ---------- ----------------
      1       21       677 ACTIVE

从v$tranasction视图中可以看到事务1.21.677处于active状态

使用set time on命令显示当前时间

SQL> set time on;

20:54:26 SQL>

set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt

declare
  cursor tx is
select
   s.username,
   t.xidusn,
   t.xidslot,
   t.xidsqn,
   x.ktuxesiz
from
   sys.x$ktuxe  x,
   sys.v_$transaction  t,
   sys.v_$session  s
where
   x.inst_id = userenv('Instance') and
   x.ktuxesta = 'ACTIVE' and
   x.ktuxesiz > 1 and
   t.xidusn = x.ktuxeusn and
   t.xidslot = x.ktuxeslt and
   t.xidsqn = x.ktuxesqn and
   s.saddr = t.ses_addr;
  user_name  varchar2(30);
  xid_usn number;
  xid_slot number;
  xid_sqn number;
  used_ublk1 number;
  used_ublk2 number;
begin
  open tx;
  loop
fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
exit when tx%notfound;
if tx%rowcount = 1
then
   sys.dbms_lock.sleep(10);
end if;
select
   sum(ktuxesiz)
into
   used_ublk2
from
   sys.x$ktuxe
where
   inst_id = userenv('Instance') and
   ktuxeusn = xid_usn and
   ktuxeslt = xid_slot and
   ktuxesqn = xid_sqn and
   ktuxesta = 'ACTIVE';
if used_ublk2 < used_ublk1
then
   sys.dbms_output.put_line(
      user_name ||
      '''s transaction ' ||
      xid_usn  || '.' ||
      xid_slot || '.' ||
      xid_sqn  ||
      ' will finish rolling back at approximately ' ||
      to_char(
      sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
      'HH24:MI:SS DD-MON-YYYY'
      )
   );
end if;
  end loop;
  if user_name is null
  then
sys.dbms_output.put_line('No transactions appear to be rolling back.');
  end if;
end;
/

MACLEAN's transaction 1.21.677 will finish rolling back at approximately 21:46:45 25-MAY-2011

以上脚本给出了估计的1.21.677事务的回滚结束时间为21:46:45，即还有50分钟，
注意这只是根据之前单位时间内rollback的进度所得到的一个估算值，所以并不精确，但还是可以做为一个参考

再次运行以上脚本，会发现2次的结果不一定一致

SQL> /
MACLEAN's transaction 1.21.677 will finish rolling back at approximately 21:35:47 25-MAY-2011

在产品数据库中应当尽可能避免出现大事务回滚的现象，因为除去lock的影响外，large transaction rollback还会导致CPU使用率大幅上升并产生往往比事务本身所产生的更多的redo重做日志记录。

http://www.oracledatabase12g.com ... 9B%9E%E6%BB%9A.html

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2012-4-3 12:25:04

在 rollback transaction 完成之前这些相关的rollback segment 将一直处于action 状态。

耐心等待rollback 完成或者考虑使用并行 rollback fast_start_parallel_rollback

If all the processes are in state RECOVERING, then you can benefit from adding more processes:

SQL> alter system set fast_start_parallel_rollback = high;

This will create parallel servers as much as 4 times the number of CPUs.

实际在处理这类大事务时需要告诫应用人员不应轻易回滚大事务。

回复只看该作者道具举报

xifenfei

4^#

发表于 2012-4-3 15:01:10

如果在回滚过程中，有大量的index，设置fast_start_parallel_rollback = high可能效果更差

回复只看该作者道具举报

ligle

5^#

发表于 2012-4-3 17:38:16

回复 3# 的帖子

谢谢，Maclean
确实不应该轻易回退大事务，否则时间之长令人难以忍受。

我遇到的情况跟你的测试有点点不一样，他们是直接kill spid，所以就导致了死事务（dead transaction）。这个死事务回滚的也是相当的慢。只能等...

回复只看该作者道具举报

Maclean Liu(刘相兵

6^#

发表于 2012-4-3 18:29:01

回复 4# 的帖子

fast_start_parallel_rollback参数决定了SMON在回滚事务时使用的并行度，若将该参数设置为false那么并行回滚将被禁用，若设置为Low(默认值)那么会以2*CPU_COUNT数目的并行度回滚，当设置为High则4*CPU_COUNT数目的回滚进程将参与进来。当我们通过以下查询发现系统中存在大的dead tranacation需要回滚时我们可以通过设置fast_start_parallel_rollback为HIGH来加速恢复:

select sum(distinct(ktuxesiz)) from x$ktuxe where ktuxecfl = 'DEAD';

==============parallel transaction recovery===============

*** 2011-06-24 20:31:01.765
SMON: system monitor process posted msgflag:0x0000 (-/-/-/-/-/-/-)

*** 2011-06-24 20:31:01.765
SMON: process sort segment requests begin

*** 2011-06-24 20:31:01.765
SMON: process sort segment requests end

*** 2011-06-24 20:31:01.765
SMON: parallel transaction recovery begin
WAIT #0: nam='DFS lock handle' ela= 504 type|mode=1413545989 id1=3 id2=11 obj#=2 tim=1308918661765715
WAIT #0: nam='DFS lock handle' ela= 346 type|mode=1413545989 id1=3 id2=12 obj#=2 tim=1308918661766135
WAIT #0: nam='DFS lock handle' ela= 565 type|mode=1413545989 id1=3 id2=13 obj#=2 tim=1308918661766758
WAIT #0: nam='DFS lock handle' ela= 409 type|mode=1413545989 id1=3 id2=14 obj#=2 tim=1308918661767221
WAIT #0: nam='DFS lock handle' ela= 332 type|mode=1413545989 id1=3 id2=15 obj#=2 tim=1308918661767746
WAIT #0: nam='DFS lock handle' ela= 316 type|mode=1413545989 id1=3 id2=16 obj#=2 tim=1308918661768146
WAIT #0: nam='DFS lock handle' ela= 349 type|mode=1413545989 id1=3 id2=17 obj#=2 tim=1308918661768549
WAIT #0: nam='DFS lock handle' ela= 258 type|mode=1413545989 id1=3 id2=18 obj#=2 tim=1308918661768858
WAIT #0: nam='DFS lock handle' ela= 310 type|mode=1413545989 id1=3 id2=19 obj#=2 tim=1308918661769224
WAIT #0: nam='DFS lock handle' ela= 281 type|mode=1413545989 id1=3 id2=20 obj#=2 tim=1308918661769555

*** 2011-06-24 20:31:01.769
SMON: parallel transaction recovery end

但是在real world的实践中可以发现当fast_start_parallel_rollback= Low/High，即启用并行回滚时常有并行进程因为各种资源互相阻塞导致回滚工作停滞的例子，当遭遇到这种问题时将fast_start_parallel_rollback设置为FALSE一般可以保证恢复工作以串行形式在较长时间内完成。

http://www.oracledatabase12g.com ... ad-transaction.html

回复只看该作者道具举报