1 2 Previous Next 21 Replies Latest reply: Aug 23, 2012 2:42 AM by Fixed Go to original post RSS
      • 15. Re: 大事务回滚特别慢
        Fixed
        之前9点多查的:
        09:19:29 SYS@crmhist1 SQL> declare 
        09:19:52   2  l_start number; 
        09:19:52   3  l_end    number; 
        09:19:52   4  begin 
        09:19:52   5    select ktuxesiz into l_start from x$ktuxe where  KTUXEUSN=21 and KTUXESLT=12; 
        09:19:52   6    dbms_lock.sleep(60); 
        09:19:52   7    select ktuxesiz into l_end from x$ktuxe where  KTUXEUSN=21 and KTUXESLT=12; 
        09:19:52   8    dbms_output.put_line('time est Day:'|| round(l_end/(l_start -l_end)/60/24,2)); 
        09:19:52   9  end; 
        09:19:52  10  /
        time est Day:327.91
        刚才12点查的:
         declare 
        12:33:20   2  l_start number; 
        12:33:20   3  l_end    number; 
        12:33:20   4  begin 
        12:33:20   5    select ktuxesiz into l_start from x$ktuxe where  KTUXEUSN=21 and KTUXESLT=12; 
        12:33:20   6    dbms_lock.sleep(60); 
        12:33:20   7    select ktuxesiz into l_end from x$ktuxe where  KTUXEUSN=21 and KTUXESLT=12; 
        12:33:20   8    dbms_output.put_line('time est Day:'|| round(l_end/(l_start -l_end)/60/24,2)); 
        12:33:20   9  end; 
        12:33:20  10  /
        time est Day:393.35
        • 16. Re: 大事务回滚特别慢
          user8866386
          from smon.trc :
          *** 2012-08-23 12:13:53.608
          Serial Transaction recovery caught exception 30319
          
          *** 2012-08-23 12:13:53.652
          * kju_tsn_aff_drm_pending TRACEUD: called with tsn x2, dissolve 0 
          * kju_tsn_aff_drm_pending TRACEUD: tsn_pkey = x2.1 
          
          * >> RM REQ QS ---: 
           single window RM request queue is empty 
           multi-window RM request queue is empty 
          * Global DRM state ---: 
             There is no dynamic remastering 
             RM lock state = 0 
           pkey 2.1 undo 1 stat 0 masters[32768, 1->1] reminc 4 RM# 1
           flg x0 type x0 afftime xbcf3ee85
           nreplays by lms 0 = 0 
           nreplays by lms 1 = 0 
          * kju_tsn_aff_drm_pending TRACEUD: matching request not found on swin queue
          * kju_tsn_aff_drm_pending TRACEUD: pp found, stat x0 
          * kju_tsn_aff_drm_pending TRACEUD: 2 return true 
          however,there is no info about "Serial Transaction recovery caught exception 30319" ,and no message about "oerr ora 30319".
          In MOS, some unsolved bugs can be found with "Serial Transaction recovery caught exception 30319" by all source, and not helpful directly.

          additionally, suggest checking other instance
          • 17. Re: 大事务回滚特别慢
            user8866386
            please refer to Bug 14357521 : TRANSACTION RECOVERY: LOCK CONFLICT CAUGHT AND IGNORED.
            although the symptom is not totally same, the "drop ..purge and recreate table " may be helpful.
            • 18. Re: 大事务回滚特别慢
              LiuMaclean(刘相兵)
              "The lock conflicts just look to be due to both instances trying
              to recover the same TX. The undo seg looks like it belongs to
              instance 2 and instance 2 is repeatedly trying to recover it."


              看起来是一个BUG , 建议你提交一个MOS SR,并提供《Bug 14357521 : TRANSACTION RECOVERY: LOCK CONFLICT CAUGHT AND IGNORED》的信息给Oracle GCS


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





              Maclean Liu
              Oracle Database Administrator
              Oracle Certified 10g/11g Master     
              www.askmaclean.com
              • 19. Re: 大事务回滚特别慢
                Fixed
                你好,只有这一个实例是打开的。
                • 20. Re: 大事务回滚特别慢
                  LiuMaclean(刘相兵)
                  Advice:


                  1.忽略该问题,结果是可能造成对这些表的查询需要访问UNDO产生大量CR读

                  2. 尝试杀掉所有的服务进程 ,kill LOCAL=NO

                  3. 重启DB INSTANCE修改该问题


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





                  Maclean Liu
                  Oracle Database Administrator
                  Oracle Certified 10g/11g Master     
                  www.askmaclean.com
                  • 21. Re: 大事务回滚特别慢
                    Fixed
                    多谢刘大以及各位大牛们,已经提交MOS SR。
                    1 2 Previous Next