Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL*Net Message to client/SQL*Net more data to client

RE: SQL*Net Message to client/SQL*Net more data to client

From: <Jared.Still_at_radisys.com>
Date: Wed, 30 Oct 2002 10:42:28 -0800
Message-ID: <F001.004F7C62.20021030104228@fatcity.com>


I've seen that very thing happen to an app.

It was object oriented to the Nth degree. Joins were not generally done in the database, rather each method in the app retrieved each individual piece of data atomically.

In an hour I think this app created 10 million network packets, average size was 200 bytes.

Since no one was willing to fix the app, we moved it to the database server for a 40% performance gain.

Jared

"Cary Millsap" <cary.millsap_at_hotsos.com> Sent by: root_at_fatcity.com
 10/30/2002 08:59 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: SQL*Net Message to client/SQL*Net more data to client


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: slee_at_dollar.com

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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com

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: ListGuru_at_fatcity.com (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:
  INET: Jared.Still_at_radisys.com

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: ListGuru_at_fatcity.com (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). Received on Wed Oct 30 2002 - 12:42:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US