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
