I recently wrote a complex SELECT statement (based on many very, very complex views) that would take 1 hour and 50 minutes to execute when run from Toad on my desktop, and only a few minutes faster when run from a Python script using the cx_Oracle library (written to flush to disk every 50 rows). The total size of the single result set was about 8000 rows totalling 5MB. During execution, my workstation was not thrashing and the CPU load was reasonably low.
The exact same query ran on the server took an astonishing 21 seconds to produce a byte-for-byte identical result set. This was also generated by the same Python/cx_Oracle script.
Transferring the 5MB result set file from the server to my workstation took only 3 seconds, so I don't think network bandwidth is the direct problem.
Could SQL*Net or one of its associated libraries be the culprit? Is there some non-linear memory management problems when queries are invoked across the network? A 5MB result set is large, but not ginormous in this day and age. Are there perhaps some buffer size configuration settings that would help? I'm using a vanilla Oracle client install.
The workstation is Windows XP Pro SP3 (only 1GB of RAM) with the Oracle 10g client and Toad for Oracle Xpert 126.96.36.199 and Python 2.6.2 with cx_Oracle 5.0.2. The server is Red Hat 2.6.9-67.ELsmp on a quad Xeon 3.8GHz 8GB server, running Oracle 10.2.0.4, Python 2.3.4, cx_Oracle 4.4.1.
Edit: Whoops! The file was only 5 Megabytes, not GB. Very sorry.
Solved: There was a population script that ran before the extract query that I mentioned. Once that population script was re-run, the extract query took 2 hours to run regardless of the location of the client program. After that first long run, the result set must've been cached somewhere, and I didn't notice that effect until I methodically went through all of the combinations.