Take the 2-minute tour ×
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.

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 –  FreshPhilOfSO 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
add comment

1 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
add comment

Your Answer

 
discard

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.