This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Aug 23, 2012 12:42 AM by Fixed RSS

大事务回滚特别慢

Fixed Newbie
Currently Being Moderated
os aix 6.1 database 11.2.0.2 rac

通过dblink往该数据库导数,期间报undo表空间不够,所以停止导数,现在事务进行回滚,查询v$fast_start_transactions信息如下:
select * from v$fast_start_transactions;

USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ----------
22 25 24271 RECOVERING 90 2009235 58574 0016001900005ECF 0
23 17 29525 RECOVERING 79 23438 58572 0017001100007355 0
34 21 8115 RECOVERING 36 902583 58097 0022001500001FB3 0
2 1 52425 RECOVERING 90 1629318 58604 000200010000CCC9 0
35 14 5897 RECOVERING 26 823516 58097 0023000E00001709 0
24 32 8646 RECOVERED 20241 20241 147 00180020000021C6 16
3 27 65401 RECOVERING 91 441558 58667 0003001B0000FF79 0
26 23 31741 RECOVERING 25 1021400 58106 001A001700007BFD 0
28 8 18958 RECOVERING 86 1047511 58659 001C000800004A0E 0
29 0 15156 RECOVERING 25 190457 58097 001D000000003B34 0
7 25 66759 RECOVERING 27 1024446 58112 00070019000104C7 0
30 11 30097 RECOVERING 28 1771394 58104 001E000B00007591 0
9 27 100924 RECOVERING 28 1521821 58111 0009001B00018A3C 0
21 12 71740 RECOVERING 114 5671675 58659 0015000C0001183C 0

14 rows selected.
查询v$fast_start_servers没有信息
select * from v$fast_start_servers;

no rows selected

现在回滚特别慢。按道理v$fast_start_servers应该是能查到信息的,请帮忙看下是否正常,多谢!
  • 1. Re: 大事务回滚特别慢
    Fixed Newbie
    Currently Being Moderated
    查询x$ktuxe情况如下:
    select KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESTA,KTUXESIZ,sysdate from x$ktuxe where KTUXECFL='DEAD';

    KTUXEUSN KTUXESLT KTUXESQN KTUXESTA KTUXESIZ SYSDATE
    ---------- ---------- ---------- ---------------- ---------- ---------
    2 1 52425 ACTIVE 1624060 23-AUG-12
    3 27 65401 ACTIVE 436664 23-AUG-12
    4 8 59257 ACTIVE 864451 23-AUG-12
    7 25 66759 ACTIVE 1019349 23-AUG-12
    8 30 67106 ACTIVE 344043 23-AUG-12
    9 27 100924 ACTIVE 1516770 23-AUG-12
    21 12 71740 ACTIVE 5665456 23-AUG-12
    22 25 24271 ACTIVE 2004202 23-AUG-12
    23 17 29525 ACTIVE 18763 23-AUG-12
    24 1 17423 ACTIVE 668190 23-AUG-12
    26 23 31741 ACTIVE 1016746 23-AUG-12
    28 8 18958 ACTIVE 1042878 23-AUG-12
    29 0 15156 ACTIVE 185850 23-AUG-12
    30 11 30097 ACTIVE 1766414 23-AUG-12
    34 21 8115 ACTIVE 895847 23-AUG-12
    35 14 5897 ACTIVE 818620 23-AUG-12

    16 rows selected.
  • 2. Re: 大事务回滚特别慢
    LiuMaclean(刘相兵) Expert
    Currently Being Moderated
    长代码或日志请用code模式
    我是code模式
    action plan:
    贴出以下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;
    /
    
    
    show parameter  fast_start_parallel_rollback
    如果觉得本回复有意义,请点击本条回复右手边的Helpful(有帮助)或者Correct(完全正确),谢谢!


    Maclean Liu
    Oracle Database Administrator
    Oracle Certified 10g/11g Master     
    www.askmaclean.com
  • 3. Re: 大事务回滚特别慢
    Fixed Newbie
    Currently Being Moderated
     
     show parameter  fast_start_parallel_rollback
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    fast_start_parallel_rollback         string      FALSE
    
     declare
      2    cursor tx is
      3      select
      4        s.username,
      5        t.xidusn,
      6        t.xidslot,
      7        t.xidsqn,
      8        x.ktuxesiz
      9      from
     10        sys.x$ktuxe  x,
     11        sys.v_$transaction  t,
     12        sys.v_$session  s
     13      where
     14        x.inst_id = userenv('Instance') and
     15        x.ktuxesta = 'ACTIVE' and
     16        x.ktuxesiz > 1 and
     17        t.xidusn = x.ktuxeusn and
     18        t.xidslot = x.ktuxeslt and
     19        t.xidsqn = x.ktuxesqn and
     20        s.saddr = t.ses_addr;
     21    user_name  varchar2(30);
     22    xid_usn    number;
     23    xid_slot   number;
     24    xid_sqn    number;
     25    used_ublk1 number;
     26    used_ublk2 number;
     27  begin
     28    open tx;
     29    loop
     30      fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
     31      exit when tx%notfound;
     32      if tx%rowcount = 1
     33      then
     34        sys.dbms_lock.sleep(10);
     35      end if;
     36      select
     37        sum(ktuxesiz)
     38      into
     39        used_ublk2
     40      from
     41        sys.x$ktuxe
     42      where
     43        inst_id = userenv('Instance') and
     44        ktuxeusn = xid_usn and
     45        ktuxeslt = xid_slot and
     46        ktuxesqn = xid_sqn and
     47        ktuxesta = 'ACTIVE';
     48      if used_ublk2 < used_ublk1
     49      then
     50        sys.dbms_output.put_line(
     51          user_name ||
     52          '''s transaction ' ||
     53          xid_usn  || '.' ||
     54          xid_slot || '.' ||
     55          xid_sqn  ||
     56          ' will finish rolling back at approximately ' ||
     57          to_char(
     58            sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
     59            'HH24:MI:SS DD-MON-YYYY'
     60          )
     61        );
     62      end if;
     63    end loop;
     64    if user_name is null
     65    then
     66      sys.dbms_output.put_line('No transactions appear to be rolling back.');
     67    end if;
     68  end;
     69  / 
    No transactions appear to be rolling back.
    Elapsed: 00:00:00.03
  • 4. Re: 大事务回滚特别慢
    LiuMaclean(刘相兵) Expert
    Currently Being Moderated
    action plan;

    请贴出以下脚本的输出

         select ktuxeusn,
                   to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') "Time",
                   ktuxesiz,
                   ktuxesta
              from x$ktuxe
             where ktuxecfl = 'DEAD';
    <font color="red" size="2" face="courier">如果觉得本回复有意义,请点击本条回复右手边的Helpful(有帮助)或者Correct(完全正确),谢谢!</font>


    Maclean Liu
    Oracle Database Administrator
    Oracle Certified 10g/11g Master     
    www.askmaclean.com

    Edited by: Liu Maclean on Aug 22, 2012 8:20 PM

    Edited by: Liu Maclean on Aug 22, 2012 8:23 PM

    Edited by: Liu Maclean on Aug 22, 2012 8:24 PM

    Edited by: Liu Maclean on Aug 22, 2012 8:25 PM

    Edited by: Liu Maclean on Aug 22, 2012 8:25 PM
  • 5. Re: 大事务回滚特别慢
    Fixed Newbie
    Currently Being Moderated
     
    select ktuxeusn,
      2                 to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') "Time",
      3                 ktuxesiz,
      4                 ktuxesta
      5            from x$ktuxe
      6           where ktuxecfl = 'DEAD';
    
      KTUXEUSN Time                      KTUXESIZ KTUXESTA
    ---------- ----------------------- ---------- ----------------
             2 23-AUG-2012 11:23:12       1623689 ACTIVE
             3 23-AUG-2012 11:23:12        436313 ACTIVE
             4 23-AUG-2012 11:23:12        864062 ACTIVE
             7 23-AUG-2012 11:23:12       1019002 ACTIVE
             8 23-AUG-2012 11:23:12        343666 ACTIVE
             9 23-AUG-2012 11:23:12       1516409 ACTIVE
            21 23-AUG-2012 11:23:12       5665002 ACTIVE
            22 23-AUG-2012 11:23:12       2003842 ACTIVE
            23 23-AUG-2012 11:23:12         18427 ACTIVE
            24 23-AUG-2012 11:23:12        667848 ACTIVE
            26 23-AUG-2012 11:23:12       1016402 ACTIVE
            28 23-AUG-2012 11:23:12       1042545 ACTIVE
            29 23-AUG-2012 11:23:12        185517 ACTIVE
            30 23-AUG-2012 11:23:12       1766047 ACTIVE
            34 23-AUG-2012 11:23:12        895352 ACTIVE
            35 23-AUG-2012 11:23:12        818271 ACTIVE
    Elapsed: 00:00:00.00
  • 6. Re: 大事务回滚特别慢
    不了峰 Newbie
    Currently Being Moderated
    10与39对应x$ktuxe 表的 x$ktuxe 的 KTUXEUSN,KTUXESLT 这两个字段
    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 est Day:'|| round(l_end/(l_start -l_end)/60/24,2));
     end;
    /
    查一下看看

    Edited by: 不了峰 on 2012-8-23 上午11:28
  • 7. Re: 大事务回滚特别慢
    LiuMaclean(刘相兵) Expert
    Currently Being Moderated
    反复执行该SQL 观察KTUXESIZ 是否有缩小,若一直没有 则可能SMON ROLLBACK hang住,或者由于性能问题出现ROLLBACK缓慢


    如要进一步分析 请上传AWR性能快照的内容

    <font color="red" size="2" face="courier">如果觉得本回复有意义,请点击本条回复右手边的Helpful(有帮助)或者Correct(完全正确),谢谢!</font>


    Maclean Liu
    Oracle Database Administrator
    Oracle Certified 10g/11g Master     
    www.askmaclean.com
  • 8. Re: 大事务回滚特别慢
    不了峰 Newbie
    Currently Being Moderated
    将参数 FAST_START_PARALLEL_ROLLBACK 改为HIGH 会有用吗?
  • 9. Re: 大事务回滚特别慢
    LiuMaclean(刘相兵) Expert
    Currently Being Moderated
    这个报错也太明显了吧:

    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Thu Aug 23 08:00:05 2012
    Thread 1 advanced to log sequence 6953 (LGWR switch)
      Current log# 5 seq# 6953 mem# 0: /dev/rlvcrmredo05
    Thu Aug 23 08:42:59 2012
    ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20120818115242.CRMHIST','crmhistsrv' SCOPE=MEMORY SID='crmhist1';
    ALTER SYSTEM SET service_names='crmhistsrv' SCOPE=MEMORY SID='crmhist1';
    Thu Aug 23 09:12:36 2012
    ALTER SYSTEM SET fast_start_parallel_rollback='HIGH' SCOPE=BOTH SID='crmhist1';
    Thu Aug 23 09:21:06 2012
    Autotune of undo retention is turned off. 
    ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=BOTH;
    Thu Aug 23 09:23:09 2012
    ALTER SYSTEM RESET _undo_autotune SCOPE=SPFILE;
    Thu Aug 23 09:28:25 2012
    Autotune of undo retention is turned on. 
    ALTER SYSTEM SET _undo_autotune=TRUE SCOPE=BOTH;
    Thu Aug 23 09:34:18 2012
    ALTER SYSTEM SET fast_start_parallel_rollback='FALSE' SCOPE=BOTH;
    Thu Aug 23 09:44:06 2012
    ALTER SYSTEM SET fast_start_parallel_rollback='HIGH' SCOPE=BOTH;
    Thu Aug 23 10:07:40 2012
    ALTER SYSTEM SET fast_start_parallel_rollback='FALSE' SCOPE=BOTH;
    比较明显的有锁存在阻塞了ROLLBACK, 跑一下下面的脚本,仅贴出结果:



    http://www.askmaclean.com/archives/script-diagnostic-oracle-locks.html

    *<font color="red" size="2" face="courier">如果觉得本回复有意义,请点击本条回复右手边的Helpful(有帮助)或者Correct(完全正确),谢谢!</font>*





    Maclean Liu
    Oracle Database Administrator
    Oracle Certified 10g/11g Master     
    www.askmaclean.com

    Edited by: Liu Maclean on Aug 22, 2012 8:50 PM
  • 10. Re: 大事务回滚特别慢
    Fixed Newbie
    Currently Being Moderated
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Wed Aug 22 21:47:11 2012
    Thread 1 advanced to log sequence 6946 (LGWR switch)
      Current log# 4 seq# 6946 mem# 0: /dev/rlvcrmredo04
    Wed Aug 22 22:49:51 2012
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Wed Aug 22 23:55:21 2012
    Thread 1 advanced to log sequence 6948 (LGWR switch)
      Current log# 6 seq# 6948 mem# 0: /dev/rlvcrmredo06
    Thu Aug 23 01:58:49 2012
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Thu Aug 23 01:58:49 2012
    Thread 1 advanced to log sequence 6949 (LGWR switch)
      Current log# 1 seq# 6949 mem# 0: /dev/rlvcrmredo01
    Thu Aug 23 03:31:32 2012
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Thu Aug 23 03:31:32 2012
    Thread 1 advanced to log sequence 6950 (LGWR switch)
      Current log# 2 seq# 6950 mem# 0: /dev/rlvcrmredo02
    Thu Aug 23 04:58:12 2012
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Thu Aug 23 04:58:12 2012
    Thread 1 advanced to log sequence 6951 (LGWR switch)
      Current log# 3 seq# 6951 mem# 0: /dev/rlvcrmredo03
    Thu Aug 23 06:27:42 2012
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
     lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Thu Aug 23 06:27:43 2012
    Thread 1 advanced to log sequence 6952 (LGWR switch)
      Current log# 4 seq# 6952 mem# 0: /dev/rlvcrmredo04
    Thu Aug 23 08:00:04 2012
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Transaction recovery: lock conflict caught and ignored
    Thu Aug 23 08:00:05 2012
    Thread 1 advanced to log sequence 6953 (LGWR switch)
      Current log# 5 seq# 6953 mem# 0: /dev/rlvcrmredo05
    Thu Aug 23 08:42:59 2012
    ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20120818115242.CRMHIST','crmhistsrv' SCOPE=MEMORY SID='crmhist1';
    ALTER SYSTEM SET service_names='crmhistsrv' SCOPE=MEMORY SID='crmhist1';
    Thu Aug 23 09:12:36 2012
    ALTER SYSTEM SET fast_start_parallel_rollback='HIGH' SCOPE=BOTH SID='crmhist1';
    Thu Aug 23 09:21:06 2012
    Autotune of undo retention is turned off. 
    ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=BOTH;
    Thu Aug 23 09:23:09 2012
    ALTER SYSTEM RESET _undo_autotune SCOPE=SPFILE;
    Thu Aug 23 09:28:25 2012
    Autotune of undo retention is turned on. 
    ALTER SYSTEM SET _undo_autotune=TRUE SCOPE=BOTH;
    Thu Aug 23 09:34:18 2012
    ALTER SYSTEM SET fast_start_parallel_rollback='FALSE' SCOPE=BOTH;
    Thu Aug 23 09:44:06 2012
    ALTER SYSTEM SET fast_start_parallel_rollback='HIGH' SCOPE=BOTH;
    Thu Aug 23 10:07:40 2012
    ALTER SYSTEM SET fast_start_parallel_rollback='FALSE' SCOPE=BOTH;
  • 11. Re: 大事务回滚特别慢
    LiuMaclean(刘相兵) Expert
    Currently Being Moderated
    查下SMON进程的 wait event 等待事件, 及其历史等待事件。 贴出SMON对应的trace 文件的内容


    *<font color="red" size="2" face="courier">如果觉得本回复有意义,请点击本条回复右手边的Helpful(有帮助)或者Correct(完全正确),谢谢!</font>*





    Maclean Liu
    Oracle Database Administrator
    Oracle Certified 10g/11g Master     
    www.askmaclean.com
  • 12. Re: 大事务回滚特别慢
    user12022473 Newbie
    Currently Being Moderated
    AE Edition Lock Prevent Dropping an edition in use


    和code edition有关的锁,看看dba_editioning_views
  • 13. Re: 大事务回滚特别慢
    Fixed Newbie
    Currently Being Moderated
     select distinct EVENT from  v$active_session_history where PROGRAM='oracle@crmhist1 (SMON)';
    
    EVENT
    -----------------------------------
    db file sequential read
    
    
    Elapsed: 00:00:00.29
    看到都只有db file sequential read一个等待事件。
  • 14. Re: 大事务回滚特别慢
    Fixed Newbie
    Currently Being Moderated
    dba_editioning_views没有信息。
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points