DBMS_STATS runs for hours and creates no stats

From: Dan <daniel.ostertag_at_visaer.com>
Date: Fri, 30 Jul 2010 07:19:13 -0700 (PDT)
Message-ID: <c5f6184d-dcda-42c9-9eaa-4beecf25c27b_at_5g2000yqz.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:19:13 CDT