Re: table stats

The default statistics collection for dbms_stats is NOT to collect histograms. (Connor's comment about 'for all columns size 1' is correct - but this means no histogram).

If you check user_tab_histograms after a default collection, you will find 2 entries per column, but this isn't a 'real' histogram, it's just a way of representing the low/high.

Possibly your 'takes ages' using dbms_stats is because the default for dbms_stats is a COMPUTE - try setting estimate_percent
to a value that matches your analyze.

I do have a vague memory of seeing a bug report about dbms_stats taking longer than the equivalent analyze though - even when doing (notionally) the same thing. I don't recall any details though.

As far as I know, for CBO purposes you can mix dbms_stats with analyze perfectly safely. The relevant figures are usually identical once you've worked out the exact matching set of parameters to dbms_stats.

-- 
Regards

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

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

April 2004 Iceland  http://www.index.is/oracleday.php
June  2004      UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"Daud" <daud11_at_hotmail.com> wrote in message
news:f0bf3cc3.0404212258.282de059_at_posting.google.com...


> Yes, the default for method_opt is not null. But if I left it as

> default, it takes ages to complete as it collects too much statistics

> for histograms and stuff like that. Which option of method_opt is the

> same as the one from analyze table command?

>

> One other question. Why can't I mixed analyze table with dbms_stats?

> The current tables have been analyzed using 'analyze table ...' but I

> would slowly like to start using dbms_stats.

>

> thanks

> Daud