Re: DBMS_STATS.GATHER_TABLE_STATS and ANALYZE TABLE

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Fri, 13 Feb 2009 10:44:02 -0600 (CST)
Message-ID: <398497d6adf5233bf5dd3c5785dda37c.squirrel_at_society.servebeer.com>

Even if one does not tell analyze to collect histograms using the "FOR ALL [INDEXED] COLUMNS" syntax, it does populate some information in the histograms view. This differs from the way DBMS_STATS works. I posted on this a few years ago, but I'm not able to find it at the moment. That, and playing in SQL Server all week [groan] have me batty.

I think a good place to start would be to have the OP post the ANALYZE statement used so we can make a comparison between that and the DBMS_STATS. Other useful information easily obtained would be the differences in the columns from USER_TABLES after each stats method.

As far as the collection and use of histograms goes, I'll defer to the masters on the list -- even after reading Jonathan's book I don't mess with them enough to keep in tune with 'em. :) See also:

http://www.freelists.org/post/oracle-l/density-calculation-when-histograms-are-involved,3

Rich

> probably a problem with Histograms? IIRC analyze does not generate
> Histograms per default. DBMS_STATS do.
>
> The Parameter method_opt defaults to FOR ALL COLUMNS SIZE AUTO which
> means, Oracle decides whether to collect histograms or not on the
> workload of the column (see Documentation for Details)
>
> For a deeper look into this the explain plan of both Queries will help.
> Also the gathered statistics for both cases.
>
> by
>
> Jörg

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 13 2009 - 10:44:02 CST