Re: DBMS_STATS.GATHER_TABLE_STATS and ANALYZE TABLE
- From: Joerg Jost <joerg.jost@xxxxxxxxxxxx>
- To: litanli@xxxxxxxxx
- Date: Fri, 13 Feb 2009 17:25:12 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
