Re: RE : RE : dbms_stats.gather_table_stats only calculating stats for first partition of range interval - list composite partitioned table
- From: Peter Hitchman <pjhoraclel@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Dec 2009 15:44:25 +0000
Hi,
I created a table in the development database with the same set-up and so
far I cannot get partition level stats outside of the first partition. Does
not matter what granularity I use, I get 0 for the number of partition rows,
but the number of partitiion blocks is populated.
Still trying ....
Pete
On Thu, Dec 10, 2009 at 3:18 PM, Bertrand Guillaumin <
bertrand.guillaumin@xxxxxxxxxx> wrote:
> Try modifying your granularity parameter to ALL :
> granularity
> Granularity of statistics to collect (only pertinent if the table is
> partitioned).
> 'ALL' - gathers all (subpartition, partition, and global) statistics
>
> 'AUTO'- determines the granularity based on the partitioning type. This is
> the default value.
>
> 'DEFAULT' - gathers global and partition-level statistics. This option is
> obsolete, and while currently supported, it is included in the documentation
> for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this
> functionality. Note that the default value is now 'AUTO'.
>
> 'GLOBAL' - gathers global statistics
>
> 'GLOBAL AND PARTITION' - gathers the global and partition level statistics.
> No subpartition level statistics are gathered even if it is a composite
> partitioned object.
>
> 'PARTITION '- gathers partition-level statistics
>
> 'SUBPARTITION' - gathers subpartition-level statistics.
> ________________________________________
> De : oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] de la
> part de Peter Hitchman [pjhoraclel@xxxxxxxxx]
> Date d'envoi : jeudi 10 décembre 2009 15:50
> À : oracle-l
> Objet : Re: RE : dbms_stats.gather_table_stats only calculating stats for
> first partition of range interval - list composite partitioned table
>
> 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@xxxxxxxxxx<mailto:bertrand.guillaumin@xxxxxxxxxx>>
> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx> [
> oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] de la
> part de Peter Hitchman [pjhoraclel@xxxxxxxxx<mailto:pjhoraclel@xxxxxxxxx>]
> 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
>
--
Regards
Pete
Other related posts:
