Re: RE : dbms_stats.gather_table_stats only calculating stats for first partition of range interval - list composite partitioned table

From: Peter Hitchman <pjhoraclel_at_gmail.com>
Date: Thu, 10 Dec 2009 14:50:26 +0000
Message-ID: <5e317f310912100650h1fda629br625e6bac9900f24f_at_mail.gmail.com>

Hi,
I have been trying various things and one is to specify a particular partition (to save time), but if it's not the first partition in the table, no partition level stats get created.

Regards

Pete

On Thu, Dec 10, 2009 at 2:40 PM, Bertrand Guillaumin < bertrand.guillaumin_at_digora.com> wrote:

> Well, try maybe not indicating a partition(or indicate null) if you want to
> gather statistics on all partitions.
> Indicating a partition in DBMS_STATS.GATHER_TABLE_STATS implies you only
> want to gather statistics on that partition.
>
> Best regards,
> Bertrand Guillaumin
> ________________________________________
> De : oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] de la
> part de Peter Hitchman [pjhoraclel_at_gmail.com]
> Date d'envoi : jeudi 10 décembre 2009 15:20
> À : oracle-l
> Objet : dbms_stats.gather_table_stats only calculating stats for first
> partition of range interval - list composite partitioned table
>
> Oracle 11.1.0.6 EE
> OEL V4 64 bit.
>
> All,
> I am stumped by what has happened to the statistics for a table that I have
> that is range interval - list partitioned.
>
> I gather the stats like this:
> DBMS_STATS.GATHER_TABLE_STATS(
> OWNNAME=>'&owner_name'
> ,TABNAME=>'TSPS_TRACKER'
> ,PARTNAME=>'&PARTITION_NAME'
> ,ESTIMATE_PERCENT=> NULL
> ,BLOCK_SAMPLE => FALSE
> ,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'
> -- ,DEGREE => DBMS_STATS.AUTO_DEGREE
> ,DEGREE => NULL
> ,GRANULARITY=>'AUTO'
> ,CASCADE=>TRUE
> ,FORCE=>TRUE);
>
> 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.
>
> The table DDL:
>
> CREATE TABLE TSPS_TRACKER
> ( PATENT_SK NUMBER(9) NOT NULL
> ,KEY NUMBER(6) NOT NULL
> ,ARRIVED TIMESTAMP NOT NULL
> )
> PARTITION BY RANGE (ARRIVED) INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
> SUBPARTITION BY LIST (KEY)
> SUBPARTITION TEMPLATE
> (SUBPARTITION DEL VALUES (5,10) TABLESPACE CSGDATA
> ,SUBPARTITION NEW VALUES (100,300,400,1000,1100,1200,1300,1390,1400)
> TABLESPACE CSGDATA
> ,SUBPARTITION SVA VALUES (1800,1900,2000,2100,2200,2300,2500) TABLESPACE
> CSGDATA
> ,SUBPARTITION EVA VALUES
> (2900,3000,3100,3145,3150,3300,3400,3500,3600,3700,3800) TABLESPACE CSGDATA
> ,SUBPARTITION PUB VALUES (3900,4000,4100,4200,7000) TABLESPACE CSGDATA
> ,SUBPARTITION REIR VALUES (5000,5005,5010,5015,5020,5025,5040,5050)
> TABLESPACE CSGDATA
> ,SUBPARTITION REIS VALUES (5100,5200,5400,5500,5900,6000,6100) TABLESPACE
> CSGDATA
> ,SUBPARTITION MISC VALUES (DEFAULT) TABLESPACE CSGDATA
> )
> ( PARTITION TT_01 VALUES LESS THAN ('01-APR-2008:00:00:00')
> )
> ENABLE ROW MOVEMENT
> /
>
> it also has two local indexes. The same thing happens for the indexes, only
> the first partition has partition stats collected.
>
> This makes SQL that is only going to use 1 partition choose very
> inefficient plans (when the partition in question is not the first one of
> course).
>
> Anyone know of a reason why this should be so?
>
> Thanks
>
> Pete
>

-- 
Regards

Pete

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 10 2009 - 08:50:26 CST