查询dba_jobs视图hang住,等待事件enq: TX contention

原创 2013年12月23日 23:20:29
现象:
客户反馈执行select job,what from dba_jobs时session Hang住,等待事件为enq: TX contention。
现象:
因为没在现场,所以我要求客户执行如下SQL:
a).生成10046trace:
SQL> connect username/password
SQL> alter session set tracefile_identifier='10046trace';
SQL> alter session set timed_statistics = true;
SQL> alter session set statistics_level=all;
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> select job,what from dba_jobs;
b). 另开一个session,生成hanganalyze trace:
SQL>oradebug setmypid
SQL>oradebug unlimit;
SQL>oradebug dump hanganalyze 3
Wait for 30 seconds
SQL>oradebug dump hanganalyze 3
Wait for 30 seconds
SQL>oradebug dump hanganalyze 3
SQL> oradebug tracefile_name
c). 开一个新的会话,生成systemstate
SQL>oradebug setmypid
SQL>oradebug unlimit
SQL>oradebug dump systemstate 10
Wait for 30 seconds
SQL>oradebug dump systemstate 10
SQL>oradebug tracefile_name

10046:
PARSING IN CURSOR #1 len=29 dep=0 uid=0 oct=3 lid=0 tim=137502489 hv=2963431435 ad='3816f698'
select job,what from dba_jobs
END OF STMT
PARSE #1:c=93750,e=144411,p=17,cr=321,cu=0,mis=1,r=0,dep=0,og=1,tim=137502487
BINDS #1:
EXEC #1:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=137502778
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=137502803
WAIT #1: nam='db file sequential read' ela= 1630 file#=1 block#=1657 blocks=1 obj#=237 tim=137504471
WAIT #1: nam='enq: TX - contention' ela= 995528 name|mode=1415053316 usn<<16 | slot=1179661 sequence=7710334 obj#=237 tim=138500082
WAIT #1: nam='enq: TX - contention' ela= 1999676 name|mode=1415053316 usn<<16 | slot=1179661 sequence=7710334 obj#=237 tim=140499853
WAIT #1: nam='enq: TX - contention' ela= 2999565 name|mode=1415053316 usn<<16 | slot=1179661 sequence=7710334 obj#=237 tim=143499520
WAIT #1: nam='enq: TX - contention' ela= 999803 name|mode=1415053316 usn<<16 | slot=1179661 sequence=7710334 obj#=237 tim=144499411
*** 2013-06-18 10:30:13.329
<...snipped...>
*** 2013-06-18 10:37:06.329
WAIT #1: nam='enq: TX - contention' ela= 2999585 name|mode=1415053316 usn<<16 | slot=1179661 sequence=7710334 obj#=237 tim=560453211
WAIT #1: nam='enq: TX - contention' ela= 2999571 name|mode=1415053316 usn<<16 | slot=1179661 sequence=7710334 obj#=237 tim=563452876
WAIT #1: nam='enq: TX - contention' ela= 2999586 name|mode=1415053316 usn<<16 | slot=1179661 sequence=7710334 obj#=237 tim=566452539

hanganalyze trace:
*** 2013-06-18 10:30:43.407
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/78/65191/0x5c041478/4368/SQL*Net message from dblink>
 -- <0/139/51158/0x5c06a920/12992/enq: TX - contention>
Other chains found:
<...snipped...>
Chain 7 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/172/23264/0x5c034068/15528/enq: TX - contention>  <-- 发现DB中还存在其他的chain


systemstate(ass09.awk解释后的):
System State 1 (2013-06-18 10:32:11.485)
~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~
<...snipped...>
18: waiting for 'rdbms ipc message' seq=7781
20: waiting for 'enq: TX - contention' [Enq TX-0012000D-0075A67E] seq=41370   <--1.大量session在等待'enq: TX - contention([Enq TX-0012000D-0075A67E])'
<...snipped...>
136:waiting for 'enq: TX - contention' [Enq TX-0012000D-0075A67E] seq=19
Cmd: Select
<...snipped...>
180:waiting for 'enq: TX - contention' [Enq TX-0012000D-0075A67E] seq=17
Cmd: Select
<...snipped...>
206:waiting for 'enq: TX - contention' [Enq TX-0012000D-0075A67E] seq=38
Cmd: Select
<...snipped...>
232:waiting for 'db file sequential read' (e,1863d4,1) seq=17277
Cmd: Select
<...snipped...>
246:waiting for 'enq: TX - contention' [Enq TX-0012000D-0075A67E] seq=28
Cmd: Select
<...snipped...>
258:waiting for 'SQL*Net message from dblink' seq=1947                        <--3.session258在等待SQL*Net message from dblink
<...snipped...>
362:waiting for 'enq: TX - contention' [Enq TX-0012000D-0075A67E] seq=48
Cmd: Select
Resource Holder State
Enq TX-0012000D-0075A67E 258: waiting for 'SQL*Net message from dblink'       <--2.[Enq TX-0012000D-0075A67E]的Holder为session 258


systemstate(raw):
PROCESS 258:
  ----------------------------------------
  SO: 000000025C041478, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
  (process) Oracle pid=258, calls cur/top: 000000025F930F60/000000025F97FF88, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 109 0 4
              last post received-location: kslpsr
              last process to post me: 5c002940 1 6
              last post sent: 0 0 24
              last post sent-location: ksasnd
              last process posted by me: 5c002940 1 6
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 000000013F3AF610
    O/S info: user: SYSTEM, term: R-DATABASE, ospid: 4368
   OSD pid info: Windows thread id: 4368, image: ORACLE.EXE (J000)  <-- session258进程为JOB进程
<...snipped...>
    SO: 000000025C118F48, type: 4, owner: 000000025C041478, flag: INIT/-/-/0x00
    (session) sid: 78 trans: 000000025F510CB0, creator: 000000025C041478, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0001-0102-000FC0ED, short-term DID: 0000-0000-00000000
              txn branch: 000000025F5A9FA0
              oct: 0, prv: 0, sql: 0000000000000000, psql: 0000000127BAEDF0, user: 59/MES2ND
    service name: SYS$USERS
    O/S info: user: SYSTEM, term: R-DATABASE, ospid: 4368, machine: R-DATABASE
              program: ORACLE.EXE (J000)
    waiting for 'SQL*Net message from dblink' blocking sess=0x0000000000000000 seq=1947 wait_time=0 seconds since wait started=133514
                driver id=0, #bytes=1, =0
    Dumping Session Wait History
     for 'SQL*Net message to dblink' count=1 wait_time=1
                driver id=0, #bytes=1, =0

 

     SO: 00000001F46C5DC0, type: 53, owner: 000000025C118F48, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=f46c5dc0 handle=27baedf0 mode=N
      call pin=0000000000000000 session pin=0000000000000000 hpc=0000 hlc=0000
      htl=00000001F46C5E40[0000000134356368,0000000135C257D0] htb=0000000134356368 ssga=0000000134355C60
      user=5c118f48 session=5c118f48 count=1 flags=[0000] savepoint=0x51bdbd17
      LIBRARY OBJECT HANDLE: handle=27baedf0 mtx=0000000127BAEF20(1) cdp=1
      name=
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN --?ì2ékey?aê§?êìa£?JOB??5·??ó?′DDò?′?£???é?oó??D??ó??
DELETE op_keyaccessory_REAL@DB;
INSERT INTO op_keyaccessory_REAL@DB   <--dblink DML操作
SELECT * FROM op_keyaccessory_1766
WHERE create_time >to_date('2013/05/01 01:00:00','yyyy/mm/dd HH24:mi:ss'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
      hash=1156c6d87688ddacd7b1919b7fa4bf5f timestamp=05-16-2013 15:57:05

可见session258是在执行一个dblink DML操作的时候由于Target端的故障(故障原因可能为:1、网络故障2、远端OS故障3、远端DB故障4、远端进程故障,这些故障都可能导致本文问题出现)导致异常,所拥有的资源无法释放,造成了相关session被阻塞。
解决方法:
kill进程J000之后,问题解决。
如果问题频繁出现,需要解决导致原因的4个方面:
1.网络是否不稳定,有异常?
2.DB是否重启过或者其他故障?
3.OS是否有异常,如重启等
4.远端进程是否有异常,如被kill等

相关文章推荐

事务上的等待事件 —— enq: TX - contention

TX锁是保护事务的,事务结束时便会释放。因此,为获得TX锁为等待的会话,要等到拥有锁的会话的事务结束为止。 SQL> select name,parameter1,parameter2,parame...

Oracle enq: TX contention 和 enq: TM contention 等待事件说明

和Oracle性能优化密切相关的一些知识参考如下Blog:        Oracle 常见的33个等待事件       http://blog.csdn.net/tianlesoftware/arc...

ORACE_常见等待事件001_enq: TX - row lock contention

enq: TX - row lock contention是oracle常见的等待事件之一。enq是一种保护共享资源的锁定机制,一个排队机制,先进先出(FIFO)发生TX锁的原因一般有几个: 1.不...

oracle 11g数据库”enq: TX - row lock contention“等待事件的处理

今天早晨发现数据库出现”enq: TX - row lock contention“等待,记录下处理过程: SQL*Plus: Release 11.2.0.2.0 Production   SI...

enq: TX - row lock contention 等待事件

OS环境:windows server 2008 64位数据库版本:11.2.0今天在使用rman备份的时候随意的查看了一下等待事件,除了了我们现在系统遇到的IO瓶颈外,还额外的发了enq: TX -...

一次大量enq: TX - row lock contention锁等待的问题

今天下午接到业务报障,系统出现问题,可能是数据库的问题 1,登录系统,查看等待事件,大量row lock 6:12:58] [16:12:58]   SID    SERIAL# OSUSER ...
  • hijk139
  • hijk139
  • 2012年09月28日 17:05
  • 3066

enq:SQ contention / row cache lock / DFS lock handle(SV) 等待事件 说明

enq:SQ contention/row cache lock/DFS lock handle(SV) 这三个等待事件都与Oracle 的Sequence 有关。 有关Sequence说明,参考我的...

Oracle Study之-- enq:SQ contention等待事件

Oracle Study之-- enq:SQ contention等待事件 通过AWR Report发现“enq:SQ contention”等待事件: 应用环境:  enq:...
  • lqx0405
  • lqx0405
  • 2015年06月02日 17:13
  • 2070

enq: TM - contention TM 等待事件的原因及模拟(表外键约束无索引导致)

近一个客户反映数据库在执行远程INSERT的时候应用反映很慢,在做AWR后发现enq: TM - contention等待事件很高,应该是外键约束上没有建立索引的问题。 下面我来做个实验还原一下当时...

事务上的等待事件 —— enq: TM - contention

执行DML期间,为防止对与DML相关的对象进行修改,执行DML的进程必须对该表获得TM锁。若在获得TM锁的过程中发生争用,则等待enq: HW - contention 事件。 SQL> selec...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:查询dba_jobs视图hang住,等待事件enq: TX contention
举报原因:
原因补充:

(最多只允许输入30个字)