Home » RDBMS Server » Performance Tuning » PERFORMANCE ISSUE - QUERY TUNING (Oracle 10g, linux)
PERFORMANCE ISSUE - QUERY TUNING [message #403752] Mon, 18 May 2009 08:52 Go to next message
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 154 times)
Re: PERFORMANCE ISSUE - QUERY TUNING [message #403754 is a reply to message #403752] Mon, 18 May 2009 08:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No thanks, I make a rule of not downloading things.
Re: PERFORMANCE ISSUE - QUERY TUNING [message #403755 is a reply to message #403754] Mon, 18 May 2009 09:03 Go to previous messageGo to next message
sridharst
Messages: 2
Registered: May 2009
Junior Member
Please find the attached text file..
  • Attachment: Tkprof.txt
    (Size: 30.02KB, Downloaded 267 times)
Re: PERFORMANCE ISSUE - QUERY TUNING [message #403849 is a reply to message #403755] Tue, 19 May 2009 03:40 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: performance Tunning
Next Topic: Query Slow
Goto Forum:
  


Current Time: Thu Dec 08 20:05:53 CST 2016

Total time taken to generate the page: 0.15381 seconds