Re: DBMS_STATS.GATHER_TABLE_STATS and ANALYZE TABLE
Date: Fri, 13 Feb 2009 11:40:22 -0600
You are right, it was the histograms. The query now runs fine after I use parameter method_opt => 'FOR ALL COLUMNS SIZE 1'. CBO did generate different execution plan with or without this parameter.
Thank you all for your input!
On Fri, Feb 13, 2009 at 10:25 AM, Joerg Jost <joerg.jost_at_unitrade.com> wrote:
> 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.
> Received on Fri Feb 13 2009 - 11:40:22 CST