wan/lan oracle performance tuning

[brasinhab]

I'm a newbie in oracle, but we are using a PDM application that was
not developed by us, and trying to determine why the wan performance
is poor, taking into account our wan speed.
The difference in regular file transfer speed between lan and wan is
about 3X (that is a 100mb X 32mb). On the other hand the application
is about 9-10 times (!) slower on remote (wan) clients. We already
know that the quality/quantity of sql is not optimized but we can't
really modify the application. The response delay is primarily due to
small sized sql transactions, but in large quantity.

BTW, The latency is 60ms. And servers/clients are AIX.

Is there any oracle/network parameters I could investigate to improve
this performance? We are currently getting test results on
tcp.nodelay=true. Any advice would be welcome. Thanks,

Hetty.

[Daniel Morgan]

brasinhab wrote:

I would start by going to oracle home bin on the client machines and
running tnsping and winnt\system32 and running ping. My guess is that the
problem is not your network or your connection. Rather a poorly
configured database.

And if that guess is correct you need to bring in a consultant to tune
your database and mentor your current DBA(s).
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)

[Antoine BRUNEL]

This is a typical problem of scalability. As you saw, bandwith is not the
most limiting parameter, but latency is.

As you are make lot of roundtrips between server and client, latency is the
contention. If your application is really making small SQL statements, then
you won't be able to change anything without changing the code.

A good solution, for you WAN clients, would be to use stored PLSQL, or even
anonymous PLSQL blocks...

"Daniel Morgan" <damo...@exxesolutions.com> a écrit dans le message de
news:3EBACC6E...@exxesolutions.com...

[brasinhab]

Thank you all for the insightful advices.

"Antoine BRUNEL" <antoinebrunel/yahoo.fr> wrote in message
news:3ebaf752$0$28760$79c1...@nan-newsreader-03.noos.net...

[Sybrand Bakker]

"brasinhab" <bras...@yahoo.com> wrote in message
news:a4a13415.03050...@posting.google.com...

In my experience the network almost never is an issue, and you would
probably get better results by addressing the root cause and suing the
vendor.
I once reduced the bandwith of a network connection by one third simply by
configuring Powerbuilder to keep cursors open.
You should look into tuning the SDU and the TDU in tnsnames.ora and
listener.ora. Oracle's default SDU is 2048, the default ethernet MTU 1500.
Oracle's default TDU is 32767, the default ethernet somewhere in the 8000s
(check Metalink on this one)
You should use ipdump and ipreport on AIX to check whether the Net8 packets
are getting fragmented.

All in all, however, I know you are just wasting your time. If the app
doesn't support array fetches you will get small roundtrips anyway, and in
that case you can change SDU and TDU to whatever you want, it is going to
buy you anything.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address