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 <martin.h...@oracle.com> 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:dkele...@gmail.com <dkele...@gmail.com>] > Sent: Thursday, July 08, 2010 10:02 AM > To: dbi-users@perl.org > 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 > > > >
