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

<oracle10_at_gmail.com> wrote in message
news:1158861514.550243.206350_at_m73g2000cwd.googlegroups.com...
> 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)
>

Looks like you're running 8i there, and have simplified the query a little. Should we assume you have some extra predicates on the small table, and that the primary key on the parent is a multi-column index of which the small table primary key is the first column ?

The missing bit of the puzzle is the plan before you collected the statistics. Both plans seem to be good plans, according to the intention stated by the first_rows/all_rows hints and the statistics that are appearing.

It is possible that your old statistics were sufficient misleading that the optimizer got a completely different impression either of how many times it would exercise the indexed access path or how much it would cost to use that index once.

It is a sad fact of optimization that there are always cases where the optimizer really hasn't got a chance of coming to the right strategy from the available information. In those cases, you need to fabricate suitable statistics, or use the right hints to make the desired path appear. If you have to fabricate statistics, you may find that a one-off design will work, but you may find that you have to use a program to keep moving the stats in line with the data and queries.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html