1. 首页
  2. IT资讯

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28686045/viewspace-1784237/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195527.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code