Prev: DBMS_STATS runs for hours and creates no stats
Next: V$SESSION Status
From: Dan on 30 Jul 2010 16:42
On Jul 30, 1:44 pm, joel garry <joel-ga...(a)home.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Perhaps it runs too long. This takes 6 1/2 hours to run, maybe there
is a bug in the process when it runs too long. Our server has been
running slow this month so this was the longest time yet. I'm running
this manually, not thru EM. We have no scheduled stats done for these
tables so I don't think that would be the cause - but a valid
suggestion to check on.

Regarding the ESTIMATE option, I've run this command probably 100
times exactly the same way with no problems, so I doubt syntax would
be an issue. All valid suggestions though. I'm stumped. In the
meantime I just have to run it again and hope...

Thanks,
Dan
From: Steve Howard on 4 Aug 2010 15:41
On Jul 30, 11:44 am, Dan <daniel.oster...(a)visaer.com> wrote:
> 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

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.
First  |  Prev  | 
Pages: 1 2
Prev: DBMS_STATS runs for hours and creates no stats
Next: V$SESSION Status