| PERFORMANCE ISSUE - QUERY TUNING [message #403752] |
Mon, 18 May 2009 08:52  |
sridharst
Messages: 2
Registered: May 2009
|
Junior Member |
|
|
Hi,
We are encountering with performance issue with one of our applications.
We need help in tuning these queries.
Please find the attached Tkprof file.
-
Attachment: Tkprof.zip
(Size: 4.18KB, Downloaded 1153 times)
|
|
|
|
|
|
|
|
| Re: PERFORMANCE ISSUE - QUERY TUNING [message #403849 is a reply to message #403755] |
Tue, 19 May 2009 03:40  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
SELECT A.MONITOR_TS, A.TRACE_ID, A.MONITOR_REF, A.DIVORDERNBR, A.HOLDS
FROM
CCI_ORDER_MONITOR_POINTS A WHERE 1=1 AND EXISTS(SELECT 'x' FROM
CCI_ORDER_TRACE_MONITOR M WHERE ( A.MONITOR_TS <> NVL(DECODE(MONITOR_REF,
'JDE201', JDE_A_RECEIVED_DATE,'MFG202', VALVES_IBM_RECEIVED_DATE,
'OsMfg202Out', A.MONITOR_TS, 'SoMfg202In', VALVES_IBM_COMPLETION_DATE,
'JDE202', JDE_EDC_RECEIVED_DATE,'OsJde202Out',JDE_EDC_COMPLETION_DATE),
SYSDATE+300) OR (MONITOR_REF = 'OsMfg202Out' AND VALVES_IBM_ORDER_NUMBER IS
NULL) ) AND A.TRACE_ID = M.TRANSACTION_ID) AND MONITOR_REF IN('JDE201',
'MFG202','OsMfg202Out','SoMfg202In','JDE202','OsJde202Out') AND MONITOR_TS
> TRUNC(SYSDATE-180)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 389062 29.61 263.67 62325 3286341 0 389061
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 389064 29.61 263.67 62325 3286341 0 389061
This cursor is fetched 389,062 times to retrieve 389061 rows. That tells me that you are fetching one row at a time. A BULK COLLECT would fetch the rows more efficiently.
UPDATE CCI_ORDER_TRACE_MONITOR SET VALVES_IBM_ORDER_NUMBER = :B3 ,
VALVES_IBM_PROCESSING_STATUS = 'Complete' , VALVES_IBM_COMPLETION_DATE =
:B2
WHERE
TRANSACTION_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 387694 83.20 332.16 1059 1163125 532352 387694
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 387695 83.20 332.16 1059 1163125 532352 387694
This update was executed 387,694 times and updated 387,694 rows. I assume that it is executed inside a cursor loop in PL/SQL. I would load up a collection and execute the updates with a FORALL statement - the would reduce the number of executions. See this article for more: http://www.orafaq.com/node/1399
Once you have fixed these two problems, the STRUCTURE of your program (the algorithm) will be (near) optimal. Then you can concentrate on tuning individual SQLs. To do that, you will need the Explain Plan of those SQLs. Not sure why that hasn't been included in the trace.
From the wait events I see a lot of DB Block Sequential Reads indicating indexed reads. We are seeing a LOT of IO here, so maybe full table scans would be more appropriate. I'd need to see the plans to be sure.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
