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