Re: "sql*net message from client" hang

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]]
>> 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
>>