enq: DX – contention和inactive transaction branch等待时间高

lai

enq: DX – contention和inactive transaction branch平均等待时间分别为6s和1s，网络上查了下，是说因为bug引起的。麻烦大家帮忙下，是现网数据库出现了这个情况，所以比较急

Maclean Liu(刘相兵

SQL ordered by Reads

    Total Disk Reads: 344,607,433
    Captured SQL account for 99.1% of Total

Physical Reads    Executions    Reads per Exec     %Total    CPU Time (s)    Elapsed Time (s)    SQL Id    SQL Module    SQL Text
339,079,748    715    474,237.41    98.40    9654.87    35087.42    3hmw62da52uq1     oracle@FJXM-DM-PTLBBS1 (TNS V1-V3)     SELECT /*+ PARALLEL ("A", 4) *...
2,365,091    5    473,018.20    0.69    79.26    230.18    2sp82wgydgamz     oracle@FJXM-DM-PTLBBS1 (TNS V1-V3)     SELECT /*+ PARALLEL ("A", 4) *...

以上2个查询是以DB LINK从远程数据库连接(FJXM-DM-PTLBBS1)过来的吗？主要焦点就是这2条SQL， 都用了并行

Maclean Liu(刘相兵

enq: DX - contention
Serializes tightly coupled distributed transaction branches

830        2881503609        enq: DX - contention       
p1 name|mode       
p2 transaction entry #       
p3 0       
wait_class_id 1893977003       
wait_class#  0       
wait_class  Other


enq: DX - contention 队列争用等待事件 time=6000ms 即该wait event 6s超时

Maclean Liu(刘相兵

inactive transaction branch        Other        branch#        waited         

The session waits for a transaction branch that is currently used by another session.

737        403545727        inactive transaction branch       
p1  branch#       
p2  waited               
p3 NULL
wait_class Other

inactive transaction branch        队列争用等待事件 time=1000ms 即该wait event 1s 超时

相关信息：

Bug 10413418  Query with dblink intermittently hang waiting for 'inactive transaction branch'

Distributed transactions could hang waiting in k2gInsert for 'inactive
transaction branch'.

Rediscovery Notes:
  This particular bug could have a number of symptoms.  The one which the
  customer hit can be verified by seeing that k2gInsert is directly calling
  kslwait and is waiting for 'inactive transaction branch'.

Workaround
None

panwenyu536

登陆到服务器上看一下 3hmw62da52uq1 2sp82wgydgamz 的执行计划
select * from table(dbms_xplan.display_cursor('3hmw62da52uq1'));
select * from table(dbms_xplan.display_cursor('2sp82wgydgamz '));