Wait event "SQL*Net more data to client" in wait class "Network"
chris
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)
sybr...@hccnet.nl
wrote:
sql*net more data to client means your data doesn't fit in one sqlnet
packet. The default packet size is 2k. You can adjust this by setting
the SDU on listener and client tnsnames.ora.
The SDU needs to be a multiple of the network card MTU, usually 1500
bytes.
--
Sybrand Bakker
Senior Oracle DBA
Charles Hooper
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.
chris
>
> - 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
Charles Hooper
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
chris
>
> Charles Hooper
> IT Manager/Oracle DBA
>
> - 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
Noons
>
> sql*net more data to client means your data doesn't fit in one sqlnet
> packet. The default packet size is 2k. You can adjust this by setting
> the SDU on listener and client tnsnames.ora.
> The SDU needs to be a multiple of the network card MTU, usually 1500
> bytes.
Last time I set those - three months ago - I was told
by an "expert" that "those things don't matter anymore
and it only shows my age" that I worry about them.
Apparently new network gear and cabling doesn't
suffer from MTU starvation...
Yeah! Right...
So I just changed them anyway,
see if I care?
;-)