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

On Nov 24, 3:47 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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=d...http://forums.oracle.com/forums/thread.jspa?messageID=1145106http://blogs.msdn.com/vsdata/archive/2004/12/17/323719.aspxhttp://www.petefinnigan.com/weblog/archives/00000251.htm
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Ok, i found, don't know why i did'nt see the problem before, but i think because client and server we're not on the same network segment
the setting in the sqlnet.ora SQLNET.AUTHENTICATION_SERVICES was the problem.

Thanks to you for helping
Chris Received on Tue Nov 27 2007 - 05:07:34 CST