How to improve oracle wan performance

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.
Who is Participating?
sdstuberConnect With a Mentor Commented:
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)
cam-eAuthor Commented:
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?
cam-eAuthor Commented:
I have made a video to demonstrate the kind of speed i am getting.

the link is here if any one is interested..
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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?
cam-eAuthor Commented:
Here is a trace file from the client.
cam-eAuthor Commented:
Also, there is no database activity at the moment and network traffic is very minimal.
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.
cam-eAuthor Commented:
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?
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
cam-eAuthor Commented:
Whoops... didnt like that file, here we go..

cam-eAuthor Commented:

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.