Re: wan/lan oracle performance tuning

"brasinhab" <brasinhab_at_yahoo.com> wrote in message news:a4a13415.0305081116.44a7098d_at_posting.google.com...
> 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.

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