Re: GLOBAL_STATS

Holger Baer <holger.baer_at_science-computing.de> wrote in message news:<ckj5pl$mp$1_at_news.BelWue.DE>...
> Martin Preiss wrote:
> > Hello,
> >
> > after some CTAS-operations on my Oracle 10.1.0.2 Server (SE) i see
> > that there are many entries in user_tables with GLOBAL_STATS = 'NO'.
> > The database reference tells me:
> >
> > GLOBAL_STATS:
> > For partitioned tables, indicates whether statistics were collected
> > for the table as a whole (YES) or were estimated from statistics on
> > underlying partitions and subpartitions (NO)
> >
> > Since these tables are not partitioned (the feature is not included in
> > SE) i have no idea how they got the flag. The problem is that the
> > tables seem to be excluded from the automatic statistic gathering. Is
> > it possible to change the GLOBAL_STATS-flag? (i didn't find a fitting
> > ALTER TABLE command) Or have i to use dbms_stats now to get the
> > statistics? And what's the reason for the GLOBAL_STATS='NO'-entries
> > for non-partitioned tables?
> >
> > Kind regards
> >
> > Martin Preiss
>
> The automatic statististic gathering only gatheres statistics for tables
> that
> a) have monitoring set to yes (check dba_tables for those tables)
> b) changed enough so the gather stale option of dbms_stats kicks in
>
> I'm a bit missing why you conclude that the value for GLOBAL_STATS influences
> automatic statistics gathering. It should be the other way round. Perhaps
> you care to sched some light on your findings?
>
> Cheers,
>
> Holger

Hi Holger,

thank you for your comment.

it seems that my conclusions were indeed a little bit fragile ...

The monitoring was set to yes (i think that's default in 10g), the tables had been changed, and there were no statistics - while the system had statistics for other CTAS-tables that had been created about the same time. The only other difference i could see in DBA_TABLES was the different entry for GLOBAL_STATS. So i assumed that the missing statistics and the GLOBAL_STATS=NO-entry were linked together, and that there were no statistics because of the GLOBAL_STATS-Setting - which is wrong as i see now: today i have statistics on most of the tables and the GLOBAL_STATS have changed to yes. I guess the GATHER_STATS_JOB did his job in the maintenance window. So everything is fine now.

But i still don't know what's the meaning of GLOBAL_STATS=NO for a non-partitioned table. The database reference seems to say that it's only of interest for partitioned tables. Or do i misread the documentation?

Kind regards

Martin Preiss Received on Thu Oct 14 2004 - 02:59:22 CDT