From: Dan on
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
From: ddf on
On Jul 30, 10:58 am, Dan <daniel.oster...(a)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
From: Dan on
On Jul 30, 11:26 am, ddf <orat...(a)msn.com> wrote:
> On Jul 30, 10:58 am, Dan <daniel.oster...(a)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
From: Mladen Gogala on
On Fri, 30 Jul 2010 07:19:13 -0700, Dan 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

If I am allowed to guess, which is pretty much all I can do without
having access to your system, I'd say that DBMS_STATS.AUTO_SAMPLE_SIZE is
the problem. Try using a fixed percentage, say 2% or try turning block
sampling on.



--
http://mgogala.byethost5.com
From: joel garry on
On Jul 30, 7:19 am, Dan <daniel.oster...(a)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

I haven't looked recently, but I have a vague memory that there is a
window in which things can be run (at least when you do this through
EM), so I'm guessing sometimes it takes too long to fit in the window
and dies. What is in the script and how is it run? How is it
logged? Are you sure the default stats gathering is disabled?
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tasks.htm

There are some number of bugs in the MOS bug database if you search
for auto_sample_size, if it isn't a resource problem I speculate there
is some instability you run into under odd conditions.

jg
--
@home.com is bogus.
http://www.sfgate.com/cgi-bin/blogs/scavenger/detail?entry_id=68992