Re: Cost/Time Anomaly

Hi Deepak,

the hash plan for this select may be OK.
You could profit from partition wise join, but only two partitions are probably 
too low to see the effect.
Check the setting of hash area size if you use manuall policy if it isn't too 
low.

Regards,

Jaromir

----- Original Message ----- 
  From: blr_dba 
  To: 'ORACLE-L' 
  Sent: Friday, December 29, 2006 8:03 PM
  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".

Other related posts: