Re: fetch size too big causes hang
Date: Thu, 26 Mar 2009 13:38:51 -0700 (PDT)
Message-ID: <cfbd282b-876d-44ff-9ace-9c25bfd3eb8f_at_v1g2000prd.googlegroups.com>
On Mar 26, 11:27 am, Jim <j..._at_trainplayer.com> wrote:
> I once had a good connection from my machine to our Oracle server, but
> something has gone wrong lately apparently with transferring packets
> over the network. Here's the symptom:
>
> I connect in SQLPlus and do a large select. Records scroll by. I
> then fool with SET ARRAYSIZE and do the same select. When I set the
> value to 400 or below, no problem. If I set it to 450 or above, it
> hangs and never comes back.
>
> Another symptom: I have an app which connects via ADO. I can set
> FETCHSIZE in the connect string to 100 with no problem, but if I set
> it to 500 or above, it hangs. (The default value is 1000, which works
> fine for everyone else.)
>
> This is after a clean new install of the 10g client. I don't have any
> other network speed or connection issues. The problem has been going
> on for months now, and as I say, it used to work fine from this
> machine (no, I don't exactly know when it started or what I happened
> to be doing that day). I am finally so desperate as to ask for help
> from you fine folks. Please suggest something I might try.
>
> -- Jim
My first thought is "memory problem" but I would think you would see something besides a hang. First, check your sqlnet.ora file (and be sure you are using the one you think you are) for anything strange. Next use tracing to see if it tells anything - see the Net Services Administrator's Guide.
tcp.nodelay is a parameter that defaults to yes, if for some reason it is set to no you might suffer from delays in sending the last tcp package. SDU is a parameter sometimes set for performance reasons (to cram more data into fewer packets), a wild guess that if this doesn't match everywhere it might do something unpredictable, rather than the documented "use the smallest setting."
Look in the registry for old Oracle home information, might be strangeness left over from multiple installs.
jg
-- _at_home.com is bogus. http://broadcast.oreilly.com/2009/03/brian-aker-what-would-an-ibm-b.htmlReceived on Thu Mar 26 2009 - 15:38:51 CDT