Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: CBO picks wrong plan after analyze. FIRST_ROWS hint is workaround. ALL_ROWS causes wrong plan

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

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 23 Sep 2006 08:13:27 +0200
Message-ID: <i0k9h2hpic3fdskl313oj9e8fkul9v72n9@4ax.com>


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
Received on Sat Sep 23 2006 - 01:13:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US