RE: The problem is that SQL*Net is too chatty, because FTP runs fine.
Date: Thu, 31 Jan 2008 16:16:07 -0500
Is there some reason that you think the amount of data across the network will be the same for the US-SW to US-EC refresh as for the US-SE to US-EC refresh? I guess I am getting at precisely what do you mean when you write "That same refresh...?" Are duplicate threads of changes being made at US-SW and US-SE so when the refresh is made data is identical? Are they both complete refreshes?
For the sanity of the network folks, I guess I'd use a fixed set of onesy row inserts from both remote locations, probably hosted right on the database server so you don't have to worry about some LAN overload from a workstation (oh - do the DB servers have private WAN connections, or are they at the mercy of the LAN?). Anyway, if you give them an apples to apples comparison they can repetitively drive, then I bet the two WAN groups could be convinced to educate each other by any discrepancies they see when someone clicks enter at each of the remote sites. Then it becomes, okay, SQL*Net may be too chatty, but why is it less horrible on one link than the other. If you can give them a nice repeatable load they can probably be inspired to work it out amongst themselves and they can come back to you with how they have managed to work around the problem. You might even leave the insert burst script around or put it into cron if you can tolerate the periodic excess load so the network guys can check whether their sqlnet throughput varies in fact when the network statistics make them think it should not. Network guys usually like indisputable facts. (Me too!) Tracking that over time might give you a nice picture of evaporating headroom versus your service levels, but that is another story for a different day.
And of course it is entirely possible in a queue situation to have a large chunk transmitter like FTP work okay while a small chunk, chatty tranmitter like a materialized view refresh gets differentially slower and slower. Think of an up escalator feeding a slide. One person per step, no matter how big (or small). Even though the occupation of the slide is low and the large persons move many pounds down the slide relatively quickly and the itsy bitsy children get just as many turns on the up escalator, the little kids move pounds down the slide very slowly.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of David Taft
Sent: Thursday, January 31, 2008 12:15 PM To: oracle-l
Subject: The problem is that SQL*Net is too chatty, because FTP runs fine.
Platform: Oracle 10gR2 on AIX 5L
We have a materialized view refresh that takes just ten minutes to complete between a database in the US-southwest and one on the east coast. That same refresh between a database in the southeast and the east coast database takes 3 hours to complete. All settings like SDU, SEND_BUF_SIZE and REC_BUF_SIZE have been verified as being set the same and the network group tells us that we are only using half the bandwidth on that southeast to east WAN pipe, so our next thought was that the problem is network latency between the southeast and east coast data centers. However, the group managing that WAN connection claims the problem is that the application (i.e. SQL*Net) is too chatty because FTP works fine. Yes, SQL*Net is chatty. It has always been chatty and always will be chatty. Comparing FTP to SQL*Net chattiness seems an unfair comparison, but I find that some network engineers like to say that "SQL*Net is just too chatty." Unfortunately this does nothing to help me solve the problem.
I am curious how others have addressed this issue in a way that doesn't make the network group defensive, but rather gets them to understand that a WAN designed for FTP does not necessarily work fine for Oracle? Are there any test scenarios that effectively demonstrate this in a way that a network engineer can appreciate? Also, if you know of any white-papers that address this issue that would be great also. I tried googling around, but must not have hit the right keywords, because I didn't find anything I could use.
Thanks & Cheers,
P.S. The east-southeast WAN pipe is new, where the east-southwest pipe is a long-time established connection. Each is managed by a different network group in different organizations.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 31 2008 - 15:16:07 CST
- application/ms-tnef attachment: winmail.dat