Re: DBMS_STATS.GATHER_TABLE_STATS and ANALYZE TABLE
Date: Fri, 13 Feb 2009 10:44:02 -0600 (CST)
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:
> 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.