Solved

How to improve oracle wan performance

Posted on 2009-05-11
11
1,162 Views
Last Modified: 2013-12-18
Currently we have an application built on oracle forms with sql*plus.

We are trying to use it over a vpn from a branch office and it is very slow, taking 5 minutes to just load up. File transfers between both offices are able to go at around 500KB/ps with a latency of around 200ms.

When we try the oracle forms program it is exceptionally slow. taking atleast 10 minutes to do anything. the average transfer rate when the program is open is also around 5KB/ps.

How can i improve the performance of this from the branch office? Would it be better to put another database here and synch them?

So far i have tried chaning the SDU to 32K and the TDU.

I posted a similar question here: http://www.experts-exchange.com/Database/Oracle/10.x/Q_24383387.html

where it was suggested we change to an odbc driver but unfortunately this isnt an option.

Thanks for youyr help.
0
Comment
Question by:cam-e
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 24360872
Even if odbc was an option I wouldn't recommend it, the odbc simply adds an extra layer of abstraction and loses features.

However to answer your question...

Yes, you can create a local db and synch it.  There are various ways to do this and depending on your needs some are better than others.

Alternatively examine the code in your forms.  Are there lots of actions going back and forth across the network that could be combined into a single call to server side pl/sql?
Making these types of changes would entail rearchitecting your application but they could be much faster.

I'm also not sure what you mean by "forms with sql*plus"  those are two different products and aren't invoked by each other (unless you go out of your way to explicitly do so)
0
 

Author Comment

by:cam-e
ID: 24360894
The application is one we purchased of a third party so unfortunately i cant look at any of the code and i am not very familiar with oracle and i was under the impression it uses oracle forms runtime with sql plus.

Are there any other ways to improve performance? 5 minutes to load up seems to be very long. Or, is synching 2 databases going to be the best option here?
0
 

Author Comment

by:cam-e
ID: 24361011
I have made a video to demonstrate the kind of speed i am getting.

the link is here if any one is interested.. http://rapidshare.com/files/231959997/video.wmv.html
0
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
LVL 74

Expert Comment

by:sdstuber
ID: 24361046
You'll probably have to talk to your vendor then.
If this is Oracle's CRM product, then contact Oracle support and open a TAR.

Somethings to look at.
Are the forms stored in the database?  If so, you may be getting lag because you have to extract the forms first before you get to run them.

Next, see if you can trace the actions from database side (ask your dba to do this for you) and see what the connection is doing.
Are you waiting on database activity or is i solely the network traffic or a combination of both?
0
 

Author Comment

by:cam-e
ID: 24361246
Here is a trace file from the client.
sqlnet.trc---Copy.txt
0
 

Author Comment

by:cam-e
ID: 24361260
Also, there is no database activity at the moment and network traffic is very minimal.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24363395
sorry I meant a server side trace to capture the db activity, not sqlnet trace.

however, if you've already ruled out both the db and the network then it's the client side forms themselves simply taking too long to render.
definitely talk to the vendor about getting the forms fixed.
0
 

Author Comment

by:cam-e
ID: 24371403
I will definately try and talk to them I have attached the server side trace... there was a few files so i have zipped them. thank you for your help with this!!

Do you think it is poor programming slowing down this thing so much or other factors?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24371422
if it's not the database and it's not the network that only leaves 3 options.

1- poor programming
2- very slow/over worked computer
3- your initial assessments that it's not the db or network is incorrect
0
 

Author Comment

by:cam-e
ID: 24371434
Whoops... didnt like that file, here we go..

thanks.
trace-files.zip
0
 

Author Comment

by:cam-e
ID: 24371499
Also,

The connection between the two sites are 100mbps fibre and a 10mbps fibre. they are located 9000miles apart though... latency is 180ms and i am able to get around 500KB/ps. The computer running the DB is Server 2003 R3 x64 with dual 3ghz processors and 4gb of ram. the database never has any more then 4 users and this computer does not run anything else.

hope that helps.
0

Featured Post

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: You must have administrative privileges in order to create/edit Roles. Salesforce.com (http://www.salesforce.com/) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales and marke…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question