2

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 9.7.2.5 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.

Improve this question
5
  • 1
    To clarify, the query took almost two hours when initiated from your workstation and 21 seconds when executed on the machine where the instance resides, and you would like to know what is causing the disparity?
    – Mark Roddy
    Commented May 29, 2009 at 23:30
  • 2
    "Transferring the 5GB result set file from the server to my workstation took only 3 seconds, so I don't think network bandwidth is the direct problem." Something doesn't add up here. Even 10GbE (unlikely for an XP workstation) will max out around 1GB/second, which would take 5 seconds. And you're not going to push 1GB/second from anything but a very beefy hardware setup (a single fast disk will usually get around 100MB/sec transfer on the local machine).
    – Christopher Cashell
    Commented May 30, 2009 at 2:14
  • Christopher: you're absolutely correct. The file was 5MB not 5GB. I've corrected the question. Sheesh, you'd think I could tell the difference between MB and GB by now.
    – yukondude
    Commented May 30, 2009 at 16:15
  • Mark: yes, that's the gist of my question.
    – yukondude
    Commented May 30, 2009 at 16:20
  • +1 interesting question - curious to know what it turns out to be
    – DCookie
    Commented May 31, 2009 at 13:25

6 Answers 6

Reset to default
1

Two points to take into account:

  • Have you executed the query first in Toad, and afterwards in python+cx_Oracle ? The first time you execute the query, Oracle needs to parse the query, create an execution plan, and execute the plan: read from disk into memory (buffer cache), execute the joins, etc, ... The second time, Oracle uses the same execution plan (stored in the SGA), and reads from the buffer cache, not disk. It can be a lot less time the second time you execute the same query.

  • Loading 8000 rows/5GB of data (655Kbytes per row!!!) into TOAD, to display them in the GUI, can take a lot of time. With python+cx_Oracle you are not displaying anything, so you are saving a lot of time here.

EDITED: OK, so 8000 rows/5 Mb of data (655 bytes per row) shouldn't be a problem for TOAD to display.

  • Compare the Oracle environment variables in Linux with the HKEY_LOCAL_MACHINE\Software\Oracle registry variables in Windows. Check if the NLS_SORT, NLS_LANG, NLS_... variables have the same value.
Improve this answer
3
  • I did run the Python+cx_Oracle from my Windows XP machine (see 1st paragraph), and it took almost 2 hours too. After I ran the 21 sec query on the server, I tried it again from my workstation, and gave up after 15 minutes, so the query plan doesn't look to be the issue.
    – yukondude
    Commented May 30, 2009 at 16:09
  • Also, I made a mistake and the result set was only 5MB, not 5GB. That reduces the row size to something more normal.
    – yukondude
    Commented May 30, 2009 at 16:19
  • Solved: the SGA buffer cache that you mentioned seems to have been the issue all along. There was a preparation SQL script that I forgot about that populated some tables used by the query. When that script was re-run, the buffer must've been invalidated and the extract took the full 2 hours even on the server. I didn't notice that preparation script when I was testing before. Now I just have to figure out how to speed up a 2-hour query...
    – yukondude
    Commented Jun 2, 2009 at 18:16
2

Trace the execution in oracle and probably you'll see your workstation fetching small chunks of data and adding up latency fast.

The solution may be to fetch results in bulk, like done here:

Resolving Oracle Contention in TOAD; look on toadworld, search for

http://www.toadworld.com/Experts/GuyHarrisonsImprovingOraclePerformance/ResolvingOracleContention/May2008OracleNetworkContention/tabid/374/Default.aspx

(sorry, can't add hyperlinks)

Improve this answer
1

I don't know Oracle that well in particular, but previous experience with the network protocols for other databases has told me that they totally ignore latency problems. In other words, even if there's not an overly large amount of data to transfer, things can still get glacial if you have to make several round trips to the server for each value.

You could test this theory if you added a bit of latency to your network (I know this is possible in Linux, I can only imagine there's some way to do it in Windows) and seeing if it affects the overall runtime significantly.

Another option is that some of the processing may be being done on the client side, which could be transferring much more than 5 GB of intermediate data to get the result. This would probably show up as some fairly notable amount of load on your desktop though, so it's not all that likely.

Improve this answer
1

All signs seem to point to a network bottleneck. What kind of throughput do you get with file transfers between these two computers? Can you try running the same query from another computer, but one closer in the network topology?

The effective transfer rate is 6.2Mbps -- not good at all. This is a shot in the dark, but... are you neglecting to tell us that there is a cable modem between your workstation and the server? :)

Perhaps your workstation is daisy-chained off an IP phone and you are actually connected at 10Mbps?

Sorry if I'm stating the obvious, but you have made no mention of your network configuration in your question.

Improve this answer
1
  • Part of the problem is that I mistakenly wrote 5GB instead of 5MB. Sorry 'bout that. I'm not a network guy, but my workstation is on 100Mbit ethernet about 50m away from the server (no idea what lies between through--probably not a cable modem).
    – yukondude
    Commented May 30, 2009 at 16:18
1

One SELECT statement should be shipped in it's entirety to the server for parsing and execution. It's up to the client to manage how those results are returned. I would bet a lot that the problem lies in how the results are being obtained by your clients. It's been a while since i used TOAD, so I don't know if it's doing bulk fetches of the data but that's one place you can get big improvements in speed, as @slovon notes.

Just for grins, see what traceroute reports from your workstation to the server, and vice-versa.

EDIT: another thing to try is to run your python script on the server, but go thru a TNS connection through the listener on the server. This should give you an idea of what effect the TNS software is having on your query while removing any intervening network issues.

Also, make sure your DNS is healthy (gotta be able to do reverse lookups) and that you're not using DHCP on the server. Underscores in the hostname are a no-no, as well.

Improve this answer
2
  • tracert shows just two hops, less than 1 ms total. DNS is fine. No DHCP. Why are underscores a no-no? We're not using them anyway, but I was curious.
    – yukondude
    Commented Jun 2, 2009 at 17:01
  • It's part of the RFC on the host table: ietf.org/rfc/rfc0952.txt?number=952. Oracle will tell you to fix that before they dig into SR's involving networking.
    – DCookie
    Commented Jun 2, 2009 at 22:40
0

Get a trace with wait time on the sessions, both from TOAD and locally. Then compare the wait events and times, plus the execution plan. It's possible that the plan's different.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .