使用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掉了,找到阻塞的会话也知道问题产生的原因了问题也就解决了.