Re: CBO doesn't work!

Look in particular at your values in
the init.ora file for:

    db_file_multiblock_read_count
    sort_area_size
    hash_area_size

You may have persuaded the CBO that it
is very cheap to scan and sort large tables then hash the results.

Also (for this case) are 304,2876 values for AM_VEND_ID which have a particularly small set of results compared to other values for AM_VEND_ID. If so are you generating column histograms to let Oracle know about special cases ?

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



zpayne_at_my-deja.com wrote in message <93g6gf$g9j$1_at_nnrp1.deja.com>...


>Here is the problem.

>

>In our shop we have several applications, and have been using cost

>based optimizations, and have scripts set up to analyze tables ect.

>

>We have found that when we switch to rule based optimizer it almost

>always returns a different plan, and almost always beats the CBO in

>performance.

>

>Here is an example query with both cost based, and rule plans.

>I'm not looking for an exact answer on this query, but more of an

>overall idea of where to look for answers, because we have about 25 of

>these queries that don't perform well with CBO.

>

>In the below example the CBO takes about 10 sec, and the Rule takes

>about 20 milisecs. In many cases the difference is 5-10 minutes on

>different queries.

>

>  SELECT /*+ RULE */ distinct

>    AM_FAC_PART.VEND_PART_NUM,

>    AM_FAC_PART.PO_PART_DESCR,

>    AM_VEND.VEND_NAME,

>    AM_VEND.AM_VEND_ID

>  FROM

>    AMPROD.AM_FAC_VEND,

>    AMPROD.AM_FAC_PART,

>    AMPROD.AM_VEND

>  WHERE

>    ((AM_FAC_VEND.AM_VEND_ID IN (304,2876)))

>  and

>    AM_FAC_VEND.AM_FAC_VEND_ID = AM_FAC_PART.AM_FAC_VEND_ID and

>    AM_FAC_VEND.AM_VEND_ID = AM_VEND.AM_VEND_ID ;

>

>SELECT STATEMENT Optimizer=HINT: RULE

>  SORT (UNIQUE)

>    NESTED LOOPS

>      NESTED LOOPS

>        NESTED LOOPS

>          TABLE ACCESS (FULL) OF COMP_VENDORS

>          TABLE ACCESS (BY INDEX ROWID) OF AM_FAC_VEND

>            INDEX (RANGE SCAN) OF FAC_VEND_VEND_ID_IDX (NON-UNIQUE)

>        TABLE ACCESS (BY INDEX ROWID) OF AM_VEND

>          INDEX (UNIQUE SCAN) OF PK_AM_VEND (UNIQUE)

>      TABLE ACCESS (BY INDEX ROWID) OF AM_FAC_PART

>        INDEX (RANGE SCAN) OF FAC_PT_FC_VND_ID_IDX (NON-UNIQUE)

>

>  SELECT distinct

>    AM_FAC_PART.VEND_PART_NUM,

>    AM_FAC_PART.PO_PART_DESCR,

>    AM_VEND.VEND_NAME,

>    AM_VEND.AM_VEND_ID

>  FROM

>    AMPROD.AM_FAC_VEND,

>    AMPROD.AM_FAC_PART,

>    AMPROD.AM_VEND

>  WHERE

>    ((AM_FAC_VEND.AM_VEND_ID IN (304,2876)))

>  and

>    AM_FAC_VEND.AM_FAC_VEND_ID = AM_FAC_PART.AM_FAC_VEND_ID and

>    AM_FAC_VEND.AM_VEND_ID = AM_VEND.AM_VEND_ID ;

>

>SELECT STATEMENT Optimizer=CHOOSE (Cost=35724 Card=337641

>Bytes=28024203)

>  SORT (UNIQUE) (Cost=35724 Card=337641 Bytes=28024203)

>    HASH JOIN (Cost=13935 Card=337641 Bytes=28024203)

>      TABLE ACCESS (FULL) OF COMP_VENDORS (Cost=1 Card=82 Bytes=1066)

>      MERGE JOIN (Cost=13472 Card=411757 Bytes=28822990)

>        SORT (JOIN) (Cost=375 Card=28889 Bytes=895559)

>          MERGE JOIN (Cost=375 Card=28889 Bytes=895559)

>            SORT (JOIN)

>              TABLE ACCESS (FULL) OF AM_VEND (Cost=166 Card=16657

>Bytes=383111)

>            SORT (JOIN) (Cost=171 Card=28889 Bytes=231112)

>              TABLE ACCESS (FULL) OF AM_FAC_VEND (Cost=38 Card=28889

>Bytes=231112)

>        SORT (JOIN) (Cost=13097 Card=411757 Bytes=16058523)

>          TABLE ACCESS (FULL) OF AM_FAC_PART (Cost=1072 Card=411757

>Bytes=16058523)

>

>Any help greatly appreciated.

>

>zp

>

>

>Sent via Deja.com

>http://www.deja.com/