How to improve oracle wan performance

Posted on 2009-05-11
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:

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

Thanks for youyr help.
Question by:cam-e
  • 7
  • 4
LVL 73

Accepted Solution

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)

Author Comment

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?

Author Comment

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..
LVL 73

Expert Comment

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?

Author Comment

ID: 24361246
Here is a trace file from the client.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

ID: 24361260
Also, there is no database activity at the moment and network traffic is very minimal.
LVL 73

Expert Comment

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.

Author Comment

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?
LVL 73

Expert Comment

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

Author Comment

ID: 24371434
Whoops... didnt like that file, here we go..


Author Comment

ID: 24371499

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.

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

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 configure lead or case queues. ( is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
Read about achieving the basic levels of HRIS security in the workplace.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

932 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now