[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

656 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