Re: DBMS_STATS.GATHER_TABLE_STATS and ANALYZE TABLE
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- 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:
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
