Hello,
Apologies, that was DBI 1.607, not .67!
with RowCacheSize '0', it hangs on the same block, with '1', it seems to
go through ok as with '-1'.
As far as changes go, that is part of the mystery, haven't been able to
find any changes around the time when this started to happen. Yes, we are
running DBI 1.607, DBD::Oracle 1.19, Oracle Client 10.2.0.1.0, all pretty
old, but I have confirmed that nothing changed in them anywhere, all the
timestamps are years old, and the issue happens across all of our
application servers. General hunch is that it is something with the
network, but I have tried it on boxes with no firewall, where a traceroute
to the DB server has no hops at all, and the issue still occurs.
Thanks,
Dan
On Thu, Jul 8, 2010 at 7:24 PM, John Scoles <sco...@pythian.com> wrote:
> The $dbh->{RowCacheSize} = -1; is funny as you are telling the box to only
> use 1 byte for RowCach
> Try it with '0' and see what happens?
>
>
>
> On Thu, Jul 8, 2010 at 7:20 PM, John Scoles <sco...@pythian.com> wrote:
>
>> Hmm DBI 0.67 that is old and the fact you get the same error with the
>> latest DBI and DBD::Oracle would lead me to think there may be something
>> wrong internally in your DB or its storage medium, corrupt block perhaps or
>> bad sector??
>>
>> The code differences between 1.61 and 0.67 are vast as would be the
>> changes in DBD::Oracle.
>>
>> Did you change your Oracle client lately or anything else with the DB or
>> network?
>>
>> Would lke to know the version of DBD:Oracle you are using with DBI 0.67
>> and what version of the ORACLE client you where using and what version of
>> the DB you are trying to select from
>>
>> that will help narrow things down a bit
>>
>> cheers
>> John Scoles
>>
>>
>> On Thu, Jul 8, 2010 at 5:28 PM, Dan <dkele...@gmail.com> wrote:
>>
>>> 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
>>> >
>>> >
>>> >
>>> >
>>>
>>
>>
> --
> New! Learn why & how to love your data with Pythian's new webinar series.
> Topics, details & register: http://www.pythian.com/webinars
>
>
