Re: Cost/Time Anomaly

Thanks for your reply...

There is no filter in this query. 

Want to know why in second case the cost is less but time taken to execute the 
query is more...?

Any suggestion on improving the performance of the query? I have two CPUs and 
the big tables are hash partitioned on ID column into two partitions.
  ----- Original Message ----- 
  From: Steve Ollig 
  To: deepak_blr@xxxxxxxxxxx ; ORACLE-L 
  Sent: Saturday, December 30, 2006 1:10 AM
  Subject: RE: Cost/Time Anomaly


  And your resultset will contain at least as many rows as B1 (~20M as you 
said), perhaps many more - correct?  Given that, and the fact that B2 and B3 
also contain ~20M rows, the CBO seems to be making a perfectly logical choice.

  Is it possible you don't want such a large resultset returned?  Is there a 
filter you left off that would limit the # of rows?
    -----Original Message-----
    From: oracle-l-bounce@xxxxxxxxxxxxx 
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of blr_dba
    Sent: Friday, December 29, 2006 1: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? 


  ______________________________________________________________________
  This email has been scanned by the MessageLabs Email Security System.
  For more information please visit http://www.messagelabs.com/email 
  ______________________________________________________________________

Other related posts: