RE: ODBC and Network Waits

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Wed, 12 Aug 2009 14:33:47 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01514F49_at_WIN02.hotsos.com>

You are likely to always get some of these, the trick is: Are they the dominant wait event? I'll take it from your description that it is.  

Just because you have a lot of these doesn't mean there is anything wrong with the network. More likely there is something wrong with the way the code is using the network. SQL*Net Message to Client and From Client will be in pairs and the more important one is FROM CLIENT. This actually measures the entire round trip (TO CLIENT seems to only measure how long it takes to create the message that will be sent, not the actual traffic).  

With FROM CLIENT there are two things that I generally look for:  

1. Are there a lots of them (at least in the 100s-1000s) of relatively short duration (all less then a second)? If so this is a chatty application that is talking back and forth a lot, my goal would be to stop that. How can I get the app to send less traffic to the database?
2. Are there few of them (likely less then 100s) of relatively long duration (over a second)? If so then the problem isn't in the database tier but in the application tier. Here the app is doing a lot of work and the database is spending a lot of time just waiting around for the app to tell it what to do next.

MORE DATA TO CLIENT just means the data being sent to the client level is to big to fit in a single network packet. Other then changing ARRAY_SIZE to a smaller value or changing the network packet size to a larger value, your likely always have these. These are necessarily a problem, of course it might mean that some one is running a "SELECT * FROM WIDE_TABLE" when they shouldn't be. Like they only really use a few columns from a table with 100+ columns. This is a huge waste of network bandwidth.  

I hope this helps.  

---

Ric Van Dyke

Hotsos Enterprises

---

 

Hotsos Symposium

March 7 - 11, 2010

Be there.    

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of walid alkaakati Sent: Wednesday, August 12, 2009 5:17 AM To: oracle-l_at_freelists.org
Subject: ODBC and Network Waits  

I have an application that uses ODBC and c++ ,i have checked the network latency and it is acceptable but the database as a whole shows high wait levels of  

  SQL*Net message to client                            
  SQL*Net more data to client                                    
  SQL*Net message from client

I have traced one slow SQL with TKPROF and i found a high cpu for parsing and fetching but also i found these wait events only.  

My question has any one faces related problems to ODBC for example bugs,data send and recevied ,network.... i want to be sure it is only an application problem.  

Oracle 10g, Windows server 2003  

Thanks    

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 12 2009 - 14:33:47 CDT