RE: "sql*net message from client" hang

If it always stops at the same place, it makes me think
of a resource problem, something like a quota?

I don't remember of there are select quotas.

Does it do the same if run as sysdba or some other
well-endowed database user?



> -----Original Message-----
> From: Dan [mailto:[email protected]]
> Sent: Thursday, July 08, 2010 10:02 AM
> To: [email protected]
> Subject: "sql*net message from client" hang
> 
> Hello,
>   I have some code that has been running happily for many years, but
> suddenly started hanging on a simple select query to our Oracle DB.
> The DB
> shows it as an inactive session with "SQL*NET message from client", so
> the
> DB thinks it is waiting for the perl script, but the perl script is
> hung
> reading from the DB according to truss.  I boiled it down to a 20 line
> test
> script that prepares a query using DBI and then executes it ~190 times
> for
> different 10k blocks of the table.  It seems to consistently hang on
> the
> 97th iteration, and times out after a few hours hung in "SQL*NET
> message
> from client". Has anyone ever come across an issue like this? Even
> stranger,
> when I inserted "$dbh->{RowCacheSize} = -1;" , it seems to complete
and
> never hang... Our hunch is that this is some issue with the network,
> but
> don't have the expertise to pinpoint what would cause this strange
> behaviour
> 
> We are running old DBI .67, but i tried the same script with a local
> version
> of the latest and greatest DBI and DBD::Oracle with the same results.
> Here
> is a sample output from setting $dbh->trace(15); :
> 
> 96 : 1793727
>     -> execute for DBD::Oracle::st (DBI::st=HASH(0x40ad7c)~0x40ad4c
> '1793727') thr#12d600
>        bind :p1 <== '1793727' (type 0)
>        rebinding :p1 (not-utf8, ftype 1, csid 0, csform 0, inout 0)
>        bind :p1 <== '1793727' (size 7/8/0, ptype 4, otype 1)
>        bind :p1 <== '1793727' (size 7/7, otype 1, indp 0, at_exec 1)
> 
>
OCIBindByName(3960a0,404734,38f52c,":p1",3,3a8438,7,1,40474c,0,404744,0
> ,0,2)=SUCCESS
> 
> OCIBindDynamic(395e34,38f52c,404710,fefec350,404710,fefec644)=SUCCESS
>        bind :p1 <== '1793727' (in, not-utf8, csid 31->0->31, ftype 1,
> csform
> 0->0, maxlen 7, maxdata_size 0)
>         OCIAttrSet(395e34,OCI_HTYPE_BIND,ffbff290,0,31,38f52c)=SUCCESS
>     dbd_st_execute SELECT (out0, lob0)...
>        in  ':p1' [0,0]: len  7, ind 0
>         OCIStmtExecute(38f4b8,3960a0,38f52c,0,0,0,0,0)=SUCCESS
>         OCIAttrGet(3960a0,OCI_HTYPE_STMT,ffbff40a,0,10,38f52c)=SUCCESS
>     dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
>     <- execute= '0E0' at simple.pl line 18
> 97 : 1793738
>     -> execute for DBD::Oracle::st (DBI::st=HASH(0x40ad7c)~0x40ad4c
> '1793738') thr#12d600
>        bind :p1 <== '1793738' (type 0)
>        rebinding :p1 (not-utf8, ftype 1, csid 0, csform 0, inout 0)
>        bind :p1 <== '1793738' (size 7/8/0, ptype 4, otype 1)
>        bind :p1 <== '1793738' (size 7/7, otype 1, indp 0, at_exec 1)
> 
>
OCIBindByName(3960a0,404734,38f52c,":p1",3,3a8438,7,1,40474c,0,404744,0
> ,0,2)=SUCCESS
> 
> OCIBindDynamic(395e34,38f52c,404710,fefec350,404710,fefec644)=SUCCESS
>        bind :p1 <== '1793738' (in, not-utf8, csid 31->0->31, ftype 1,
> csform
> 0->0, maxlen 7, maxdata_size 0)
>         OCIAttrSet(395e34,OCI_HTYPE_BIND,ffbff290,0,31,38f52c)=SUCCESS
>     dbd_st_execute SELECT (out0, lob0)...
>        in  ':p1' [0,0]: len  7, ind 0
> ...
> ### HANGS forever here :(
> ...
> 
> Any help is much appreciated!
> 
> Thanks,
> Dan