1 2 Previous Next 21 Replies Latest reply: Aug 23, 2012 2:42 AM by Fixed RSS

    大事务回滚特别慢

    Fixed
      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
          查询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(刘相兵)
            长代码或日志请用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
               
               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(刘相兵)
                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
                   
                  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: 大事务回滚特别慢
                    不了峰
                    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(刘相兵)
                      反复执行该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: 大事务回滚特别慢
                        不了峰
                        将参数 FAST_START_PARALLEL_ROLLBACK 改为HIGH 会有用吗?
                        • 9. Re: 大事务回滚特别慢
                          LiuMaclean(刘相兵)
                          这个报错也太明显了吧:

                          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
                            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(刘相兵)
                              查下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
                                AE Edition Lock Prevent Dropping an edition in use


                                和code edition有关的锁,看看dba_editioning_views
                                • 13. Re: 大事务回滚特别慢
                                  Fixed
                                   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
                                    dba_editioning_views没有信息。
                                    1 2 Previous Next