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?

Other related posts: