RE: Cost/Time Anomaly

From: "Kerber, Andrew" <Andrew.Kerber@xxxxxxx>
To: rgoulet@xxxxxxxxxx, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 2 Jan 2007 11:49:35 -0600
Try using a union or 'union all' to reduce your number of reads., eg:

 

Select * from bt1,bt2 where

Bt1.id=bt2.id

Union

Select * from bt1, bt3 where

Bt1.id=bt2.id

Union...

 

Andrew W. Kerber 
Oracle DBA 
UMB 
816-860-3921 
andrew.kerber@xxxxxxx 

 

"If at first you dont succeed, dont take up skydiving" 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Richard J. Goulet
Sent: Tuesday, January 02, 2007 11:32 AM
To: ORACLE-L
Subject: RE: Cost/Time Anomaly

 

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@xxxxxxxxxx
: POWERING TRANSFORMATION 

 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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? 


------------------------------------------------------------------------------
NOTICE:  This electronic mail message and any attached files are confidential.  
The information is exclusively for the use of the individual or entity intended 
as the recipient.  If you are not the intended recipient, any use, copying, 
printing, reviewing, retention, disclosure, distribution or forwarding of the 
message or any attached file is not authorized and is strictly prohibited.  If 
you have received this electronic mail message in error, please advise the 
sender by reply electronic mail immediately and permanently delete the original 
transmission, any attachments and any copies of this message from your computer 
system. Thank you.

==============================================================================
RE: Cost/Time Anomaly

Other related posts:
