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:1158883091.341006.37170_at_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
>

I am puzzled about two things:

    Why, when you have no filter predicates on     small_table, does the optimizer think that a     full scan will return 42,699 out of the 130K     that are supposed to be there.

    Why, when you seem to be suggesting that the     PK_ID is the primary key on the large table,     does a join condition pk_id = {join value}     result in a range scan of the unique index.

When you join, do you get only 126 rows because the join fails on the pk_id, or because the filter predicates on large_table eliminate rows after they have successfully matched.

What fraction of the large table matches the conditions
>> > AND LT.code_tp = 'maybe'
>> > AND LT.trans_date IS NULL
>> > AND LT.status <> 'Incomplete'

The optimizer seems to think it is nearly 100%, since its full scan cardinality is 3.8M, compared to your figure of 4M for the row count. How accurate is that ? Check what the column stats look like.

Don't fiddle with sample sizes and histograms until you can tell which columns are reporting statistics that are misleading the optimizer.

-- 
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