- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Extracting data from remote Oracle too slow - look...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-04-2007 03:03 AM
тАО05-04-2007 03:03 AM
Extracting data from remote Oracle too slow - looking for suggestions
All ideas are welcome. Thanks!
Dikki Coy
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-04-2007 03:30 AM
тАО05-04-2007 03:30 AM
Re: Extracting data from remote Oracle too slow - looking for suggestions
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-04-2007 03:30 AM
тАО05-04-2007 03:30 AM
Re: Extracting data from remote Oracle too slow - looking for suggestions
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-04-2007 03:33 AM
тАО05-04-2007 03:33 AM
Re: Extracting data from remote Oracle too slow - looking for suggestions
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
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-04-2007 03:35 AM
тАО05-04-2007 03:35 AM
Re: Extracting data from remote Oracle too slow - looking for suggestions
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
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-04-2007 03:54 AM
тАО05-04-2007 03:54 AM
Re: Extracting data from remote Oracle too slow - looking for suggestions
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-04-2007 05:48 AM
тАО05-04-2007 05:48 AM
Re: Extracting data from remote Oracle too slow - looking for suggestions
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-04-2007 05:20 PM
тАО05-04-2007 05:20 PM
Re: Extracting data from remote Oracle too slow - looking for suggestions
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-17-2007 01:57 AM
тАО05-17-2007 01:57 AM
Re: Extracting data from remote Oracle too slow - looking for 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