Re: Wait event "SQL*Net more data to client" in wait class "Network"

On Nov 24, 1:45 pm, chris <cboi..._at_jlr.ca> wrote:
> On Nov 23, 8:05 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > On Nov 23, 1:30 pm, chris <cboi..._at_jlr.ca> wrote:
>
> > > Hi,
>
> > > Starting last week my application got problem "SQL*Net more data to
> > > client"
> > > 1 connection on 10 hang for about 10 secondes.
>
> > > I'd check the network, and everything is ok, and the problem is
> > > randomly
> > > yesterday afternoon, everything go fine, this morning it hang a lot
>
> > > My env :
> > > Oracle 10.2.0.3 std 64bit
> > > Win 2003 ent 64bit
> > > VB 6 app design "Just-in-time" connection
> > > connect via microsoft ODBC
>
> > > I have 4 websites connecting on the same instance, with no problem
> > > and same design ...
>
> > > The only difference between website and the vb6 app is the network
> > > segment
> > > website and Db are on the same network segment (DMZ) and the app is
> > > in
> > > the Intranet (Inside interface)
>
> > > I'll monitor my FW to see if something wrong ... but change nothing on
> > > this recently
>
> > > If someone have seen this, maybe have a solution ...
>
> > > Thank you
> > > Chris
> > > (sorry if my english is poor)
>
> > In addition to Sybrand's comments, you may want to investigate a
> > couple additional items:
> > * I am familar with VB6, but not the term '"Just-in-time"
> > connection'. Is the VB 6 application connecting and disconnecting
> > from the database server, or does it keep the same connection open?
> > Each new connection will likely create a new thread on the Windows
> > server, and the disconnection will likely terminate the thread - this
> > is probably less time consuming than the equivalent on Unix, where a
> > new process is created or destroyed, but should be avoided if
> > possible. The database server may cache a previously disconnected
> > session in the event that a new session will be created by the same
> > calling application, do that may reduce the time for the second and
> > following connection requests.
>
> > * The firewall between the client (on the LAN) and database (in the
> > DMZ) may be responsible for some/all of the delay. How is the
> > firewall configured to handle packets to/from the database server in
> > the DMZ? Is it a stateful firewall? What is the MTU setting on the
> > firewall?
>
> > * How does the application perform when using Oracle's ODBC client or
> > OLEDB (Provider=OraOLEDB.Oracle)?
>
> > I suggest that you run a packet capture utility, such as Wireshark, on
> > the client and server (I have not had a chance to test Wireshark on 64
> > bit Windows, so I am not sure if it will affect stability, but it runs
> > fine on 32 bit Windows). Wireshark will be able to indicate if
> > packets are being lost and retransmitted, and even if differences in
> > MTU (or other settings), Nagle/delayed ACKs or something else is
> > causing the delay at the network level. Reading a packet capture
> > takes a bit of practice.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> > - Show quoted text -
>
> Hi thanks for the answer,
>
> When a say just-in-time, the application connect, ask the DB and
> disconnect in each method
> it use a connection pooling and it work well... suppose to! I have to
> check but for the last 7 years
> i'd never got problem with that
>
> I'll try packet capture with my networking consultant.
> The MTU on my FW is 1500.
>
> But if its a problem related to the size of the packet, that could
> cause the connect event to hang ?
> i don't have any problem with SQL query and all DML action, very good
> response time
>
> I'll let you know if i found the prob
> Thank you
> Chris

It is hard to say, as it depends in part on how the firewall is configured. The firewall may be configured to reject fragmented packets (where the packet data exceeds the maximum data size roughly 1500 bytes), or it may be configured to protect against certain forms of attack that purposely send malformed packets - such as sending the second packet fragment without sending the first packet fragment. VPN solutions that use X.509 certificates that are secured with a large number of encryption bits (greater than 128) may have difficulty passing through such devices.

It could also be that the firewall is configured to allow only certain port numbers to be used for communication between the LAN and the DMZ - a well configured firewall blocks all but the essential ports that must be enabled in both directions. If I recall correctly, a client computer makes a connection request to the listener port (usually TCP port 1521) and then when the connection is handed off by the listener, a different port number is potentially selected to continue the communication. It could be that this secondary port (randomly assigned) is blocked by either the firewall at the LAN/DMZ perimeter, the Windows firewall on the server, or the Windows firewall on the client computer. The problem _may_ also be the result of the firewall performing NAT (many to one, or one to many address translation), although I am not encountering that problem.

Possible resources:

http://www.petefinnigan.com/forum/yabb/YaBB.cgi?board=oracle;action=display;num=1136314676
http://forums.oracle.com/forums/thread.jspa?messageID=1145106
http://blogs.msdn.com/vsdata/archive/2004/12/17/323719.aspx
http://www.petefinnigan.com/weblog/archives/00000251.htm

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Nov 24 2007 - 14:47:01 CST