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