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: jaromir nemec <jaromir_at_db-nemec.com>
Date: Sat, 30 Dec 2006 16:05:46 +0100
Message-ID: <2be801c72c23$ff41d350$3c02a8c0@JARAWIN>


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

  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

Original text of this message

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