使用oradebug dump processstate 來診斷enq: TX - row lock contention

朋友的應用程式在年度結轉時呼叫儲存過程時hang住了.經過除錯儲存過程發現執行到下面的語句時被hang住.
UPDATE t_config_info
       SET last_do_time = systimestamp
     WHERE config_id = config_record.config_id;
    IF SQL%ROWCOUNT = 0 THEN
      RAISE error1;
    END IF;


解決這個問題的方法就是找到執行儲存過程的會話,並用oradebug來dump程式資訊.先執行下面的語句來找到執行儲存過程會話對應的spid.
SQL>select p.spid
from v$session s,v$process p,v$sqlarea c
where s.username is not null and s.PADDR=p.ADDR and s.sql_id=c.sql_id
and s.sql_fulltext like'%UPDATE t_config_info%'
SPID
----------
14483524
得到的spid為14483524


在另一個會話中執行下面的語句
SQL> oradebug setospid 14483524
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump processstate 10
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/hygeia/hygeia1/trace/hygeia2_ora_14483524.trc


從得到的跟蹤檔案中可以看到以下資訊:
 SO: 0x700000758606100, type: 4, owner: 0x70000075c4e73e0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x70000075c4e73e0, name=session, file=ksu.h LINE:11467 ID:, pg=0
    (session) sid: 539 ser: 14973 trans: 0x700000753a47aa8, creator: 0x70000075c4e73e0
              flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x40008) -/-
              DID: , short-term DID:
              txn branch: 0x0
              oct: 6, prv: 0, sql: 0x7000005cced65d0, psql: 0x70000076ed6dc58, user: 123/ZWJH_MM
    ksuxds FALSE at location: 0
    service name: hygeia
    client details:
      O/S info: user: Administrator, term: LENOVO-JGXROLVS, ospid: 6608:6612
      machine: WORKGROUP\LENOVO-JGXROLVS program: plsqldev.exe
      application name: PL/SQL Developer, hash value=1190136663
      action name: SQL Window - New, hash value=3399691616
    Current Wait Stack:
     0: waiting for 'enq: TX - row lock contention'
        name|mode=0x54580006, usn<<16 | slot=0x15000b, sequence=0x362616
        wait_id=811 seq_num=812 snap_id=1
        wait times: snap=29.036136 sec, exc=29.036136 sec, total=29.036136 sec
        wait times: max=infinite, heur=29.036136 sec
        wait counts: calls=59 os=59
        in_wait=1 iflags=0x15a0
    There is at least one session blocking this session.
      Dumping 1 direct blocker(s):
        inst: 1, sid: 625, ser: 53645
      Dumping final blocker:
        inst: 1, sid: 625, ser: 53645


上面的inst: 1, sid: 625, ser: 53645可以知道造成阻塞的會話是1號例項中的會話sid,serial#為625,53645,被人為的kill掉了,然後後繼多次執行這個儲存過程當hang住後又kill掉了,找到阻塞的會話也知道問題產生的原因了問題也就解決了.