Re: Very slow fetches with high cputime

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: ronpet@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 16 Feb 2009 23:47:35 +0800


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




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


Other related posts: