Home » RDBMS Server » Performance Tuning » Query performance problem (Oracle 9.2.0.8.0)
Query performance problem [message #440451] Mon, 25 January 2010 00:24 Go to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Hi Friends,

I have the following query which takes hours to execute. The query selects records from the table COMPETITIVE_RATE_SHOPPING_FACT. The columns in the where clause have unique index created on them.

SELECT 
 CURR.BOOKING_RATE_CALENDAR_KEY,
 CURR.ARV_CALENDAR_KEY,
 CURR.SITE_KEY AS SITE_KEY,
 CURR.DAYS_TO_ARRIVAL_DATE,
 CURR.LENGTH_OF_STAY,
 CURR.NUMBER_OF_GUEST,
 CURR.DATA_SOURCE_CODE,
 CURR.COMPETITOR_PRODUCT_NAME,
 MIN(CURR.RATE_AMT_USD) AS MIN_RATE_AMT_USD,
 (
  SELECT
   MIN(PREV.RATE_AMT_USD)
  FROM
   OPERATIONS.COMPETITIVE_RATE_SHOPPING_FACT PREV
  WHERE
   PREV.ARV_CALENDAR_KEY = CURR.ARV_CALENDAR_KEY AND
   PREV.SITE_KEY = CURR.SITE_KEY AND
   PREV.LENGTH_OF_STAY = CURR.LENGTH_OF_STAY AND
   PREV.NUMBER_OF_GUEST = CURR.NUMBER_OF_GUEST AND
   PREV.DATA_SOURCE_CODE = CURR.DATA_SOURCE_CODE AND
   PREV.COMPETITOR_PRODUCT_NAME = CURR.COMPETITOR_PRODUCT_NAME AND
   PREV.BOOKING_RATE_CALENDAR_KEY = CURR.BOOKING_RATE_CALENDAR_KEY - 7 AND
   PREV.REPORTING_COMPETITOR_ID = PREV.RUBICON_PROPERTY_ID
 ) AS MIN_PREV_RATE_AMT_USD
FROM
 OPERATIONS.COMPETITIVE_RATE_SHOPPING_FACT CURR
WHERE
 CURR.REPORTING_COMPETITOR_ID = CURR.RUBICON_PROPERTY_ID AND
 CURR.BOOKING_RATE_CALENDAR_KEY > 0 AND
 CURR.DM_INSERTED_DATE >= (SELECT MAX(C.DM_INSERTED_DATE) - 1 FROM OPERATIONS.COMPETITIVE_RATE_SHOPPING_FACT C)
GROUP BY
 CURR.BOOKING_RATE_CALENDAR_KEY,
 CURR.BOOKING_RATE_DAY_TIME_KEY,
 CURR.ARV_CALENDAR_KEY,
 CURR.DAYS_TO_ARRIVAL_DATE,
 CURR.LENGTH_OF_STAY,
 CURR.NUMBER_OF_GUEST,
 CURR.DATA_SOURCE_CODE,
 CURR.RUBICON_PROPERTY_ID,
 CURR.COMPETITOR_PRODUCT_NAME,
 CURR.SITE_KEY;


Can you please advice me on what could be reason for the performance? I have also attached the plan for the above query in Plan.jpg file. Any help would be appreciated.

Thanks,
Senthil
  • Attachment: Plan.JPG
    (Size: 13.40KB, Downloaded 159 times)
Re: Query performance problem [message #440452 is a reply to message #440451] Mon, 25 January 2010 00:30 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLvAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Query performance problem [message #440512 is a reply to message #440452] Mon, 25 January 2010 05:39 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The problem is the scalar sub-query in the SELECT clause. It gets executed for every row returned.

Run it with the scalar sub-query removed and compare the performance. I expect you will find it goes from hours down to minutes or even seconds.

If I am right, you need to find another way to write the query - as a join most probably.

Ross Leishman
Previous Topic: Use of double parallel hint
Next Topic: Undo used by Batch Job
Goto Forum:
  


Current Time: Tue Sep 27 02:27:32 CDT 2016

Total time taken to generate the page: 0.05796 seconds