Select With Local Function and Remote Tables Using a Dblink Hangs Due To Enq DX (Doc ID 730423.1)

Last updated on FEBRUARY 07, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.1 [Release 9.2 to 11.2]
Information in this document applies to any platform.


Symptoms

-- Problem Statement:
Two databases interacting via a private DBLINK.

The query at the LOCAL Site includes references for remote objects (via a dblink) as well as a LOCAL FUNCTION (that does not exist at the remote Site):

SELECT p.wey_cc_category_id, pp.wey_cc_phase_id, pp.projectname, pb.wey_cc_bndl_nbr
FROM ps_wey_ccprjdefn@WEYCC8.WORLD p, ps_wey_cc_prj_ph@WEYCC8.WORLD pp, ps_wey_cc_prj_bndl@WEYCC8.WORLD pb
WHERE pp.projectname = p.projectname AND
pb.projectname = pp.projectname AND
f_wey_get_bndl_ready_status (pp.projectname, pp.wey_cc_phase_id , SYSDATE, pb.wey_cc_bndl_nbr) = 'BR';




When executing the query it hangs and systemstate dumps (at the remote site) show something like:

17: waiting for 'SQL*Net more data to client' seq=138
Cmd: Select
18: waiting for 'enq: DX - contention' [Enq DX-0000001C-00000000] seq=9

.
.
Blockers
~~~~~~~~

Resource Holder State
Enq DX-0000001C-00000000 17: waiting for 'SQL*Net more data to client'
.
.


PROCESS 17:
----------------
.
.
SO: c00000032b285470, type: 4, owner: c00000032b1dd670, flag: INIT/-/-/0x00
(session) sid: 93 trans: c00000032a75c1c8, creator: c00000032b1dd670, flag:
(41) USR/- BSY/-/-/-/-/-
DID: 0001-0011-0000CAE4, short-term DID: 0000-0000-00000000
txn branch: c00000032a794db0
oct: 3, prv: 0, sql: c0000003267ce3d0, psql: c000000326883d68, user: 39/WEYCC88
O/S info: user: oracle, term: pts/tb, ospid: 11772, machine: psdev1
program: oracle@psdev1 (TNS V1-V3)
application name: oracle@psdev1 (TNS V1-V3), hash value=0
waiting for 'SQL*Net more data to client' blocking sess=0x0000000000000000
seq=138 wait_time=0 seconds since wait started=681
driver id=54435000, #bytes=7cf, =0
.
.
SO: c000000326441838, type: 53, owner: c00000032b285470, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=c000000326441838 handle=c0000003267ce3d0 mode=N
call pin=0000000000000000 session pin=0000000000000000 hpc=0000 hlc=0000
htl=c0000003264418b8[c0000003267c39b8,c000000326447e30] htb=c0000003267c39b8 ssga=c0000003267c2dc0 user=c00000032b285470 session=c00000032b285470 count=1 flags=[0000] savepoint=0x48447c16
LIBRARY OBJECT HANDLE: handle=c0000003267ce3d0 mtx=c0000003267ce500(1)
cdp=1
name=SELECT "PROJECTNAME","WEY_CC_PHASE_ID" FROM "WEYCC88".
"PS_WEY_CC_PRJ_PH" "PP"
hash=bd1ceef17a931288bed1ec5cc36ca4ae timestamp=05-15-2008 11:21:50
.
.
PROCESS 18:
---------------
.
.
SO: c00000032b293490, type: 4, owner: c00000032b1dde60, flag: INIT/-/-/0x00
(session) sid: 104 trans: 0000000000000000, creator: c00000032b1dde60, flag:
(c0000041) USR/- BSY/-/-/-/-/-
DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000
txn branch: c00000032a794ef0
oct: 0, prv: 0, sql: 0000000000000000, psql: 0000000000000000, user: 39/WEYCC88
O/S info: user: oracle, term: pts/tb, ospid: 11772, machine: psdev1
program: oracle@psdev1 (TNS V1-V3)
application name: oracle@psdev1 (TNS V1-V3), hash value=0
waiting for 'enq: DX - contention' blocking sess=0xc00000032b285470 seq=9
wait_time=0 seconds since wait started=680
name|mode=44580006, transaction entry #=1c, 0=0
Dumping Session Wait History
for 'enq: DX - contention' count=1 wait_time=2939328
name|mode=44580006, transaction entry #=1c, 0=0
.
.


Due to this lock, the query hangs.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms