Re: Very slow fetches with high cputime

Your problem isn't the Waits -- as you've already deduced. The 604 fetches for 661,012 rows actually read 2 billion 766 million buffers. The problem is the execution plan -- most likely doing a Nested Loop a few million times ?

Check the Execution Plan.

Hemant

At 11:10 PM Monday, ronpet_at_zonnet.nl wrote:
>Dear list,
>
>I'm trying to fix a performance issue on a query that runs 48 hours,
>where it normally runs for 5 minutes.
>During last slow-run, I activated a 10046 traceevent, to find out
>where time is spend on.
>
>Query stats (tkprof) are:
>
>call count cpu elapsed disk query current
> rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 0 0.00 0.00 0 0 0
> 0
>Execute 0 0.00 0.00 0 0 0
> 0
>Fetch 604 170203.78 167091.28 44687 2765869668 0
> 661012
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 604 170203.78 167091.28 44687 2765869668 0
> 661012
>
>Misses in library cache during parse: 0
>Parsing user id: 71
>
>Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total Waited
> ---------------------------------------- Waited ---------- ------------
> SQL*Net more data to client 5448 0.00 0.51
> db file sequential read 12574 0.14 36.56
> db file scattered read 424 0.05 4.81
> SQL*Net message from client 605 0.02 4.74
> SQL*Net message to client 604 0.00 0.00
> latch: cache buffers chains 992 0.45 241.80
> read by other session 25 0.01 0.06
> latch free 54 0.30 14.29
>********************************************************************************
>
>Since an average fetch takes about 5 minutes, I browsed through the
>trace-file. I noticed that:
>* a FETCH cputime (c=) itself is about 5 minutes
>* a FETCH elapsetime (e=) is often smaller the FETCH cpu-time
>* the total of WAIT's is only about 2 seconds
>
>Does anyone have anyone have idea what's making the fetch so slow
>and why FETCH elapsetime is smaller than FETCH cpu-time. My main
>concern is to find out how I can solve this performance issue.
>
>I included a part of the 10046 tracefile (between 2 fetches) below.
>
>Thnx for any feedback,
>Ronald Peters
>

--
http://www.freelists.org/webpage/oracle-l