Re: GLOBAL_STATS

• Post for FREE via your newsreader at post.usenet.com ****

Hi Martin

> 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)

Only DBMS_STATS, except in old versions where in some situations ANALYZE is used, generates global statistics. ANALYZE always generate non-global statistics. This is true for non-partitioned tables as well, even if it makes no sense to speak about global statitics in this case...

> Since these tables are not partitioned (the feature is not included in
> SE) i have no idea how they got the flag.

Be carefull, internally, in SE 10g, the partitioning options is used. An example is AWR...

> The problem is that the
> tables seem to be excluded from the automatic statistic gathering.

Mhmm... if accoring to all_tab_modifications at least 10% of the rows has changed or a truncate has been performed, the statistics, in a default configuration, are automatically gathered. Of course if you changed something in the config... the automatic feature can eventually be disabled...

> IS it possible to change the GLOBAL_STATS-flag? (i didn't find a fitting
> ALTER TABLE command)

No. There's no such statement.

> Or have i to use dbms_stats now to get the
> statistics?

Yes. This is the only way to do it.

> And what's the reason for the GLOBAL_STATS='NO'-entries
> for non-partitioned tables?

As I wrote before, ANALYZE is used.

Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=