Re: DBMS_STATS.GATHER_TABLE_STATS and ANALYZE TABLE

From: Joerg Jost <joerg.jost_at_unitrade.com>
Date: Fri, 13 Feb 2009 17:25:12 +0100
Message-Id: <1234542312.3865.10.camel_at_localhost>

Am Freitag, den 13.02.2009, 09:43 -0600 schrieb Li Li:
> Hi, List,
>
> We have a complex query that just hangs forever with stats gathered by
> DBMS_STATS.GATHER_TABLE_STATS, however, if stats is gathered by
> "Analyze table tab_name estimate statistics" or "Analyze table
> tab_name compute statistics", this query returns back in less than 1
> minute. I tried to find the DBMS_STATS.GATHER_TABLE_STATS equivalence
> of "Analyze table ...", but didn't find any. The procedures I have
> tried are:
>
> BEGIN
> DBMS_STATS.GATHER_TABLE_STATS(ownname => 'owner_name',
> tabname => 'table_name',
> estimate_percent => AUTO_SAMPLE_SIZE,
> degree => DBMS_STATS.AUTO_DEGREE,
> cascade => DBMS_STATS.AUTO_CASCADE);
> END;
> /
>

Hi Li,

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:25:12 CST