Re: Tuning question - low cpu_time, high elapsed_time - what gives?

BigBoote66_at_hotmail.com (Steve B) wrote in message news:<67bcf80a.0410140741.28308e37_at_posting.google.com>...
> bdurrett_at_diamonddata.com (Bobby Durrett) wrote in message news:<1903bd23.0410131122.f966e84_at_posting.google.com>...
> > > I'm not sure I understand your advice. We know which sql statements
> > > are the slow ones - I left them out of the output above for space
> > > purposes. However, the same statement that runs slowly (with the
> > > large elapsed time but favorable statistics) will run just fine if you
> > > re-execute it at a later date (with physical i/o being the same), so
> > > the statement is already "tuned". I believe the real problem is some
> > > kind of resource contention, and I'm looking for a way of identifying
> > > the resource that is causing the slowdown.
> > >
> > > -Steve
> >
> > Steve,
> >
> > I think the idea is to do a trace when the problem occurs. If you do
> > a tkprof waits=yes it will show you what it was waiting for. If the
> > elapsed time is not accounted for by the CPU time or the Oracle waits
> > then it would have to be waiting on something like being queued on the
> > CPU. But if your CPU usage is low chances are the Oracle waits will
> > point your right to it.
> >
> > - Bobby
>
> I've done traces before, but always from within the session that I'm
> tracing, and only for the purposes of query tuning - looking at
> minimizing i/o or playing with query plans. Using traces for resource
> contention are new to me. Is it possible to enable tracing for
> session A from another session B? Also, the problem occurs somewhat
> sporadically; I assume I can't enable tracing after the fact, so if I
> want to capture this behavior, I'll need to be tracing the sessions
> a-priori. I have pool of sessions that exhibit this behavior - about
> 20, and I'm a little nervous about what kind of impact it's going to
> have on our production system to have 20 sessions tracing at 10086 -
> is this a valid concern?
>
> Finally, once I do have a trace file that shows the statement and the
> pain it was experiencing, and it shows that it suffered from some kind
> of wait XXX, will the trace file show me the other process that was
> causing the block, or will it just show the wait.
>
> I realize these are complex questions - do you have a resource you
> could point me at (book, URL) that could help me out with this?
>
> Thanks,
> -Steve

here is something you can do and it is not in Bill Clinton book about his saga with Ana C Dent:

use oradebug and break on scgbrm() which returns modes of conflicting requests from within a blocking request -- which is exactly what you need. Received on Mon Oct 18 2004 - 02:04:33 CDT