Re: Global Stats

  If I remember right, global stats are computed/inferred from partition level stats if its available. If thats the case, then it may make sense to compute/estimate the stats at the partition level. You also have the advantage of being able to run analyze each partition in parallel.

Regards,
Denny

Quoting "Koivu, Lisa" <Lisa.Koivu_at_efairfield.com>:

> Hi everyone,
>
> Back to the lovely world of Oracle :) I've been reading up on
> statistics.
> Out of the 8.1.7 doco:
> /*
> Partitioned schema objects may contain multiple sets of statistics. They
> can
> have statistics which refer to the entire schema object as a whole
> (global
> statistics), they can have statistics which refer to an individual
> partition, and they can have statistics which refer to an individual
> subpartition of a composite partitioned object.
>
> Unless the query predicate narrows the query to a single partition,
> the
> optimizer uses the global statistics. Because most queries are not
> likely to
> be this restrictive, it is most important to have accurate global
> statistics. Intuitively, it may seem that generating global statistics
> from
> partition-level statistics should be straightforward; however, this is
> only
> true for some of the statistics. For example, it is very difficult to
> figure
> out the number of distinct values for a column from the number of
> distinct
> values found in each partition because of the possible overlap in
> values.
> Therefore, actually gathering global statistics with the DBMS_STATS
> package
> is highly recommended, rather than calculating them with the ANALYZE
> statement
>
> */
> The table I need to generate stats for is currently 32GB and grows by
> ~2GB
> per week. Even the smallest estimate with calculating global stats
> will
> take a long long time and I may not be able to spring for all the
> required
> temp space.
>
> How does the list feel about global stats? Does anyone agree with the
> documentation that they "most important"? I'm thinking my partitioned
> statistics are the "most important".
>
> Any input is appreciated. Thanks
>
> Lisa Koivu
> Oracle Database Administrator
> Fairfield Resorts, Inc.
> 5259 Coconut Creek Parkway
> Ft. Lauderdale, FL, USA 33063
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Denny Koovakattu
  INET: groups_at_koovakattu.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).