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.

  • 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ᵀᴹ
    Commented Nov 8, 2013 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?
    – user953
    Commented Nov 8, 2013 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. Commented Nov 8, 2013 at 13:46
  • is the explain with the extra column different than without the extra column?
    – user953
    Commented Nov 11, 2013 at 20:23
  • possible duplicate of dba.stackexchange.com/questions/59200/…. 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
    Commented Mar 9, 2015 at 14:20

2 Answers 2


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!


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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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