Extracting data from remote Oracle too slow - looking for suggestions

[Dikki A. Coy]

Oracle database recently moved several states away. Users extracting data back to our Unix environment are struggling with excessive latency issues. We are looking for suggestions to improve network data/packet transfer rate.

All ideas are welcome. Thanks!

Dikki Coy

[Hein van den Heuvel]

To speed up owrk over a high latency connection you must increase the number of elements obtained per trip.

If the application is 'sqlplus' then the solution could be to set the ARRAYSIZE parameter (default 15, try 150?)

It will depend to the client software utilized how to set this parameter. For example, if you use an ODBC layer then you might need to tweak "Fetch Buffer Size"

If you use OLEDB then you may want to try specify "FetchSize"="150"

and so on.

Just Google for +oracle +arraysize +'your client layer(s)'

Sample writeup of the effects:

http://www.dba-oracle.com/oracle_tips_sql_plus_arraysize.htm


Finally for these case it will be more critical then ever to review and/or set up sqlnet.ora.

Check out parameters like TCP.NODELAY and the SDU settings.
Carefully review Oracle docs (like, but not limited to:)

http://download-east.oracle.com/docs/cd/B12037_01/server.101/b10752/network.htm

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[A. Clay Stephenson]

1) Pay for a bigger "pipe".
2) Write more efficient code.
3) If most of the processing is being done by the remote clients then change you model so that the processing is done on the far end of the connection and only the results are transmitted back. If your clients are PC's then you might consider putting a Citrix server on the far end of the connection and then all the transactions (other than keystrokes, screen updates, and print jobs) will be "local" with respect to your database server.

Sadly, yours are the kinds of questions that should have been asked BEFORE the move was made.

[Steven E. Protter]

Shaom,

It's the pipe most likely.

If your network is running 100% then you know the answer is the pipe. You can check that.

A. Clay's second suggestion, more efficient sql code can actually have the same impact. On this item is the control of how much data goes into the pipe.

SEP

[Steven E. Protter]

Another idea occurs just after hitting submit.

Use Oracle data replication to maintain a local copy of the database. Dataguard works with archive log files, there are other tools depending on write needs and such that can speed things up in this situation.

If you can't move the data more efficiently through the pipe (you should), or less data in the pipe (you should), then move the data closer to you. You can sell it to management as a good DR strategy.

SEP

[Hein van den Heuvel]

>> If your network is running 100% then you know the answer is the pipe. You can check that.


I beg to differ. If the problem is latency, then the network pipe will appear rather unused whilest still being the problem.

Its simple really... that next speed of light restiction (and light goes even slower in glass). Several states = several hundreds of miles (except in newEngland ME - NH - MA = 20M and MA - RI - CO = 20M).
Let's call is.

300,000km/sec = 300km/mS = approx 200 Mile per millisecond. So if you need 1000 miles roundtrip, then that is 5ms/trip. If you wait for each message to be acked, then you can get only 500 messages/second... which is at the max, but hardly tickling the interface. So you'd better make good yuse of those packets: Make 'm big; Avoid transferring all columns; stick multiple rows in a packet; Make it a train of packets; don't wait for the first package to arrive before sending the next.


Cheers,
Hein.

[Dikki A. Coy]

Thanks for your suggestions. Yes - it would have been great if "management" had considered the nature of the work being done before making the decision to move servers into a data center. The subject of citrix servers has been brought up and is still being kicked around. Certainly a bigger pipe would be nice.

We had started researching SDU settings - and will continue in the other areas you mentioned as well. Thanks for the links to documentation.

I will make sure to post our findings and may likely have additional questions. Thanks again for taking the time out to help!

Dikki

[Yogeeraj_1]

hi,

You may also look into the applications. For example, when using Forms, you can define the parameters:
. Number of Records buffered,
. Number of Records Displayed and
. Query all Records

Basically, it would mean review of applications which might not be feasible.

For the rest you must analyse the bandwidth utilised (LAN/WAN) and identify the real bottleneck...

hope this helps too!

kind regards
yogeeraj

[Dikki A. Coy]

Just wanted to thank you all again for your help and suggestions.

We have the network guys looking at the pipe and any bottlenecks there may be. Plus they are testing WAN accelerators, which I suspect will be more help for repetitive file/data transfers.

We use SAS software and do pass-thru queries to Oracle. We added the "buffsize=1000" option in the connection string, and found the query time dropped by half. We found we had to experiment with the number a little to get the best results - and it may need tweaked depending on the query.

For straight sql queries - setting the arraysize = 1000 gave us much better results also.

Thanks again!

Dikki