Re: CBO doesn't work!

Hi.

  What Oracle version you are using?

  Try setting OPTIMIZER_INDEX_COST_ADJ = 10 and   OPTIMIZER_INDEX_CACHING = 90 ( ??? check it, because I don't remember    the exact name ).

  HTH. Michael.

In article <93g6gf$g9j$1_at_nnrp1.deja.com>,   zpayne_at_my-deja.com wrote:
> 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/
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 11 2001 - 10:01:43 CST