Re: DBMS_STATS runs for hours and creates no stats
From: Steve Howard <stevedhoward_at_gmail.com>
Date: Wed, 4 Aug 2010 12:41:36 -0700 (PDT)
Message-ID: <77b7c260-a270-4c97-a935-a93e436081d6_at_z10g2000yqb.googlegroups.com>
On Jul 30, 11:44 am, Dan <daniel.oster..._at_visaer.com> wrote:
> 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
Date: Wed, 4 Aug 2010 12:41:36 -0700 (PDT)
Message-ID: <77b7c260-a270-4c97-a935-a93e436081d6_at_z10g2000yqb.googlegroups.com>
On Jul 30, 11:44 am, Dan <daniel.oster..._at_visaer.com> wrote:
> 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
I would exec
dbms_monitor.session_trace_enable(binds=>true),waits=>false) in your
session from which you are running the stats gathering.
If you have legal access to the AWR views, I would also find your session_id in the dba_hist_active_sess_history view, as that may provide some past clues for problems. Query for action = 'GATHER_STATS_JOB' during the problem window. If that isn't set (it may be set by the stock scheduler job that ships with the database), query for program like 'sqlplus%', or whatever program you used to run the gathering. Received on Wed Aug 04 2010 - 14:41:36 CDT