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

On Jul 30, 10:58*am, Dan <daniel.oster... (AT) visaer (DOT) com> wrote:
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

On Jul 30, 11:26*am, ddf <orat... (AT) msn (DOT) com> wrote:
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

On Jul 30, 11:44*am, Dan <daniel.oster... (AT) visaer (DOT) com> wrote:
I would exec
dbms_monitor.session_trace_enable(binds=>true),wai ts=>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.