How to improve oracle wan performance

Posted on 2009-05-11
Medium Priority
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.
Question by:cam-e
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
LVL 74

Accepted Solution

sdstuber earned 1000 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.. http://rapidshare.com/files/231959997/video.wmv.html
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

LVL 74

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.

Author Comment

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

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 74

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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. Salesforce.com (http://www.Salesforce.com) is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
Salesforce.com is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

743 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