Re: dbms_stats default

John,

I think I missed the point of your original question as I tend to read the expression 'column stats' to mean 'multi-bucket histograms'.

I think the detail that you need to know is that whether you

    analyze table X compute statistics
or execute

    dbms_stats.gather_table_stats(..... table_name =>'X', ..)

In both cases, you get a 'one-bucket histogram' on each column. If you check the trace files, both calls will update the hist_head$ table, but not the histgrm$ table.

The other point -- the ANALYZE command doesn't appear to run any SQL to do the necessary sorting and counting, whereas the actual count, count(distinct) max() min() SQL appears in the trace for dbms_stats - I don't think this is an issue: it is work that has to be done somehow to get the results - the fact that the (deprecated) analyze command did it without admitting it is (probably) not a significant point.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


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





John Darrah wrote in message ...


>Sorry for the vague response.  The 3X increase was on the analyze

operation


>not queries against the table.  I thought there was a difference

between


>gathering column stats and creating histograms.  I thought that

gathering


>stats with a size of 1 meant that I would only be populating the

columns in


>the col$ table and not be creating histograms.  It turns out that

gathering


>for columns does create a histogram on that column.  It seems strange

that


>the default behavior of the dbms_stats package would be to create a

one


>bucket histogram for every column in the table but I guess the

rational is


>to err on the side of too much information rather than not enough.  I

wasn't


>aware that the optimizer was sophisticated enough to order predicates

>according by what will most reduce the row source, that's good to

know.


>Would your strategy be to gather table stats, without gathering

column stats


>and then selectively gather stats on columns with appropriately sized

>histograms? The only bad thing I can see with this method is that 1)

the


>avg_row_len of the table may be inaccurate and 2) there will be

columns in


>the table with no stats which could pose a problem for future

queries.


>Finally, I just wanted to say how much I enjoyed your seminar.  Its

made me


>want to go back and take a more in-depth look at a lot of the things

I


>thought I understood.

>

>Thanks for the reply,

>

>John