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

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. Received on Fri Nov 23 2007 - 19:05:38 CST