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 16:00:25 +0000
To try to be a bit clearer:
the number of rows for the subpartitions are populated but at the partition
level the value stored is zero and of course it should be a sum of the
subpartitions.
Pete
On Thu, Dec 10, 2009 at 3:44 PM, Peter Hitchman <pjhoraclel@xxxxxxxxx>wrote:
> 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
>
--
Regards
Pete
Other related posts:
