Re: OCI Query hangs after approx 30min, but not at 28min

On Feb 28, 2:22 pm, "joel garry" <joel-ga..._at_home.com> wrote:
> On Feb 28, 9:07 am, dale5..._at_gmail.com wrote:
>
>
>
>
>
> > On Feb 27, 6:29 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
>
> > > On Feb 27, 6:35 pm, dale5..._at_gmail.com wrote:
>
> > > > Hi,
>
> > > > We have been tracking down this issue for a few weeks now and recently
> > > > recreated it outside of our application using only sqlplus. To
> > > > reproduce the problem we run the following script from sqlplus on a
> > > > Win2003 client against a 10g Oracle 64bit Server.
>
> > > > The sqlplus command never returns and appears to be simply waiting for
> > > > a response. An OCI trace gives no more information either, just
> > > > appears to be waiting for a response. There are no errors reported on
> > > > the client or server.
>
> > > > The problem is that the results are not consistent. When run from the
> > > > same client against a diff DBs sometimes it works fine. Different
> > > > client to the same server also sometimes works. Its not that sometimes
> > > > it works sometimes it doesnt. For a given client/server combination we
> > > > do see consistent results, that is it either always hangs after 32mins
> > > > or always returns.
>
> > > > The problem has not yet been seen against a 9i Server. This is also
> > > > only happening at our Clients environment, we havent been able to
> > > > reproduce the issue in house.
>
> > > > Any ideas? Help!!
>
> > > > DECLARE
> > > > sleepMins NUMBER;
> > > > BEGIN
> > > > sleepMins := 32;
> > > > dbms_output.put_line('About to Sleep for: ' || sleepMins ||
> > > > 'mins');
> > > > FOR idx IN 1 .. sleepMins LOOP
> > > > dbms_output.put_line('Sleep iteration: ' || idx);
> > > > dbms_lock.sleep(60);
> > > > END LOOP;
> > > > dbms_output.put_line('Finished');
> > > > END;
> > > > /
>
> > > is database behind a firewall? If yes then you might want to check
> > > firewall timeout settings
>
> > >http://download-east.oracle.com/docs/cd/B19306_01/java.102/b14355/apx...
>
> > > Anurag- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thanks but I dont think this is a firewall problem based on some
> > recent results. From the same client (running either 9i or 10g)
> > against the same DB Server, but 2 seperate instances we get 1 test
> > hanging and the other returning fine. To summarize
>
> > >From some Clients (9i or 10g) to DBServer Host running 10g instance:
> > Hangs consistently
> > >From same Clients (9i or 10g) to same DBServer Host running another 9i
>
> > instance: Returns consistently
>
> > Could this be a Windows client problem, from the limited data it seems
> > to be Win200x clients that have the problem. WinXP seems to work fine
> > all the time.
>
> > thanks again
>
> Maybe there's just something fundamentally wrong with
> dbms_lock.sleep. See bugs 1842189 and (your exact problem) 2222425
> (and always remember to be extremely detailed on client and server
> oracle versions and OS versions).
>
> jg
> --
> @home.com is bogus.
> "We're about halfway there." - Kevin Rooney, San Diego police
> lieutenant, who was asked how much of a dismembered body had been
> recovered after a head and hand were found yesterday, a day after the
> body was found in Palm City- Hide quoted text -
>
> - Show quoted text -

I spoke too soon regarding the firewall, the client recently installed a firewall between some of the boxes, which was news to the IT guys and yes it had an idle timeout value of 30mins.

I'm still puzzled as to why 1 instance worked and another instance didnt. I've asked them to double-check the EXPIRE_TIME to see if it is different between the 2.

thanks everyone.
Dale Received on Fri Mar 02 2007 - 08:42:20 CST