Re: DBMS_STATS runs for hours and creates no stats

From: joel garry <joel-garry_at_home.com>
Date: Fri, 30 Jul 2010 10:44:36 -0700 (PDT)
Message-ID: <f45218e3-e0ea-4644-a25f-6b67cc5f20b2_at_o7g2000prg.googlegroups.com>

On Jul 30, 7:19 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

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

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

Received on Fri Jul 30 2010 - 12:44:36 CDT