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

From: Bertrand Guillaumin <bertrand.guillaumin_at_digora.com>
Date: Thu, 10 Dec 2009 16:18:47 +0100
Message-ID: <7F06EBB9DB1FFD4BB233D8162D0F925729F4E85511_at_athena.Digorix2.digora.com>



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_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: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_at_digora.com<mailto: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<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] de la part de Peter Hitchman [pjhoraclel_at_gmail.com<mailto: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 - 09:18:47 CST

Original text of this message