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".

--

http://www.freelists.org/webpage/oracle-l Received on Sat Dec 30 2006 - 09:05:46 CST