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

On 22 Sep 2006 11:15:45 -0700, oracle10_at_gmail.com wrote:

>
>Sybrand Bakker wrote:
>> On 21 Sep 2006 10:58:34 -0700, oracle10_at_gmail.com wrote:
>>
>> >Are there cases when following Oracle Recommendation by frequent
>> >ANALYZE/stats gathering causes CBO to go astray?
>>
>> Yes. That is one of the reasons why you should dbms_stats instead of
>> ANALYZE. Dbms_stats can save the old 'good' statistics to a stats
>> table, so you can easily restore them.
>> Also many people warn against frequent analysis.
>> IMO, I think frequent analysis, without saving the old statistics
>> (which seems to be the norm in my office) is just asking for trouble.
>>
>> --
>> Sybrand Bakker, Senior Oracle DBA
>
>Thanks for advise against frequent analysis. I heard that CBO in 8i
>was inferior to RBO. Is that your experience?
>Thanks

I agree with Daniel. Also my experience is that - when you modify opt_index_caching and index_cost_adj to something sensible
- use the FIRST_ROWS hint and/or put an user in FIRST_ROWS mode (using an after logon trigger)
your plans very much resemble the RULE plans.

In 9i I would dump RBO.

--
Sybrand Bakker, Senior Oracle DBA