DBMS_STATS runs for hours and creates no stats

From: Dan <daniel.ostertag_at_visaer.com>
Date: Fri, 30 Jul 2010 07:58:08 -0700 (PDT)
Message-ID: <ebe88f35-7df7-4e41-a6c8-5fee799e80d3_at_m1g2000yqo.googlegroups.com>

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 Received on Fri Jul 30 2010 - 09:58:08 CDT