查询dba_jobs视图hang住，等待事件enq:TXcontention

现象：
客户反馈执行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等