RE: Select statement hangs over DBlink (Intermittent)
- From: "Reidy, Ron" <Ron.Reidy@xxxxxxxxxxxxxxxxxx>
- To: <dba.orcl@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Sep 2005 17:11:15 -0600
Network problems?
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
Other related posts:
