Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Cost/Time Anomaly

RE: Cost/Time Anomaly

From: Richard J. Goulet <rgoulet_at_kanbay.com>
Date: Tue, 2 Jan 2007 12:32:06 -0500
Message-ID: <C3EE2ADD31ACF64DAB1B236044A1968D51511B@miaexc01.kanbay.com>


This stinks like a star schema of one sort or another, therefore is star_join enabled in this database?     

Dick Goulet, Senior Oracle DBA

45 Bartlett St Marlborough, Ma 01752, USA Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795

RGoulet_at_kanbay.com
: POWERING TRANSFORMATION  


From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of blr_dba Sent: Friday, December 29, 2006 2:03 PM
To: 'ORACLE-L'
Subject: Cost/Time Anomaly

Hi Gurus,  

Am stuck in a tuning problem and need your expertise to get rid of the issue.

I have 3 huge tables(~20M rows each) and many small look up tables joined in a query as follows...

Assume:
Big tables : BT1, BT2, BT3
Small tables: ST1, ST2, ST3, ST4, ST5, ST6

select * from BT1, BT2, BT3, ST1, ST2, ST3, ST4, ST5, ST6 where

BT1.id=BT2.id(+) and 
BT1.id=BT3.id(+) and 
BT1.id=ST1.id(+) and 
BT1.id=ST2.id(+) and 
BT1.id=ST3.id(+) and 
BT1.id=ST4.id(+) and 
BT1.id=ST5.id(+) and 
BT1.id=ST6.id(+); 

The CBO is using hash joins and the cost is too high (400K) and we are having a lots of "direct path write waits".

I tried to remove the outer joins for the small lookup tables by using sclar sub-queries. The cost reduced drastically (10K) but the overall execution time got increased.

Badly need your expertise to get rid of this issue.

Also would like to know even if the CBO cost is less in the second case, why the overall execution time is more. Is n't the cost inversly proportional to the time taken to execute the query?


--
http://www.freelists.org/webpage/oracle-l


klogo.gif
Received on Tue Jan 02 2007 - 11:32:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US