Home » RDBMS Server » Performance Tuning » batch job that run long (Oracle 9.2)
batch job that run long [message #322117] Thu, 22 May 2008 09:45 Go to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I have batch job that run for long I got the trace file.Find the file attached.Please advice
Re: batch job that run long [message #322136 is a reply to message #322117] Thu, 22 May 2008 10:25 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
The MOST effective way to speed up a process is to simply not do the action where the majority of the time is being spent.



SELECT UNIT_RETAIL, MULTI_UNIT1, MULTI_UNIT_RETAIL1, MULTI_UNIT2, 

  MULTI_UNIT_RETAIL2, MULTI_UNIT3, MULTI_UNIT_RETAIL3, MULTI_UNIT4, 

  MULTI_UNIT_RETAIL4, MULTI_UNIT5, MULTI_UNIT_RETAIL5, MULTI_UNIT6, 

  MULTI_UNIT_RETAIL6, MULTI_UNIT7, MULTI_UNIT_RETAIL7, MULTI_UNIT8, 

  MULTI_UNIT_RETAIL8, MULTI_UNIT9, MULTI_UNIT_RETAIL9, MULTI_UNIT10, 

  MULTI_UNIT_RETAIL10, MULTI_UNIT11, MULTI_UNIT_RETAIL11, MULTI_UNIT12, 

  MULTI_UNIT_RETAIL12, MULTI_UNIT13, MULTI_UNIT_RETAIL13, MULTI_UNIT14, 

  MULTI_UNIT_RETAIL14, MULTI_UNIT15, MULTI_UNIT_RETAIL15, MULTI_UNIT16, 

  MULTI_UNIT_RETAIL16, ACTIVE_DATE 

FROM

 OD_ZONE_LVL IT WHERE IT.ITEM = :B2 AND IT.ZONE_ID = :B1 AND IT.ZONE_GROUP_ID 

  NOT IN(99, 9999) AND IT.ACTIVE_DATE = (SELECT MAX(ITA.ACTIVE_DATE) FROM 

  OD_ZONE_LVL ITA WHERE ITA.ITEM = IT.ITEM AND ITA.ZONE_ID = IT.ZONE_ID AND 

  ITA.ACTIVE_DATE <= :B3 AND ITA.ZONE_GROUP_ID NOT IN(99, 9999) AND 

  ITA.OD_LAST_UPDATE_DATETIME = (SELECT MAX(ITB.OD_LAST_UPDATE_DATETIME) FROM 

  OD_ZONE_LVL ITB WHERE ITB.ITEM = ITA.ITEM AND ITB.ZONE_ID = ITA.ZONE_ID AND 

  ITB.ACTIVE_DATE = ITA.ACTIVE_DATE AND ITB.ZONE_GROUP_ID = ITB.ZONE_GROUP_ID)

  )





call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute 886266     51.83      50.21          0          0          0           0

Fetch   886266    784.77    1178.16     299083   39589445          0      886265

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total   1772533    836.60    1228.38     299083   39589445          0      886265

Doing the same SQL close to 1,000,000 time seems excessive at 1st glance.
Re: batch job that run long [message #322383 is a reply to message #322136] Fri, 23 May 2008 05:26 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That right. Your problem is that you are executing SQL-within-SQL.

Take a look at this article for some re-design ideas.

Ross Leishman
Previous Topic: What situation should I add more termporary tablespace?
Next Topic: New to Tuning
Goto Forum:
  


Current Time: Sat Dec 03 22:12:15 CST 2016

Total time taken to generate the page: 0.04225 seconds