Re: dbms_stats.gather_table_stats only calculating stats for first partition of range interval - list composite partitioned table
From: Greg Rahn <greg_at_structureddata.org>
Date: Thu, 10 Dec 2009 10:17:09 -0800
Message-ID: <a9c093440912101017jff484das776c76000f1aae1b_at_mail.gmail.com>
First, if you are on 11g you really should not deviate from the default of ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE. NULL/100% takes significantly longer and yields the same results. Read more: http://structureddata.org/2007/09/17/oracle-11g-enhancements-to-dbms_stats/
> Oracle 11.1.0.6 EE
> OEL V4 64 bit.
> I am stumped by what has happened to the statistics for a table that I have
> that is range interval - list partitioned.
> but only the first partition (the one you have to declare) has stats
> gathered for it at the partition level, even though they are gathered for
> all of the subpartitions for all partitions.
> Global stats are also created.
Date: Thu, 10 Dec 2009 10:17:09 -0800
Message-ID: <a9c093440912101017jff484das776c76000f1aae1b_at_mail.gmail.com>
First, if you are on 11g you really should not deviate from the default of ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE. NULL/100% takes significantly longer and yields the same results. Read more: http://structureddata.org/2007/09/17/oracle-11g-enhancements-to-dbms_stats/
What you are observing is a bug in 11.1.0.6 - it does not reproduce in 11.1.0.7. Let me see if I can find the bug.
On Thu, Dec 10, 2009 at 6:20 AM, Peter Hitchman <pjhoraclel_at_gmail.com> wrote:
>
> Oracle 11.1.0.6 EE
> OEL V4 64 bit.
> I am stumped by what has happened to the statistics for a table that I have
> that is range interval - list partitioned.
> but only the first partition (the one you have to declare) has stats
> gathered for it at the partition level, even though they are gathered for
> all of the subpartitions for all partitions.
> Global stats are also created.
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 10 2009 - 12:17:09 CST