This discussion is archived
2 Replies Latest reply: Dec 2, 2012 4:21 AM by LiuMaclean(刘相兵) RSS

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

958613 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    两个脚本:
    [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(刘相兵) Expert
    Currently Being Moderated
    大表的 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

Legend

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