RE: cost based optimizer

[Toepke, Kevin M]

Title: cost based optimizer

John:

 

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.

 

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.

 

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.

 

HTH

Kevin

-----Original Message-----
From: Baylis, John [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 08, 2002 1:08 PM
To: Multiple recipients of list ORACLE-L
Subject: 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 */
PKTS_ICMSSHDR from ICMSSHDR
where (FK_IX_ICMSSHDR_DELV='x' and (SHDR_DELV_WHSE_CODE<'86'));

Execution Plan
----------------------------------------------------------
   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)
   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
----------------------------------------------------------
   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
Database Administrator
Canadian Forest Products Ltd.
Vancouver B.C. Canada

(604) 697-6476 (Office)
(604) 313-6054 (Cell)