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

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

Select statement hangs over DBlink (Intermittent)

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: Tue, 27 Sep 2005 15:41:43 -0700
Message-ID: <f09dd62805092715416bae301d@mail.gmail.com> 





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





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;




--
http://www.freelists.org/webpage/oracle-l


Received on Tue Sep 27 2005 - 17:44:17 CDT












Original text of this message













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