Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have Python scripts running on a Windows 2003 server that access an Oracle 11g database in the US via VPN. I am in the UK. We have recently moved offices and upgraded our broadband speed. Some of these scripts hang when accessing Oracle views now, whereas they didn't before. I have isolated two queries, one of which hangs, and one which doesn't, and the only difference between the two is that the one that hangs pulls out one more column from the database. I don't know the underlying structure of the view, but from what I know of the database the column that "causes" the hang is from a different table to the ones that come out OK, and so the underlying query is doing a join when it hangs.

I'm guessing the problem is related to timeouts somewhere along the way. I've tried changing the ODBC settings for the Oracle driver, e.g. unticking the "query timeout" option, but nothing changes.

How can I prevent these queries hanging?

Another bit of info: when I run the same two queries on a Linux box based in the US, using the isql utility they both complete in the same time - 43 seconds.

The Python ODBC library I'm using is the one from Egenix.

Update: using Wireshark I've been following the packets that are exchanged. When I use the query that causes a hang I get a response from the server, and the first row of data within half a second, and then nothing ever again. When I use the query that doesn't hang it takes 40 seconds to get the first row, and then returns all the others.

share|improve this question
Sounds like some kind of networking or firewall issue. You probably need to get your network admin to help diagnose it. Listener tracing server-side may also help, along with netstat etc – Phil Nov 8 '13 at 11:48
what kind of data is in the 'extra' column? Can you use explain plan to find how the 'extra' column is accessed? Is the join using an index? – ik_zelf Nov 8 '13 at 13:16
@ik_zelf The data in the extra column is text. I don't know the answers to the other two questions, so I'll contact the DBAs. – paulmorriss Nov 8 '13 at 13:46
is the explain with the extra column different than without the extra column? – ik_zelf Nov 11 '13 at 20:23
possible duplicate of…. Also some SSL VPNs do not like TCP packets having OOB bit set. These get either merged into TCP stream or discarded. You can also use DISABLE_OOB. – ibre5041 Mar 9 '15 at 14:20

I had a very similar problem where I had a query that would run, return 541 rows (every time) and then just hang. Wireshark looked like I was sending a packet and just not getting any response.

While troubleshooting the issue I added an order by clause to the query trying to see if I could find a potentially problematic record, but then the query executed just fine.

I don't know why the order by clause fixed it and I don't care too much to find out. Hopefully this simple solution will work for others too!

share|improve this answer

I never got to the bottom of this. The dbas are in the US, so it would have taken a few days of back and forward to try and work out why efficient queries hung (if we'd been able to work it out at all). In the end I got the data in chunks of 100 rows using the technique described here

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
  a.*, ROWNUM rnum 
      from ( your_query_goes_here, 
      with order by ) a 
      where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.