CBO doesn't work!

From: <zpayne_at_my-deja.com>
Date: Tue, 09 Jan 2001 23:22:58 GMT
Message-ID: <93g6gf$g9j$1@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/ Received on Tue Jan 09 2001 - 17:22:58 CST