Re: DBMS_STATS runs for hours and creates no stats

> I'm running into an occasional issue with my stats gathering in Oracle
> 10.2. I run the following commands:
>
> DEFINE SCHEMA = "IBXREF1"
> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> '&SCHEMA', tabname=>
> 'HOUSEHOLD_DATA', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
>
> This is a large table, 135G, and the stats gathering runs for 5-6 hours
> on our server. It completes as normal with no errors, but when you look
> at the table afterward there are no stats for it. The
> USER_TABLE.LAST_ANALYZED is blank.
>
> This command is from a script that I run every month, and most months
> are fine, so it's not a syntax thing. This just seems to happen once
> every 2 or 3 months on one of my tables.
>
> Any ideas why this is happening and what I can do to fix it?
>
> Dan

If I am allowed to guess, which is pretty much all I can do without having access to your system, I'd say that DBMS_STATS.AUTO_SAMPLE_SIZE is the problem. Try using a fixed percentage, say 2% or try turning block sampling on.

-- 
http://mgogala.byethost5.com