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

From: <oracle10_at_gmail.com>
Date: 21 Sep 2006 16:58:11 -0700
Message-ID: <1158883091.341006.37170@b28g2000cwb.googlegroups.com>

Thank you Jonathan for reply

I actually only changed column and table names. I didn't remove anything. The main thing here is CBO uses small table as driver in FIRST_ROWS but not ALL_ROWS.

How do I fabricate stats?

What about bucket size? would it help to say SIZE 20 instead of SIZE 1?

I should read the book first :-)

Thanks

Jonathan Lewis wrote:
> <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
Received on Thu Sep 21 2006 - 18:58:11 CDT