From: Rich Jesse <>
Date: Fri, 13 Feb 2009 10:44:02 -0600 (CST)
Message-ID: <>

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:,3


> 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

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

Original text of this message