Re: Dbms_stats Analyze Data Dictionay ?

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

Actually since version 7.3 any hint in an SQL statement, except for the RULE hint, causes the CBO to be invoked for the statment. In version 8.1 some of Oracle's data dictionary (recursive) queries are hinted, so the CBO is and has been used on some rdbms dictionary queries for a while. I think I first noticed the hints in version 8, but it gets hard to remember.

Just adding to the discussion -- Mark D Powell -- Received on Mon May 26 2003 - 19:28:12 CDT