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

> 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