Re: DBMS_STATS vs. manual ANALYZE ... desupported?

One reason why it's "a good thing" to use dbms_stats instead of analyze is that some of the results are different, and the optimizer is likely to be engineered to assume that the results it uses come from dbms_stats.

For example: avg_col_len in user_tab_columns is one larger after a dbms_stats call than it is after an analyze - and it is a figure used in the calculation of the cost of a hash join or a sort. Changing from analyze to dbms_stats could (at the boundary cases) change some of your execution paths.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 2nd





"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
news:cie8nk$t7a_at_odah37.prod.google.com...


> Howard J. Rogers wrote:

> > Niall Litchfield wrote:

> > >> Instead, they're saying if we don't use dbms_stats, we're going

> > >> straight to hell.

> > >

> > > I'd like to see the reference.

> >

> > It *is* strongly recommended to stop using 'analyze table EMP'

> statements.

> > And for the sort of good reasons I mentioned.

>


> What I was trying to get at was that I thought Domenic's question was

> good because I have seen the advance warning that ANALYZE will be

> removed at a later date, but I haven't seen much *from Oracle* saying

> why. I agree with all your statements by the way - I should have

> explained what I meant by flexible and didn't.

>


> --

> Niall Litchfield

> Oracle DBA

> http://www.niall.litchfield.dial.pipex.com

>