RE: cost based optimizer

From: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Fri, 08 Feb 2002 11:29:34 -0800
Message-ID: <F001.0040A85D.20020208104828@fatcity.com>

<SPAN

class=806543518-08022002>John:
<SPAN

class=806543518-08022002> 
The
Cost of a query is based on Oracle's estimation of the number of datablocks that will have to be read in order to resolve the query. The Index-access Plan's cost is higher because Oracle is estimating that it will have to process more datablocks to return the requested rows.
<SPAN

class=806543518-08022002> 
<SPAN

class=806543518-08022002>Oracle's optimizer isn't perfect. I have found that the Cost of a query is a reasonable measure of relative performance of queries only when the estimated number of rows (Card=999) is reasonably accurate.
<SPAN

class=806543518-08022002> 
You
didn't say what version of Oracle you are using. If you are using Oracle 8i or above, you can use the Plan Stability feature to specify the explain plan for the query without using hints.
<SPAN

class=806543518-08022002> 
<SPAN

class=806543518-08022002>HTH
<SPAN

class=806543518-08022002>Kevin

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Baylis, John   [mailto:JBaylis_at_mail.canfor.ca]Sent: Friday, February 08, 2002 1:08   PMTo: Multiple recipients of list ORACLE-LSubject: cost   based optimizer
  I have a table of 500,000 records that is analyzed   and contains several indexes that are analyzed.   Can someone explain to me why the cost without a   hint is much lower than the cost with a hint?   Using a hint is 10 times faster than without a hint   even though the cost is much higher.
  Since this is a third party app, I cannot add   hints. What aoptions do I have?
  select  /*+ Index("ICMSSHDR" XSKSHDRS181M1)   Use this index XSKSHDRS181M1 */ <FONT face=Arial   size=2>PKTS_ICMSSHDR from ICMSSHDR where   (FK_IX_ICMSSHDR_DELV='x' and (SHDR_DELV_WHSE_CODE<'86'));   Execution Plan <FONT face=Arial

  size=2>---------------------------------------------------------- 

     0     
  SELECT STATEMENT Optimizer=CHOOSE (Cost=213529 Card=228346   Bytes=12102338)   
  1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ICMSSHDR'   (Cost=213529 Card=228346 Bytes=12102338) <FONT face=Arial   size=2>   2    1     INDEX (RANGE
  SCAN) OF 'XSKSHDRS181M1' (NON-UNIQUE) (Cost=2346 Card=228346)   select  PKTS_ICMSSHDR from ICMSSHDR
  where (FK_IX_ICMSSHDR_DELV='x' and
  (SHDR_DELV_WHSE_CODE<'86'));
  Execution Plan <FONT face=Arial

  size=2>---------------------------------------------------------- 

     0     
  SELECT STATEMENT Optimizer=CHOOSE (Cost=3526 Card=228346   Bytes=12102338)   
  1    0   TABLE ACCESS (FULL) OF 'ICMSSHDR' (Cost=3526   Card=228346 Bytes=12102338)
  Thanks
  John Baylis <FONT

  face=Arial color=#0080ff size=1>D<FONT face=Arial color=#0080ff 
  size=1>atabase Administrator <FONT face=Arial color=#0080ff 
  size=1>Canadian Forest Products Ltd. <FONT face=Arial color=#0080ff 
  size=1>Vancouver B.C. Canada 

  (604) 697-6476
  (Office) (604)
  313-6054 (Cell) Received on Fri Feb 08 2002 - 13:29:34 CST