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

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Tue, 12 Oct 2004 14:13:29 GMT
Message-ID: <Xns9580497EA3794SunnySD@68.6.19.6>

BigBoote66_at_hotmail.com (Steve B) wrote in news:67bcf80a.0410120608.7906bf18_at_posting.google.com:

> "Ana C. Dent" <anacedent_at_hotmail.com> wrote in message
> news:<Xns957FB795CB741SunnySD_at_68.6.19.6>...

>> enable event 10046 at level 8
>> run the resultant trace file thru TKPROF.
>> TKPROF's output will reveal the culprit SQL statement(s).
>> Then tune the ones where a large discrepency between
>> elapsed & CPU time exists.
>> 
>> You can search the achives of the ORACLE-L email list
>> to complete instructions on how to do all this

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

Visit http://asktom.oracle.com
do a keyword search on "enqueue wait". Received on Tue Oct 12 2004 - 09:13:29 CDT