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: Christopher Spence <cspence_at_FuelSpot.com>
Date: Wed, 08 Aug 2001 08:37:12 -0700
Message-ID: <F001.003644CA.20010808081535@fatcity.com> 







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> 


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.


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 <FONT 
    face=Arial size=2>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 <FONT 


    face=Arial size=2>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 <FONT 
    face=Arial size=2>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 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 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 <FONT 


    face=Arial size=2>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 <FONT 
    face=Arial size=2>have taken into account such things as: 


    
  
  1.   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 <FONT 
    face=Arial size=2>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 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 <FONT 

    face=Arial size=2>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 


Received on Wed Aug 08 2001 - 10:37:12 CDT












Original text of this message













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