Re: "sql*net message from client" hang

Hello,
  We do not have any quotas implemented on our Oracle instance.  The simple
select works fine for that hanging block on sqlplus, or if i only execute it
for that block in the perl script instead of iterating through them all. I
just tested running for that same block 185 times instead of all 185 blocks,
and it seems to hang on the second iteration (three times in a row so far)

Thanks,
Dan

On Thu, Jul 8, 2010 at 12:29 PM, Martin Hall <[email protected]> wrote:

> there are various quotas in Oracle that could stop a query.  However, they
> do tend to kill the guilty session rather than just hang like that.  If your
> simple select ran multiple times for the same 10k block, would it still
> hang?
>
> Cheers
>
> Martin
>
>
> On 08/07/2010 17:07, Howard, Chris wrote:
>
> 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] <[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
>
>
>
>