RE: Single-task message waits

Hey Cary,

No magic bullet? I was afraid you were going to say that. :)

I see that there are some parsing issues from the first of many 10046 traces. Most of these high-parsing statements are generated by Oracle when a Context index is used in a query. The good news, if TKPROF output can be believed, is that the total time spent in all parses is 35 seconds out of 52 minutes elapsed, so I hadn't planned on spending any more time here.

As for the one-row-at-a-time manipulation part, well... Implicit cursors within cursors within cursors. We've removed a few where we could, but unfortunately a rewrite probably isn't going to happen for this project. We're still learning to be good SQL coders while dealing with crappy data layouts from the 80s.

Thanks for your input! And thanks for the 10046 paper on hotsos.com! :) Rich

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]
> Sent: Wednesday, December 04, 2002 12:54 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Single-task message waits
>
>
> Rich,
>
> Well, at least we've cleared up the "whether." Now the
> question turns to
> the "why."
>
> Once you have identified a program's dominant response time
> contributor,
> solving any optimization problem degenerates to a very simple
> strategy:
>
> 1. Reduce the number of times the dominant contributor's services are
> required.
> 2. Reduce the duration per service call of the dominant contributor.
>
> I expect that #2 will be difficult to do in this case, because I'd
> expect that the single-task driver is probably the fastest conceivable
> way that two pieces of client-server code can communicate
> (they're after
> all in the same executable). Your leverage is probably #1.
> It's the old
> adage:
>
> Q: What's the fastest way to do X?
>
> A: Don't.
>
> Find out whether your process is making more database calls than it
> needs to be making. Investigate the usual suspects: SQL that
> doesn't use
> bind variables and therefore has to be parsed more often than
> it should
> be parsed, one-row-at-a-time manipulation instead of using arrays and
> bulk features, and so on.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic, Dec 9-11 Honolulu
> - Hotsos Clinic 101, Jan 7-9 Knoxville
> - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
> - 2003 Hotsos Symposium, Feb 9-12 Dallas
>
>
> -----Original Message-----
> Rich
> Sent: Wednesday, December 04, 2002 10:59 AM
> To: Multiple recipients of list ORACLE-L
>
> Hey Cary,
>
> "Yes" and "Yes". Which brings me back to my original
> question: How do
> I go
> about tracking down "single-task message" waits?
>
> Thanks!
> Rich
>
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International,
> Sussex, WI
> USA
>
> > -----Original Message-----
> > From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]
> > Sent: Wednesday, December 04, 2002 10:14 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Single-task message waits
> >
> >
> > If...
> >
> > 1) you're collecting time consumption data for ONLY the
> > session you care
> > about, AND
> >
> > 2) you're collecting the data for a time interval [t0,t1]
> > that includes
> > ONLY response time that you care about eliminating,
> >
> > ...then the waits are worth investigating.
> >

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.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).