This sounds like a possible queueing (i.e., load-induced) issue. Queueing issues show up as "application works fine sometimes, but really slow other times." The impetus for the really slow behavior is when you increase concurrent load on the resource in question (here, the net) just enough that response times begin to degrade exponentially instead of linearly. It's where you're going "up" faster than you're going "out" on that hockey-stick-shaped performance curve. (Sorry I can't show pictures here in text; they'll all be in the book due out in about June.)
If this is the problem, it's not your network administrator's fault, it's your application's fault. Response problems are caused by either or both of only two things: excessive call LATENCY, or excessive call COUNT. Many analysts focus exclusively on latency, never understanding that it's the call COUNT that's the real problem. Excessive call counts within individual application programs, when combined with high user concurrency, produce loads that drive response times into the exponential degradation behavior. Count the number of relevant SQL*Net events in your raw SQL trace data, and get an idea of which cursor most of these events are associated with. For the SQL associated with that cursor, check its tkprof output for unnecessarily high db call counts (or use the Hotsos Profiler and do it in one step). For example, if your app parses every time it executes, it's putting more load than necessary on the network: take your parse calls out of your loops. If it executes once for every row that inserted, updated, or deleted, then it's putting more load than necessary on the network: use array inserts. If it fetches once for ever row that's selected, then it's putting more load than necessary on the network: use array fetching. You don't have to make your application perfect if this is your problem; you only have to reduce unnecessary load enough that your total workload remains left of that knee in the performance curve. You can often eliminate thousands of db calls (and hence network round-trips) by focusing your effort upon just a handful of SQL statements that are executed with the highest levels of concurrency. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -----Original Message----- Lee Sent: Wednesday, October 30, 2002 9:15 AM To: Multiple recipients of list ORACLE-L For what it's worth, we had the same thing going on here recently and have not resolved it. In our case, it is a visual basic app running what is essentially a batch job (don't ask me why a batch job was written as a VB app; I just work here). The client is a PC, and the database is on Tru64 (again ... I just work here). Three different PC's were tried. It appears that the tcp_nodelay parameter worked on two of them but not the third (which, as you might guess, is the production box and the one on which it NEEDS to run faster ... Of course!). All the PC's are on the same subnet, going through same routers to get to the same database. If you get the problem resolved, I will be most interested in your solution. Thus far, we have only been able to attribute it either to sunspots or something about the W2K OS on the PC ... both of which, as we all know, are responsible for a lot of unexplained behavior. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
