Oracle Global,Partition and Sub-partition Statistics

Here is an example of how to collect partitioned table stats. Specifically for Non-LIST sub-partition tables, whose sub-partition stats will not be collected by default. here is the rule :

        When the AUTO option is specified for “granularity”, Oracle collects global, partition-level, and sub-partition level statistics if sub-partition method is LIST. For other partitioned tables, only the global and partition level statistics are generated. 

1)Create a sample range type sub-partition table.

CREATE TABLE STUDENTS
(
ENROLLMENT_DATE DATE NOT NULL,
DEPARTMENT_ID NUMBER(2) NOT NULL,
STUDENT_ID NUMBER(6)
)
PARTITION BY RANGE (ENROLLMENT_DATE)
SUBPARTITION BY RANGE (DEPARTMENT_ID)
SUBPARTITION TEMPLATE
(SUBPARTITION SP_1 VALUES LESS THAN (10),
SUBPARTITION SP_2 VALUES LESS THAN (20),
SUBPARTITION SP_MAX VALUES LESS THAN (MAXVALUE)
)
(
PARTITION P_2016 VALUES LESS THAN (TO_DATE('2017-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION P_2017 VALUES LESS THAN (TO_DATE('2018-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
);
Table created.

insert into students values (TO_DATE('2016-07-01 10:20:08', 'YYYY-MM-DD HH24:MI:SS'), 8,1);
insert into students values (TO_DATE('2016-07-01 10:20:08', 'YYYY-MM-DD HH24:MI:SS'), 12,2);
insert into students values (TO_DATE('2016-07-01 10:20:08', 'YYYY-MM-DD HH24:MI:SS'), 25,3);

insert into students values (TO_DATE('2017-02-09 18:34:43', 'YYYY-MM-DD HH24:MI:SS'), 7,4);
insert into students values (TO_DATE('2017-02-09 18:34:43', 'YYYY-MM-DD HH24:MI:SS'), 13,5);
insert into students values (TO_DATE('2017-02-09 18:34:43', 'YYYY-MM-DD HH24:MI:SS'), 23,6);

insert into students values (TO_DATE('2018-08-03 02:30:45', 'YYYY-MM-DD HH24:MI:SS'), 3,7);
insert into students values (TO_DATE('2018-08-03 02:30:45', 'YYYY-MM-DD HH24:MI:SS'), 19,8);
insert into students values (TO_DATE('2018-08-03 02:30:45', 'YYYY-MM-DD HH24:MI:SS'), 28,9);

2) Check table, partition and sub-partition without stats:

SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED 
from user_tab_partitions where table_name='STUDENTS' ;

TABLE_NAME PARTITION_NAME     NUM_ROWS   LAST_ANALYZED
---------- -----------------  ---------- -------------
STUDENTS   P_2016
STUDENTS   P_2017
STUDENTS   P_MAX

SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED 
     from user_tables 
    where table_name='STUDENTS';

TABLE_NAME  NUM_ROWS   LAST_ANALYZED
----------- ---------- ---------------
STUDENTS

SQL> select TABLE_NAME,PARTITION_NAME,subpartition_name,NUM_ROWS,
            LAST_ANALYZED 
      from user_tab_subpartitions
     where table_name='STUDENTS' 
     order by PARTITION_NAME,LAST_ANALYZED;

TABLE_NAME PARTITIO SUBPARTITION_NAME NUM_ROWS LAST_ANAL
---------- -------- ----------------- -------- ----------
STUDENTS   P_2016   P_2016_SP_1
STUDENTS   P_2016   P_2016_SP_2
STUDENTS   P_2016   P_2016_SP_MAX
STUDENTS   P_2017   P_2017_SP_1
STUDENTS   P_2017   P_2017_SP_2
STUDENTS   P_2017   P_2017_SP_MAX
STUDENTS   P_MAX    P_MAX_SP_1
STUDENTS   P_MAX    P_MAX_SP_2
STUDENTS   P_MAX    P_MAX_SP_MAX

9 rows selected.

3)Sub-partition stats are NOT collected by default.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'TETSUSER',
                                        TabName =>'STUDENTS');

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED
      from user_tables 
     where table_name='STUDENTS'; 

TABLE_NAME   NUM_ROWS   LAST_ANAL
------------ ---------- ---------
STUDENTS     9          04-JAN-19

SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED
from user_tab_partitions where table_name='STUDENTS' ;

TABLE_NAME PARTITION_NAME NUM_ROWS   LAST_ANAL
---------- -------------- ---------- ---------
STUDENTS   P_2016         3          04-JAN-19
STUDENTS   P_2017         3          04-JAN-19
STUDENTS   P_MAX          3          04-JAN-19

SQL> select TABLE_NAME,PARTITION_NAME,subpartition_name,
            NUM_ROWS,LAST_ANALYZED
      from user_tab_subpartitions
     where table_name='STUDENTS'
  order by PARTITION_NAME,LAST_ANALYZED;

TABLE_NAME    PARTITION_NA SUBPARTITION_NAME NUM_ROWS   LAST_ANAL
------------- ------------ ----------------- ---------- ---------
STUDENTS      P_2016       P_2016_SP_1
STUDENTS      P_2016       P_2016_SP_2
STUDENTS      P_2016       P_2016_SP_MAX
STUDENTS      P_2017       P_2017_SP_1
STUDENTS      P_2017       P_2017_SP_2
STUDENTS      P_2017       P_2017_SP_MAX
STUDENTS      P_MAX        P_MAX_SP_1
STUDENTS      P_MAX        P_MAX_SP_2
STUDENTS      P_MAX        P_MAX_SP_MAX

9 rows selected.

4) With PartName option, all Global and Partition stats are collected, but not for Sub-partitions.

SQL> exec dbms_stats.delete_table_stats(ownname => 'TETSUSER',
                                        tabname => 'STUDENTS');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'TETSUSER',
                        TabName =>'STUDENTS',PartName=>'P_2016');

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED
     from user_tables where table_name='STUDENTS'; 

TABLE_NAME    NUM_ROWS    LAST_ANAL
------------- ---------- ---------
STUDENTS      9           07-JAN-19

SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED
     from user_tab_partitions where table_name='STUDENTS'  ;

TABLE_NAME    PARTITION_NA NUM_ROWS   LAST_ANAL
------------- ------------ ---------- ---------
STUDENTS      P_2016       3          07-JAN-19
STUDENTS      P_2017       3          07-JAN-19
STUDENTS      P_MAX        3          07-JAN-19

SQL> select TABLE_NAME,PARTITION_NAME,subpartition_name,
            NUM_ROWS, LAST_ANALYZED
      from user_tab_subpartitions
     where table_name='STUDENTS' 
  order by PARTITION_NAME,LAST_ANALYZED;

TABLE_NAME    PARTITION_NA SUBPARTITION_NAME NUM_ROWS  LAST_ANAL
------------- ------------ ------------------ -------- ---------
STUDENTS      P_2016       P_2016_SP_1
STUDENTS      P_2016       P_2016_SP_2
STUDENTS      P_2016       P_2016_SP_MAX
STUDENTS      P_2017       P_2017_SP_1
STUDENTS      P_2017       P_2017_SP_2
STUDENTS      P_2017       P_2017_SP_MAX
STUDENTS      P_MAX        P_MAX_SP_1
STUDENTS      P_MAX        P_MAX_SP_2
STUDENTS      P_MAX        P_MAX_SP_MAX

9 rows selected.

5) With Granularity=>’ALL’,  all Global, Partition and Sun-partition stats are collected.

SQL> exec dbms_stats.delete_table_stats(ownname => 'TESTUSER',
tabname => 'STUDENTS');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'TESTUSER',
TabName =>'STUDENTS', Granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED
from user_tables where table_name='STUDENTS'; 

TABLE_NAME    NUM_ROWS   LAST_ANAL
------------- ---------- ---------
STUDENTS      9          07-JAN-19

SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED
from user_tab_partitions where table_name='STUDENTS' ;

TABLE_NAME    PARTITION_NA NUM_ROWS   LAST_ANAL
------------- ------------ ---------- ---------
STUDENTS      P_2016       3          07-JAN-19
STUDENTS      P_2017       3          07-JAN-19
STUDENTS      P_MAX        3          07-JAN-19

SQL>select TABLE_NAME,PARTITION_NAME,subpartition_name,
           NUM_ROWS,LAST_ANALYZED
     from user_tab_subpartitions
    where table_name='STUDENTS' 
 order by PARTITION_NAME,LAST_ANALYZED;

TABLE_NAME    PARTITION_NA SUBPARTITION_NAME NUM_ROWS  LAST_ANAL
------------- ------------ ----------------- --------- ---------
STUDENTS      P_2016       P_2016_SP_1        1        07-JAN-19
STUDENTS      P_2016       P_2016_SP_2        1        07-JAN-19
STUDENTS      P_2016       P_2016_SP_MAX      1        07-JAN-19
STUDENTS      P_2017       P_2017_SP_1        1        07-JAN-19
STUDENTS      P_2017       P_2017_SP_2        1        07-JAN-19
STUDENTS      P_2017       P_2017_SP_MAX      1        07-JAN-19
STUDENTS      P_MAX        P_MAX_SP_1         1        07-JAN-19
STUDENTS      P_MAX        P_MAX_SP_2         1        07-JAN-19
STUDENTS      P_MAX        P_MAX_SP_MAX       1        07-JAN-19

9 rows selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.