RE: Optimizer Mode......how to choose the right one?

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Wed, 08 Aug 2001 09:33:47 -0700
Message-ID: <F001.003646DC.20010808091659@fatcity.com>

I
assume you are refering to the classic statement: 
All things being equal RBO chooses the driving order by taking thetables in the FROM clause RIGHT to LEFT. CBO determines join order from costs derived from gathered statistics. If there are no stats then CBO chooses the driving order of tables from LEFT to RIGHT in the FROM clause. This is OPPOSITE to the RBO.

This
only happens in two cases, there is a tie on the cost, which would be quite rare.
2nd if
there is no statisitcs, which is poor use of CBO and should be considered normal.
Both
of these cases are not normal practice.

"Do not criticize someone until you walked a mile in their shoes, that way when you
criticize them, you are a mile a way and have their shoes." Christopher R. Spence <FONT

face="Bell MT" size=1>OCP  MCSE MCP A+ RAPTOR CNA <FONT 
face="Comic Sans MS" size=2>Oracle DBA <FONT face="Comic Sans MS" 
size=2>Phone: (978) 322-5744 <FONT face="Comic Sans MS" 
size=2>Fax:    (707) 885-2275

Fuelspot <FONT

face="Comic Sans MS" size=2>73 Princeton Street <FONT 
face="Comic Sans MS" size=2>North, Chelmsford 01863 <FONT 
face="Comic Sans MS" size=2>

<FONT

 face=Tahoma size=2>-----Original Message-----From: Vadim Gorbounov [mailto:vgorbounov_at_simplyengineering.com] Sent: Wednesday, August 08, 2001 12:09 PMTo: 'cspence_at_FuelSpot.com'Subject: RE: Optimizer Mode......how to choose the right one?
Chris,
Sorry for taking your time, but it seems ORDERED matters with CBO. What about Note:35934.1,
 they
 say quite opposite to yours:
 Hints- Any hint, except RULE, causes
 CBO to be used. It is very important to note that a HINT cannot be 'turned off' by any parameter settings. Any
 thoughts? Did I get something wrong? I think,
 Ordered will essentially reduce # of permutation optimizer makes to calculate cheapest access path.

Vadim Gorbounov,

Oracle DBA


-----Original Message-----From: Christopher Spence [mailto:cspence_at_FuelSpot.com]Sent: Wednesday, August 08, 2001 12:16 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Optimizer Mode......how to choose the right one?

I
 by no means say CBO doesn't make mistakes. It by far does, but the original statement stays accurate, order of tables only matters with RBO (if comes up with tie) and ordered hint. 
 There is nothing wrong with getting it in a good order, I just wanted to stress that it only truely matters during those two conditions. That's all, not trying to be adversary. 
 CNA is Novell Admin, I haven't done it in a while, I originally started as a Novell Admin.
 Raptor is a firewall product I spent about 4 years working with.
 
 "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."
 Christopher R. Spence <FONT

    face="Bell MT" size=1>OCP  MCSE MCP A+ RAPTOR CNA <FONT 
    face="Comic Sans MS" size=2>Oracle DBA <FONT face="Comic Sans MS" 
    size=2>Phone: (978) 322-5744 <FONT face="Comic Sans MS" 
    size=2>Fax:    (707) 885-2275

Fuelspot <FONT

    face="Comic Sans MS" size=2>73 Princeton Street <FONT 
    face="Comic Sans MS" size=2>North, Chelmsford 01863 <FONT 
    face="Comic Sans MS" size=2>  
    
      
      <FONT 
      face=Tahoma size=2>-----Original Message-----From: Koivu, Lisa 
      [mailto:lisa.koivu_at_efairfield.com] Sent: Wednesday, August 08, 
      2001 11:08 AMTo: 'ORACLE-L_at_fatcity.com'; 
      'cspence_at_FuelSpot.com'Subject: RE: Optimizer Mode......how to 
      choose the right one?
      Ahh, but Christopher, therein 
      lies the problem.  The CBO makes mistakes and sometimes needs the 
      gentle nudging of the ORDERED hint.  I've seen a query go from hours 
      to 2 seconds after some analysis of the execution plan that was failing 
      miserably.
      Love the sig.  You are a 
      CNA?  A Raptor?  Am I missing something? 
      Lisa Koivu <FONT 
      face=Arial color=#0000ff size=2>Vikings Fan (and DBA) <FONT 
      face=Arial color=#0000ff size=2>Ft. Lauderdale, FL, USA 
      
        -----Original Message----- 
        From:   <FONT 
        face=Arial size=1>Christopher Spence [SMTP:cspence_at_FuelSpot.com] 
        Sent:   <FONT 
        face=Arial size=1>Wednesday, August 08, 2001 10:27 AM 
        To:     
        Multiple recipients of list ORACLE-L 
        <FONT face=Arial 
        size=1>Subject:        
        RE: Optimizer Mode......how to choose the right 
        one? 
        The driving table order does not matter when 
        using CBO, it will detirmine the 
        order based on cost, not placement. 
        The order only matters when dealing with the 
        RBO (when it hits a tie, otherwise it 
        will change it, but this happens often), or when using the 
        ordered hint.  
        "Do not criticize someone until you walked a 
        mile in their shoes, that way when 
        you criticize them, you are a mile a way and have their shoes." 
        
        Christopher R. Spence  OCP  MCSE 
        MCP A+ RAPTOR CNA Oracle DBA 
        Phone: (978) 322-5744 <FONT 
        face=Arial size=2>Fax:    (707) 885-2275 
        Fuelspot <FONT face=Arial 
        size=2>73 Princeton Street North, 
        Chelmsford 01863   
        
        -----Original Message----- <FONT 
        face=Arial size=2>Sent: Wednesday, August 08, 2001 4:30 AM 
        To: Multiple recipients of list 
        ORACLE-L 
        >>Order of the tables only matters when 
        using the ORDERED hint or using <FONT face=Arial 
        size=2>>>rbo AND there is a tie ranking. 
        But surely the very nature of the CHOOSE hint 
        is to select the best method to use 
        whether it's to follow the RBO or the CBO. Therefor ensuring that 
        you have the driving table defined in 
        the correct place within the query will <FONT face=Arial 
        size=2>have a benefit whether the database selects to use the RBO or 
        CBO... 
        IMHO. <FONT face=Arial 
        size=2>Kev. 
        __________________ 
        Kevin Thomas <FONT face=Arial 
        size=2>Technical Analyst Deregulation 
        Services Calanais Ltd. 
        Tel: 0141 568 2314 <FONT 
        face=Arial size=2>Fax: 0141 568 2366 <FONT face=Arial 
        size=2><A target=_blank 
        href="http://www.calanais.com">http://www.calanais.com 
        
        -----Original Message----- <FONT 
        face=Arial size=2>Sent: 07 August 2001 18:36 <FONT face=Arial 
        size=2>To: Multiple recipients of list ORACLE-L 
        When using first rows, you force the cost 
        based optimizer, in which the order 
        of the tables does not matter. 
        Order of the tables only matters when using 
        the ORDERED hint or using rbo AND 
        there is a tie ranking. 
        "Do not criticize someone until you walked a 
        mile in their shoes, that way when 
        you criticize them, you are a mile a way and have their shoes." 
        
        Christopher R. Spence  OCP  MCSE 
        MCP A+ RAPTOR CNA Oracle DBA 
        Phone: (978) 322-5744 <FONT 
        face=Arial size=2>Fax:    (707) 885-2275 
        Fuelspot <FONT face=Arial 
        size=2>73 Princeton Street North, 
        Chelmsford 01863   
        
        -----Original Message----- <FONT 
        face=Arial size=2>Sent: Tuesday, August 07, 2001 12:37 PM 
        To: Multiple recipients of list 
        ORACLE-L 
        Hi there, 
        Unfortunately you can't just throw things 
        like FIRST_ROWS at a select statement 
        to make it run faster. The person who wrote the statement should 
        have taken into account such things 
        as: 
        a) Size of tables, ordering, which is the 
        driving table b) Indexes, are the 
        being used, running statements through explain plan will 
        show you where the bottlenecks are...failing 
        that TKPROF will identify potential 
        problem areas with your database. c) 
        Size of queries, joins etc. 
        Too many developers do not write code to be 
        efficient, as long as it returns the 
        correct values it doesn't matter whether it takes 2 minutes or  
        2 hours... 
        I've spent a number of 
        hours/days/weeks/months tidying up poorly optimised <FONT 
        face=Arial size=2>code, it's an absolute nightmare for the DBAs and it 
        doesn't look good on the 
        developers. 
        Cheers, <FONT face=Arial 
        size=2>Kev. (a cheesed off 
        developer...not a DBA!) 
        __________________ 
        Kevin Thomas <FONT face=Arial 
        size=2>Technical Analyst Deregulation 
        Services Calanais Ltd. 
        Tel: 0141 568 2314 <FONT 
        face=Arial size=2>Fax: 0141 568 2366 <FONT face=Arial 
        size=2><A target=_blank 
        href="http://www.calanais.com">http://www.calanais.com 
        
        -----Original Message----- <FONT 
        face=Arial size=2>Sent: 07 August 2001 17:06 <FONT face=Arial 
        size=2>To: Multiple recipients of list ORACLE-L 
        Morning listers! 
        I am having performance problems with this 
        database, transactions ar running <FONT face=Arial 
        size=2>very slow and I am not sure if I have choose the right optimizer 
        mode... 
        AIX 4.2.1, Oracle 7.3.4, 30 GB Database, 1 GB 
        Real Memory, 500 MB SGA, 70 
        concurrent users, mostly OLTP transactions. 
        I have tunned init parameters the best I can 
        we the resources I have: 
        db_files = 70 <FONT face=Arial 
        size=2>db_writers = 4 <FONT face=Arial 
        size=2>db_file_multiblock_read_count = 16 <FONT face=Arial 
        size=2>db_block_buffers = 57600 <FONT face=Arial 
        size=2>db_block_size = 8192 <FONT face=Arial 
        size=2>shared_pool_size = 157286400 <FONT face=Arial 
        size=2>processes = 200 dml_locks = 
        1500 log_buffer = 655360 
        sequence_cache_entries = 800 
        sequence_cache_hash_buckets = 89 
        log_checkpoint_interval = 80000 
        optimizer_mode=CHOOSE <FONT 
        face=Arial size=2>session_cached_cursors =  300 <FONT 
        face=Arial size=2>sort_area_size=1048576 <FONT face=Arial 
        size=2>hash_area_size=262144 <FONT face=Arial 
        size=2>hash_multiblock_io_count=4 <FONT face=Arial 
        size=2>hash_join_enabled=TRUE <FONT face=Arial 
        size=2>always_anti_join=HASH <FONT face=Arial 
        size=2>job_queue_processes=8 
        35 rollback segments, inital 1MB, next 1MB, 
        optimal 30 MB 8 multiplexed redologs, 
        30 MB each 
        I tried FIRST_ROWS, analyzing the tables, but 
        users claimed that performance was 
        getting worse, so I chaged it to Choose. Always analyzing the 
        tables.... 
        but, everytime I analyze the tables, 
        performance gets worse. Is this a 
        normal behavior? 
        Any advice will be welcome! 
        thanks is advance, 
        Saludos, <FONT face=Arial 
        size=2>Veronica Levin Enriquez <FONT face=Arial 
        size=2>Administrador AIX Compañía 
        Cervecera de Nicaragua -- 
        Please see the official ORACLE-L FAQ: 
        <A target=_blank 
        href="http://www.orafaq.com">http://www.orafaq.com <FONT 
        face=Arial size=2>-- Author: Veronica 
        Levin   INET: 
        vlevin_at_victoria.com.ni 
        Fat City Network Services    
        -- (858) 538-5051  FAX: (858) 538-5051 <FONT face=Arial 
        size=2>San Diego, California        
        -- Public Internet access / Mailing Lists <FONT face=Arial 
        size=2>-------------------------------------------------------------------- 
        To REMOVE yourself from this mailing list, 
        send an E-Mail message to: 
        ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
        the message BODY, include a line 
        containing: UNSUB ORACLE-L (or the name of <FONT face=Arial 
        size=2>mailing list you want to be removed from).  You may also 
        send the HELP command for other 
        information (like subscribing). -- 
        Please see the official ORACLE-L FAQ: 
        <A target=_blank 
        href="http://www.orafaq.com">http://www.orafaq.com <FONT 
        face=Arial size=2>-- Author: Thomas, 
        Kevin   INET: 
        Kevin.Thomas_at_calanais.com 
        Fat City Network Services    
        -- (858) 538-5051  FAX: (858) 538-5051 <FONT face=Arial 
        size=2>San Diego, California        
        -- Public Internet access / Mailing Lists <FONT face=Arial 
        size=2>-------------------------------------------------------------------- 
        To REMOVE yourself from this mailing list, 
        send an E-Mail message to: 
        ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
        the message BODY, include a line 
        containing: UNSUB ORACLE-L (or the name of <FONT face=Arial 
        size=2>mailing list you want to be removed from).  You may also 
        send the HELP command for other 
        information (like subscribing). -- 
        Please see the official ORACLE-L FAQ: 
        <A target=_blank 
        href="http://www.orafaq.com">http://www.orafaq.com <FONT 
        face=Arial size=2>-- Author: 
        Christopher Spence   INET: 
        cspence_at_FuelSpot.com 
        Fat City Network Services    
        -- (858) 538-5051  FAX: (858) 538-5051 <FONT face=Arial 
        size=2>San Diego, California        
        -- Public Internet access / Mailing Lists <FONT face=Arial 
        size=2>-------------------------------------------------------------------- 
        To REMOVE yourself from this mailing list, 
        send an E-Mail message to: 
        ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
        the message BODY, include a line 
        containing: UNSUB ORACLE-L (or the name of <FONT face=Arial 
        size=2>mailing list you want to be removed from).  You may also 
        send the HELP command for other 
        information (like subscribing). -- 
        Please see the official ORACLE-L FAQ: 
        <A target=_blank 
        href="http://www.orafaq.com">http://www.orafaq.com <FONT 
        face=Arial size=2>-- Author: Thomas, 
        Kevin   INET: 
        Kevin.Thomas_at_calanais.com 
        Fat City Network Services    
        -- (858) 538-5051  FAX: (858) 538-5051 <FONT face=Arial 
        size=2>San Diego, California        
        -- Public Internet access / Mailing Lists <FONT face=Arial 
        size=2>-------------------------------------------------------------------- 
        To REMOVE yourself from this mailing list, 
        send an E-Mail message to: 
        ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
        the message BODY, include a line 
        containing: UNSUB ORACLE-L (or the name of <FONT face=Arial 
        size=2>mailing list you want to be removed from).  You may also 
        send the HELP command for other 
        information (like subscribing). -- 
        Please see the official ORACLE-L FAQ: 
        <A target=_blank 
        href="http://www.orafaq.com">http://www.orafaq.com <FONT 
        face=Arial size=2>-- Author: 
        Christopher Spence   INET: 
        cspence_at_FuelSpot.com 
        Fat City Network Services    
        -- (858) 538-5051  FAX: (858) 538-5051 <FONT face=Arial 
        size=2>San Diego, California        
        -- Public Internet access / Mailing Lists <FONT face=Arial 
        size=2>-------------------------------------------------------------------- 
        To REMOVE yourself from this mailing list, 
        send an E-Mail message to: 
        ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
        the message BODY, include a line containing: 
        UNSUB ORACLE-L (or the name of 
        mailing list you want to be removed from).  You may 
        also send the HELP command for other 
        information (like subscribing).

Received on Wed Aug 08 2001 - 11:33:47 CDT