Oracle hangs on select statement on certain computers

I'm not really sure where to post this as I'm not really sure where the problem lies, but I THINK it may be an oracle problem:

I have a problem with certain select statements hanging (either retrieving several hundred rows than just stopping on the same row every time, or just hanging before retrieving anything.) These exact statements do work on most of our computers and up until a few weeks ago ran fine on the few that now hang. And even on the computers that have the problem, it's only 3 particular databases (2 of which are on the same server but all of which belong to the same client and are accessed via VPN.) This problem is consistantly reproducable on the same computers with the same statements with the same databases. The statements are being run through an application that we created in PowerBuilder and I have also tried them in a database utility that we also created in PowerBuilder, SQL Plus, & TOAD for Oracle. On the computers that have the problem, the statements freeze in both our PowerBuilder apps, and in a version of TOAD (I forget what) prior to 8.6.0.38; they work in SQL Plus (which I'm told DOESN'T use the oracle API) and TOAD 8.6.0.38. We have recently upgraded TOAD on all machines which is why I'm not sure what the version was that was failing, but in the previous version the statements DID return fine when run as single statements (which retrieves a few rows into a table and retrieves the rest when you scroll down); they just hung when executing as a script
(which retrieves everything at once.)

Not everyone has the exact same versions of everything (particularly Oracle and Windows-we all use XP Pro but there still are version differences..) or the exact same programs installed. I have tried two computers with the same Oracle version though and one worked while one did not. One strange thing about this problem is that the 3 computers that have the problem (there are 3 that I know of and they all started having the problem a few weeks ago) are the 3 that access the particular databases the most; this may or may not be coincidence. As I mentioned, the databases are accessed by us over VPN but the client
(who accesses them directly) reported to us the other day a problem
that they had in which it hangs when the app runs certain insert or delete statements. We are aware that locks on the tables could cause these problems but we tried and the client tried when everything was unlocked and they hung, locking the tables, on all their databases while only two of the three hung for us.

All three of their servers are at oracle 9.2.0.6.0; I just now thought of asking if this upgrade was made recently; if so, I'll post that info in here tomorrow.

Oh, and by the way, not only does the structure (ie. columns/tables/etc used) affect which statements hang, but the data used in them (ie. "where flag='x'" vs. "where flag=' '"), which doesn't change the execution plan, is a factor.

So does anyone have any suggestions?

Thanks, Dan Received on Mon Feb 20 2006 - 16:35:12 CST