DBMS_STATS.GATHER_TABLE_STATS and ANALYZE TABLE
Date: Fri, 13 Feb 2009 09:43:34 -0600
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:
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;
I've also tried estimate_percent of 5, 10, 15, 100 with no luck. Version: Oracle 10.2.0.3 64-bit on Linux X86-64
Anybody has any comments on this? Any suggestion would be greatly appreciated!