Re: DBMS_STATS.GATHER_TABLE_STATS and ANALYZE TABLE

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!
-LI

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;
>> /
>>
>

>

>

>

>

>

--
http://www.freelists.org/webpage/oracle-l