My Oracle Support Banner

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

Last updated on FEBRUARY 25, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 9.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.


-- 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.col 1, pp.col 2, pp.Col 3, pb.Col 4
FROM <Table 1>@<dblink.domain name> p, <Table 2>@<dblink.domain name> pp, <Table 3>@<dblink.domain name> pb
WHERE pp.Col 3 = p.Col 3 AND
pb.Col 3 = pp.Col 3 AND
Col 5 (pp.Col 3, pp.Col 2 , SYSDATE, pb.Col 4) = '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


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

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/<Schema>
O/S info: user: XXX, term: pts/tb, ospid: 11772, machine: YYY
program: oracle@<dblink.domain name> (TNS V1-V3)
application name: oracle@<dblink.domain name>(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)
name=SELECT "col 3","Col 2" FROM "<Table 2>".
"<col 2>" "PP"
hash=bd1ceef17a931288bed1ec5cc36ca4ae timestamp=05-15-2008 11:21:50
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/<Schema>
O/S info: user: oracle, term: pts/tb, ospid: 11772, machine: psdev1
program: oracle@<dblink> (TNS V1-V3)
application name: oracle@<dblink> (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.


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.