Re: Dbms_stats Analyze Data Dictionay ?

No. Let's be clear:

The rule-based optimizer goes in 10i (though support in various forms continues). Therefore, you are still supposed to use rule-based on the data dictionary in 9i.

No doubt some sort of pre-10i migration to the CBO has been made in 9i, which is probably why it isn't so painful to make the switch as it was in earlier versions. They aren't going to unleash a CBO-accessed dictionary on an unsuspecting world (without having tested it on an unsuspecting 9i world).

In a similar way, if Dictionary Managed Tablespace is disappearing in 10i, or 11i, it makes sense to switch to an LMT SYSTEM tablespace a bit earlier than that so the bugs can be ironed out before no-one has a choice in the matter.

But a CBO SYSTEM tablespace still isn't recommended in 9i.

Will it do harm? Probably.

Will it do the sort of harm that you would have experienced in 8.0 or 8i? Probably not: a lot of the code that 10i will contain must have already crept into 9i (version 2, at any rate).

Regards
HJR "Ryan" <rgaffuri_at_cox.net> wrote in message news:cB8Aa.97348$823.47068_at_news1.east.cox.net...
>
> "Jack Silvey" <depifster_at_yahoo.com> wrote in message
> news:25c7944d.0305251049.4b6064fc_at_posting.google.com...
> > Ryan,
> >
> > Without stats on the DD, your recursive data dictionary queries run
> > under rule based optimization, which is the intent in earlier versions
> > of Oracle (<9i).
> >
> > If you analyze the DD, the optimizer will try to use cost based
> > optimization in dictionary queries. This can be a disaster since the
> > dictionary was designed long ago to run under rule. The CBO could
> > start making very bad choices about execution plans and this can
> > really slow down all your users (sort segment allocation, query
> > parsing, space allocation, stats gathering, etc.)
> >
> > A good thing might be to test on your dev/qa systems.
> >
> > hth,
> >
> > Jack Silvey
> > warehouse DBA somewhere in Texas
> >
> >
> >
> > > acceptable? does it help at all or its just not a bad thing?
>
> pre-9i? Wait a second, everything I have read and seen has said that 9i
> still uses the RBO on the system tablespace? Its not until version 10 that
> the CBO will be used on the data dictionary.
>
> Or have I been reading the wrong material?
>
>
Received on Mon May 26 2003 - 05:44:59 CDT