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

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 Received on Thu Oct 14 2004 - 10:41:20 CDT