CBO picks wrong plan after analyze. FIRST_ROWS hint is workaround. ALL_ROWS causes wrong plan

Giganews Newsgroups
Subject: CBO picks wrong plan after analyze. FIRST_ROWS hint is workaround. ALL_ROWS causes wrong plan
Posted by:  oracle…@gmail.com
Date: 21 Sep 2006

We have a query joining large and small tables. Small table has 130K
rows. LargeTable has 4M (4 million) rows
Both have PK column called pk_id with Index on PK

    SELECT ST.pk_id
      FROM smalltable ST, largetable SL
    WHERE ST.pk_id = LT.pk_id
      AND LT.code_tp = 'maybe'
      AND LT.trans_date IS NULL
      AND LT.status <> 'Incomplete'
Table and Index were not analyzed for 6 months and CBO was picking
correct plan where small table drives large table
i.e. full scan on small table
for each row in smalltable, oracle uses PK index on large table to
locate matching join row on largetable

This happened with all_rows or first_rows optimizer goal/mode

Few days ago, we updated / analyzed all tables and indexes

After analysis CBO started picking wrong plan and do FULL scan on large
and small tables both and do Hash Join instead of Nested Loops in case
of ALL_ROWS
FIRST_ROWS still works after analysis

Are there cases when following Oracle Recommendation by frequent
ANALYZE/stats gathering causes CBO to go astray?

Thanks

Good Plan:
Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=170900 Car
          d=42699 Bytes=1067475)

  1    0  NESTED LOOPS (Cost=170900 Card=42699 Bytes=1067475)
  2    1    TABLE ACCESS (FULL) OF 'SMALLTABLE' (Cost=104 Car
          d=42699 Bytes=384291)
  3    1    TABLE ACCESS (BY INDEX ROWID) OF 'LARGETABLE' (Cost=4
Card
          =3830901 Bytes=61294416)
  4    3      INDEX (RANGE SCAN) OF 'PK_ID' (UNIQUE) (Cost=3 Card
          =3830901)

Bad Plan:
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=19388 Card=4
          2699 Bytes=1067475)

  1    0  HASH JOIN (Cost=19388 Card=42699 Bytes=1067475)
  2    1    TABLE ACCESS (FULL) OF 'SMALLTABLE' (Cost=104 Car
          d=42699 Bytes=384291)
  3    1    TABLE ACCESS (FULL) OF 'LARGETABLE' (Cost=15214
Card=38309
          01 Bytes=61294416)

Replies