Re: Dbms_stats Analyze Data Dictionay ?

Howard J. Rogers wrote:
>
> 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?
> >
> >

I'm sure there's a metalink note that somewhat contradicts this - along the lines that in 9.2 cbo stats on the dictionary is fully supported. The only issue is deciding whether 'supported' = 'advantageous'

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"