2 Replies Latest reply: Dec 2, 2012 6:21 AM by LiuMaclean(刘相兵) RSS

    update操作异常终止紧接执行相同命令(update)非常慢

    958613
      OS:OEL 5.6 64BIT
      
      sys@MAA> select * from v$version;
      
      BANNER
      ----------------------------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE    11.2.0.3.0      Production
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      
      有一张大表,我先批量更新,中途异常终止(在这里kill session来模拟)
      
      luocs@MAA> select count(*) from l_bigt;
      
        COUNT(*)
      ----------
        10000000
      
      luocs@MAA> select sid from v$mystat where rownum=1;
      
             SID
      ----------
              23
      
      luocs@MAA> set timing on
      
      luocs@MAA> update l_bigt set object_name='LUOCS';
      
      大约过了2分钟后,我kill掉该session
      sys@MAA> select SERIAL#,sid from v$session where sid=23;
      
         SERIAL#        SID
      ---------- ----------
            2135         23
      
      sys@MAA> alter system kill session '23,2135';
      
      System altered.
      
      紧接着我又执行了更新语句:
      luocs@MAA> select distinct sid from v$mystat;
      
             SID
      ----------
              53
      
      luocs@MAA> update l_bigt set object_name='LUOCS';
      -- 这时候感觉像HANG住
      
      查看lock信息
      sys@MAA> @showlock
      
             SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK
      ---------- ---- ---------- ---------- ---------- ---------- ----------
              13 TX       327709        979          6          0          1
              53 TM        24831          0          3          0          0
              53 TX        65557        803          6          0          0
              53 TX       327709        979          0          6          0
      
      sys@MAA> @showevent
      Enter value for sid: 53
      old   3: where sid=&sid
      new   3: where sid=53
      
      EVENT                                                   TOTAL_WAITS TIME_WAITED
      ------------------------------------------------------- ----------- -----------
      enq: TX - row lock contention                                   352       38329
      log file switch (archiving needed)                                1       13660
      db file sequential read                                       19463        1911
      db file scattered read                                          142         523
      SQL*Net message from client                                      17         215
      log buffer space                                                  8          82
      Data file init write                                              6          67
      KSV master wait                                                  36          52
      log file switch (checkpoint incomplete)                           7          32
      control file sequential read                                     60          23
      events in waitclass Other                                        71          21
      kfk: async disk IO                                                3          14
      log file switch completion                                        3          10
      control file parallel write                                       9           9
      db file single write                                              3           9
      Disk file operations I/O                                         16           1
      latch: redo writing                                               1           0
      SQL*Net message to client                                        17           0
      
      18 rows selected.
      -- 可见产生大量enq: TX - row lock contention等待事件
      我在这里想问的是,为什么异常终止后rollback在SID 13上进行,为什么SID 13阻塞别的的SESSION?

      我的这条更新等了近28分钟后终于执行完毕
      luocs@MAA> update l_bigt set object_name='LUOCS';
      
      10000000 rows updated.
      
      Elapsed: 00:27:52.93
      Edited by: 955610 on 2012-12-1 上午1:50

      Edited by: 955610 on 2012-12-1 上午3:13
        • 1. Re: update操作异常终止紧接执行相同命令(update)非常慢
          958613
          两个脚本:
          [oracle@maa3 ~]$ cat showevent.sql
          select event, total_waits, time_waited 
          from v$session_event 
          where sid=&sid
          order by 3 desc
          /
          
          [oracle@maa3 ~]$ cat showlock.sql 
          select SID, 
                  TYPE, 
                  ID1, 
                  ID2, 
                  LMODE, 
                  REQUEST, 
                  BLOCK 
          from v$lock 
          where TYPE in ('TM','TX') 
          order by 1,2
          /
          • 2. Re: update操作异常终止紧接执行相同命令(update)非常慢
            LiuMaclean(刘相兵)
            大表的 update 、rollback都很慢, kill session 之后SMON 负责ROLLBACK TRANSATION, 之前被 UPDATE的数据 需要ROLLBACK之后才会释放行锁, 如果继续对该大表UPDATE则非常慢, 你应当监控下 SMON 何时能回滚完。


            可参考信息 reference:

            监控一个大事务的回滚
            http://www.askmaclean.com/archives/%E7%9B%91%E6%8E%A7%E4%B8%80%E4%B8%AA%E5%A4%A7%E4%BA%8B%E5%8A%A1%E7%9A%84%E5%9B%9E%E6%BB%9A.html


            了解你所不知道的SMON功能(五):Recover Dead transaction
            http://www.askmaclean.com/archives/smon-recover-dead-transaction.html