RE: The problem is that SQL*Net is too chatty, because FTP runs fine.

From: "Mark W. Farnham" <mwf@xxxxxxxx>
To: <oradbt054@xxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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,

David

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.

Follow-Ups:
- Re: The problem is that SQL*Net is too chatty, because FTP runs fine.
  - From: David Taft

RE: The problem is that SQL*Net is too chatty, because FTP runs fine.

Other related posts: