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

  • From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>, pjhoraclel@xxxxxxxxx
  • Date: Thu, 10 Dec 2009 08:09:11 -0800 (PST)

Have you tried to create a script to gather the stats at a partition level?  I 
know this doesn't address your issue at the table level stats gathering, but it 
might be note worthy to see if you can gather at least one partition at the 
partition level for the table and have it correct.
 
SELECT 'Exec  DBMS_STATS.GATHER_TABLE_STATS (ownname=>'''||table_owner||''', 
tabname=>'''||table_name||''',partname=>'''||partition_name||''', 
method_opt=>''FOR ALL COLUMS SIZE 1",estimate_percent=><%>, CASCADE=>TRUE, 
DEGREE=>6);' from DBA_TAB_PARTITIONS
where num_rows=0
and table_owner='<OWNER>'
and table_name='<TABLE>'
and last_analyzed is null; <--or if num_row is 0, look at that! :)


Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
 
"Go away before I replace you with a very small and efficient shell script..."

--- On Thu, 12/10/09, Peter Hitchman <pjhoraclel@xxxxxxxxx> wrote:


From: Peter Hitchman <pjhoraclel@xxxxxxxxx>
Subject: Re: RE : RE : dbms_stats.gather_table_stats only calculating stats for 
first partition of range interval - list composite partitioned table
To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Date: Thursday, December 10, 2009, 9:00 AM


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: