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: Optimizer Mode......how to choose the right one?

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

From: Steve Haas <steven.haas_at_snet.net>
Date: Thu, 09 Aug 2001 12:57:57 -0700
Message-ID: <F001.0036598C.20010809085440@fatcity.com>

Does the order of tables also make a
difference with a RULE hint?
 
Steven HaasOpus Consultants, LLC
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

  Sent: Wednesday, August 08, 2001 12:15   PM
  Subject: 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. 
<FONT face=Arial color=#0000ff

  size=2> 
<FONT face=Arial color=#0000ff

  size=2>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.
<FONT face=Arial color=#0000ff

  size=2> 
  CNA
  is Novell Admin, I haven't done it in a while, I originally started as a   Novell Admin.
<FONT face=Arial color=#0000ff

  size=2>Raptor is a firewall product I spent about 4 years working   with.
<FONT face=Arial color=#0000ff

  size=2> 
  "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----- <FONT 
      face=Arial size=1>From:   <FONT face=Arial 
      size=1>Christopher Spence [SMTP:cspence_at_FuelSpot.com] <FONT 
      face=Arial size=1>Sent:   <FONT face=Arial 
      size=1>Wednesday, August 08, 2001 10:27 AM <FONT face=Arial 
      size=1>To:     <FONT face=Arial 
      size=1>Multiple recipients of list ORACLE-L <FONT face=Arial 
      size=1>Subject:        <FONT 
      face=Arial size=1>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 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. <FONT 
      face=Arial size=2>Tel: 0141 568 2314 <FONT face=Arial 
      size=2>Fax: 0141 568 2366 <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 73 
      Princeton Street North, Chelmsford 
      01863   
      -----Original Message----- <FONT 
      face=Arial size=2>Sent: Tuesday, August 07, 2001 12:37 PM <FONT 
      face=Arial size=2>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 <FONT 
      face=Arial size=2>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 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. <FONT 
      face=Arial size=2>Tel: 0141 568 2314 <FONT face=Arial 
      size=2>Fax: 0141 568 2366 <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 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 <FONT 
      face=Arial size=2>sequence_cache_hash_buckets = 89 <FONT 
      face=Arial size=2>log_checkpoint_interval = 80000 <FONT 
      face=Arial size=2>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 mailing list 
      you want to be removed from).  You may also send the HELP 
      command for other information (like 
      subscribing). -- <FONT 
      face=Arial size=2>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 mailing list 
      you want to be removed from).  You may also send the HELP 
      command for other information (like 
      subscribing). -- <FONT 
      face=Arial size=2>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). -- <FONT 
      face=Arial size=2>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 mailing list 
      you want to be removed from).  You may also send the HELP 
      command for other information (like 
      subscribing). -- <FONT 
      face=Arial size=2>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 <FONT 
      face=Arial size=2>also send the HELP command for other information (like 
      subscribing). 
Received on Thu Aug 09 2001 - 14:57:57 CDT

Original text of this message

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