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

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 Received on Sat Nov 24 2007 - 12:45:16 CST