Select statement hangs over DBlink (Intermittent)

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Select statement hangs over DBlink (Intermittent)

Select statement hangs over DBlink (Intermittent)

2005-09-27 - By Sami Seerangan

Reply: 1 2 3 4

QUERY

c1 refcursor;
c2 refcursor;
BEGIN BIB_OTP_PKG.get_token_details('123678456',:c1,:c2); END;

I am executing the stored procedure which includes select over DB Link. Most
of the time it works (> 95%) and few times it hangs.

I took 10053 event trace and it says wait event on 'SQL*Net message from
dblink' ,'SQL*Net message to dblink' and 'SQL*Net break/reset to dblink'.
Any idea what is going wrong here?

/opt/oracle/admin/dnet13/udump/dnet131_ora_21694_bib_otp_pkg_03.trc

*** TRACE DUMP CONTINUED FROM FILE
/opt/oracle/admin/dnet13/udump/dnet131_ora_21694_bib_otp_pkg_02.trc ***

EXEC #3:c=0,e=1147,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=951110760122
WAIT #3: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 496 p1=1650815232 p2=1 p3=0
QUERY
alter session set events '10053 trace name context forever, level 12'
=====================
PARSING IN CURSOR #3 len=69 dep=0 uid=164 oct=42 lid=164 tim=951110760998
hv=2800020113 ad='a5e71f18'
alter session set events '10053 trace name context forever, level 12'
END OF STMT
PARSE #3:c=0,e=106,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=951110760994
BINDS #3:
EXEC #3:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=951110761210
WAIT #3: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 923 p1=1650815232 p2=1 p3=0
QUERY
BEGIN BIB_OTP_PKG.get_token_details('0058020258',:c1,:c2); END;
=====================
PARSING IN CURSOR #3 len=65 dep=0 uid=164 oct=47 lid=164 tim=951110762903
hv=727016698 ad='a5f7d828'
BEGIN BIB_OTP_PKG.get_token_details('0058020258',:c1,:c2); END;
END OF STMT
PARSE #3:c=0,e=306,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=951110762895
BINDS #3:
bind 0: dty=102 mxl=04(04) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=16
offset=0
bfp=ffffffff7c95fb48 bln=04 avl=04 flg=05
value=
Dump of memory from 0xFFFFFFFF7C95FB48 to 0xFFFFFFFF7C95FB4C
FFFFFFFF7C95FB40 00000000 [....]
bind 1: dty=102 mxl=04(04) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=0
offset=8
bfp=ffffffff7c95fb50 bln=04 avl=04 flg=01
value=
Dump of memory from 0xFFFFFFFF7C95FB50 to 0xFFFFFFFF7C95FB54
FFFFFFFF7C95FB50 00000000 [....]
WAIT #4: nam='SQL*Net message to dblink' ela= 4 p1=1413697536 p2=1 p3=0
*** 2005-09-27 18:03:19.304
WAIT #4: nam='SQL*Net message from dblink' ela= 509763902 p1=1413697536 p2=1
p3=0
WAIT #4: nam='SQL*Net break/reset to dblink' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net break/reset to dblink' ela= 93 p1=1413697536 p2=0 p3=0
WAIT #4: nam='single-task message' ela= 94523 p1=0 p2=0 p3=0
WAIT #4: nam='SQL*Net message to dblink' ela= 3 p1=1413697536 p2=28 p3=0
WAIT #4: nam='SQL*Net message from dblink' ela= 6925 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message to dblink' ela= 1 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message from dblink' ela= 2284 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message to dblink' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message from dblink' ela= 13798 p1=1413697536 p2=1
p3=0
WAIT #4: nam='SQL*Net message to dblink' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message from dblink' ela= 5553 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message to dblink' ela= 1 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message from dblink' ela= 556 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message to dblink' ela= 1 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message from dblink' ela= 2465 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message to dblink' ela= 1 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message from dblink' ela= 335 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message to dblink' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message from dblink' ela= 767 p1=1413697536 p2=1 p3=0

=====================
PARSE ERROR #4:len=618 dep=1 uid=168 oct=3 lid=168 tim=951620661502 err=2051
SELECT A.TOKEN_TYPE, B.COLOR, C.LOCATION_NAME, C.HSBC_ENTITY,
E.TOKEN_STATUS_DESC CURRENT_STATUS, H.STATUS_CHANGE_DATE IN_ST
OCK_DATE, E.TOKEN_STATUS_ID FROM TOKEN_INVENTORY A, TOKEN_ATTRIBUTES B,
TOKEN_DISTRIBUTION_LOCATIONS C, TOKEN_INFORMATION D,
TOKEN_STATUS E, TOKEN_INFORMATION H WHERE A.SERIAL_NUMBER = :B1 AND
A.TOKEN_ATTRIBUTE_ID = B.TOKEN_ATTRIBUTE_ID AND B.HSBC_
LOCATION_ID = C.HSBC_LOCATION_ID AND A.SERIAL_NUMBER = D.SERIAL_NUMBER AND
D.CURRENTFLAG = 'Y' AND D.TOKEN_STATUS_ID = E.TOK
EN_STATUS_ID AND D.APPLICATION_NAME = E.APPLICATION_NAME AND H.SERIAL_NUMBER=
A.SERIAL_NUMBER AND H.TOKEN_STATUS_ID = 30
EXEC
#3:c=10000,e=509899355,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=4,tim=951620662363
ERROR #3:err=2051 tim=97445955
WAIT #3: nam='SQL*Net break/reset to client' ela= 3 p1=1650815232 p2=1 p3=0
WAIT #3: nam='SQL*Net break/reset to client' ela= 180 p1=1650815232 p2=0
p3=0
WAIT #3: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 2155 p1=1650815232 p2=1 p3=0
QUERY
BEGIN BIB_OTP_PKG.get_token_details('0058020234',:c1,:c2); END;
=====================

QUERY 
 
c1 refcursor; 
c2 refcursor; 

BEGIN BIB_OTP_PKG.get_token_details('123678456',:c1,:c2); END; 
 
I am executing the stored procedure which includes select over DB Link.
Most of the time it works (> 95%) and few times it hangs. 
 
I took 10053 event trace and it says wait event on 'SQL*Net message
from dblink' ,'SQL*Net message to dblink' and 'SQL*Net break/reset to
dblink'. Any idea what is going wrong here? 
 
 
 
/opt/oracle/admin/dnet13/udump/dnet131_ora_21694_bib_otp_pkg_03.trc 
  
*** TRACE DUMP CONTINUED FROM FILE /opt/oracle/admin/dnet13/udump/dnet131_ora
_21694_bib_otp_pkg_02.trc *** 
  
EXEC #3:c=0,e=1147,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=951110760122 
WAIT #3: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0 
WAIT #3: nam='SQL*Net message from client' ela= 496 p1=1650815232 p2=1 p3=0 
QUERY 
alter session set events '10053 trace name context forever, level 12' 
===================== 
PARSING IN CURSOR #3 len=69 dep=0 uid=164 oct=42 lid=164 tim=951110760998 hv
=2800020113 ad='a5e71f18' 
alter session set events '10053 trace name context forever, level 12' 
END OF STMT 
PARSE #3:c=0,e=106,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=951110760994 
BINDS #3: 
EXEC #3:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=951110761210 
WAIT #3: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0 
WAIT #3: nam='SQL*Net message from client' ela= 923 p1=1650815232 p2=1 p3=0 
QUERY 
BEGIN BIB_OTP_PKG.get_token_details('0058020258',:c1,:c2); END; 
===================== 
PARSING IN CURSOR #3 len=65 dep=0 uid=164 oct=47 lid=164 tim=951110762903 hv
=727016698 ad='a5f7d828' 
BEGIN BIB_OTP_PKG.get_token_details('0058020258',:c1,:c2); END; 
END OF STMT 
PARSE #3:c=0,e=306,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=951110762895 
BINDS #3: 
 bind 0: dty=102 mxl=04(04) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size
=16 offset=0 
   bfp=ffffffff7c95fb48 bln=04 avl=04 flg=05 
   value= 
Dump of memory from 0xFFFFFFFF7C95FB48 to 0xFFFFFFFF7C95FB4C 
FFFFFFFF7C95FB40          
        
00000000           &nbsp
;      
[....] 
 bind 1: dty=102 mxl=04(04) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=0
offset=8 
   bfp=ffffffff7c95fb50 bln=04 avl=04 flg=01 
   value= 
Dump of memory from 0xFFFFFFFF7C95FB50 to 0xFFFFFFFF7C95FB54 
FFFFFFFF7C95FB50
00000000           &nbsp
;             
   
[....] 
WAIT #4: nam='SQL*Net message to dblink' ela= 4 p1=1413697536 p2=1 p3=0 
*** 2005-09-27 18:03:19.304 
WAIT #4: nam='SQL*Net message from dblink' ela= 509763902 p1=1413697536 p2=1 p3
=0 
WAIT #4: nam='SQL*Net break/reset to dblink' ela= 2 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net break/reset to dblink' ela= 93 p1=1413697536 p2=0 p3=0 
WAIT #4: nam='single-task message' ela= 94523 p1=0 p2=0 p3=0 
WAIT #4: nam='SQL*Net message to dblink' ela= 3 p1=1413697536 p2=28 p3=0 
WAIT #4: nam='SQL*Net message from dblink' ela= 6925 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message to dblink' ela= 1 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message from dblink' ela= 2284 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message to dblink' ela= 3 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message from dblink' ela= 13798 p1=1413697536 p2=1 p3=0
 
WAIT #4: nam='SQL*Net message to dblink' ela= 2 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message from dblink' ela= 5553 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message to dblink' ela= 1 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message from dblink' ela= 556 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message to dblink' ela= 1 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message from dblink' ela= 2465 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message to dblink' ela= 1 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message from dblink' ela= 335 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message to dblink' ela= 0 p1=1413697536 p2=1 p3=0 
WAIT #4: nam='SQL*Net message from dblink' ela= 767 p1=1413697536 p2=1 p3=0 
 
 
===================== 
PARSE ERROR #4:len=618 dep=1 uid=168 oct=3 lid=168 tim=951620661502 err=2051 
SELECT A.TOKEN_TYPE, B.COLOR, C.LOCATION_NAME, C.HSBC_ENTITY, E.TOKEN_STATUS
_DESC CURRENT_STATUS, H.STATUS_CHANGE_DATE IN_ST 
OCK_DATE, E.TOKEN_STATUS_ID FROM TOKEN_INVENTORY A, TOKEN_ATTRIBUTES B, TOKEN
_DISTRIBUTION_LOCATIONS C, TOKEN_INFORMATION D, 
 TOKEN_STATUS E, TOKEN_INFORMATION H WHERE A.SERIAL_NUMBER = :B1
AND A.TOKEN_ATTRIBUTE_ID = B.TOKEN_ATTRIBUTE_ID AND B.HSBC_ 
LOCATION_ID = C.HSBC_LOCATION_ID AND A.SERIAL_NUMBER = D.SERIAL_NUMBER AND D
.CURRENTFLAG = 'Y' AND D.TOKEN_STATUS_ID = E.TOK 
EN_STATUS_ID AND D.APPLICATION_NAME = E.APPLICATION_NAME AND H.SERIAL_NUMBER =
A.SERIAL_NUMBER AND H.TOKEN_STATUS_ID = 30 
EXEC #3:c=10000,e=509899355,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=4,tim=951620662363
 
ERROR #3:err=2051 tim=97445955 
WAIT #3: nam='SQL*Net break/reset to client' ela= 3 p1=1650815232 p2=1 p3=0 
WAIT #3: nam='SQL*Net break/reset to client' ela= 180 p1=1650815232 p2=0 p3=0
 
WAIT #3: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0 
WAIT #3: nam='SQL*Net message from client' ela= 2155 p1=1650815232 p2=1 p3=0 
QUERY 
BEGIN BIB_OTP_PKG.get_token_details('0058020234',:c1,:c2); END; 
=====================