Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Select statement hangs over DBlink (Intermittent)

RE: Select statement hangs over DBlink (Intermittent)

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Tue, 27 Sep 2005 17:11:15 -0600
Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B87350B@fiji.arraybp.com>


Network problems?  

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sami Seerangan
Sent: Tuesday, September 27, 2005 4:42 PM
To: oracle-l
Subject: Select statement hangs over DBlink (Intermittent)


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=9516206623 63 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;
=====================
This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system. -- http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 27 2005 - 18:15:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US