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"
Sent by: root_at_fatcity.com
10/30/2002 08:59 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
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.comUpcoming 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.comSan 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.comSan 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.comSan 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).
