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

> 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?
>

All of this is in the book "Optimizing Oracle Performance" by Cary Millsap/Jeff Holt.

http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_perf.html is a free article that is about the same topic.

You can turn on trace in another session. You might want to try just tracing one session in your production system and see what impact it has. Maybe you can catch it when things are slow.

The waits in the trace file (not the output from tkprof) have some more information about what it waits on. But what I think you will really gain is an understanding of the kind of thing it is waiting on.  Is it disk? Is it a lock? Once you find the name of the wait event you can research that particular event and see what it means.

Anyway, the book I mention does a much better job of explaining this. Also, it is relatively short and inexpensive.

• Bobby