Re: DBMS_STATS runs for hours and creates no stats
From: Dan <daniel.ostertag_at_visaer.com>
Date: Fri, 30 Jul 2010 08:44:13 -0700 (PDT)
Message-ID: <79a63109-f191-4846-85fc-a7d3f997f8c6_at_m1g2000yqo.googlegroups.com>
On Jul 30, 11:26 am, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 30 Jul 2010 08:44:13 -0700 (PDT)
Message-ID: <79a63109-f191-4846-85fc-a7d3f997f8c6_at_m1g2000yqo.googlegroups.com>
On Jul 30, 11:26 am, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
David,
I'm on version 10.2.0.3.0. I've looked at logs and nothing shows an error. The command itself didn't show an error so I'd be surprised if a log showed an error.
Dan Received on Fri Jul 30 2010 - 10:44:13 CDT