Re: DBMS_STATS runs for hours and creates no stats

From: ddf <oratune_at_msn.com>
Date: Fri, 30 Jul 2010 08:26:19 -0700 (PDT)
Message-ID: <b73a9d77-eb5f-42bc-9e73-d46cfb155650_at_g21g2000prn.googlegroups.com>

On Jul 30, 10:58 am, Dan <daniel.oster..._at_visaer.com> wrote:
> 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

10.2 doesn't say much as to which patch level the database is on; report the version to at least 4 numbers (10.2.0.1, 10.2.0.4, as examples). You have checked My Oracle Support for this behaviour? Have you looked in the alert log for messages and/or errors? Any trace files generated at the time the statistics are being gathered?

Please provide as much information as possible to assist those attempting to help you.

David Fitzjarrell Received on Fri Jul 30 2010 - 10:26:19 CDT