RE: Single-task message waits

After filing a TAR on this, Oracle Support says "It's Internal...out of user's control." The problem with that response is that the 75-minute process is spending 30 minutes waiting for "single-task message" according to my grep'ing and SQL*Loading of waits of the 10046 traces. Aside from that, I've got a 10-minute wait on "db file sequential read", which for various reasons, I think I'm stuck with. The 30 minutes isn't really that bad, except this load needs to run 24 times (one for each month in the past two years).

If I ever meet the genius who decided to bury key G/L data in a free-format CHAR column... :\

Rich

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

> -----Original Message-----
> From: Jesse, Rich
> Sent: Wednesday, December 04, 2002 2:54 PM
> To: Multiple recipients of list ORACLE-L
> Subject: 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).