Queries hang when accessing Oracle via ODBC and Python

Question

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.

Answer 1

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!

Answer 2

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;