online stats gathering for partitioned Tables

One of the nice feature introduced in Oracle 12c (12.1.0.1) is the ability to gather online stats for tables during bulk loads such as CREATE TABLE AS SELECT operation or Direct path insert into an empty table.

 

demo@ORA12C> create table t

  2  partition by range( created )

  3     interval( numtoyminterval(1,'year') )

  4  ( partition p_2013 values less than

  5     ( to_date('01-Jan-2014','dd-mon-yyyy') ) )

  6  as

  7  select owner,object_name,object_id,created

  8  from all_objects;

 

Table created.

 

demo@ORA12C> select num_rows,blocks,last_analyzed

  2  from user_Tables

  3  where table_name ='T';

 

  NUM_ROWS     BLOCKS LAST_ANALYZED

---------- ---------- -----------------------

     61521        550 12-FEB-2018 12:53:31 pm

 

demo@ORA12C> select column_name,num_distinct,last_analyzed

  2  from user_tab_col_statistics

  3  where table_name ='T';

 

COLUMN_NAME          NUM_DISTINCT LAST_ANALYZED

-------------------- ------------ -----------------------

OWNER                          20 12-FEB-2018 12:53:31 pm

OBJECT_NAME                 51692 12-FEB-2018 12:53:31 pm

OBJECT_ID                   61521 12-FEB-2018 12:53:31 pm

CREATED                      1294 12-FEB-2018 12:53:31 pm

 

demo@ORA12C>

 

So as part of data load, the database is able to gather the stats on this object, this improves the performance and manageability of bulk load operations by eliminating the user intervention to gather statistics after the load by removing an additional full table scan required for separate statistics gathering operations.

 

Since the online stats gathering was designed to work with minimal impacts to direct path operation, database by default don’t gather partition level statistics.

 

 

demo@ORA12C> select partition_name,num_rows,blocks,last_analyzed

  2  from user_tab_partitions

  3  where table_name ='T';

 

PARTITION_   NUM_ROWS     BLOCKS LAST_ANALYZED

---------- ---------- ---------- -----------------------

P_2013

SYS_P10261

SYS_P10281

 

demo@ORA12C> select partition_name,column_name,num_distinct,last_analyzed

  2  from user_part_col_statistics

  3  where table_name ='T';

 

PARTITION_ COLUMN_NAME          NUM_DISTINCT LAST_ANALYZED

---------- -------------------- ------------ -----------------------

P_2013     OWNER

P_2013     OBJECT_NAME

P_2013     OBJECT_ID

P_2013     CREATED

SYS_P10261 OWNER

SYS_P10261 OBJECT_NAME

SYS_P10261 OBJECT_ID

SYS_P10261 CREATED

SYS_P10281 OWNER

SYS_P10281 OBJECT_NAME

SYS_P10281 OBJECT_ID

SYS_P10281 CREATED

 

12 rows selected.

 

demo@ORA12C>

 

So just to build the partition level statistics in this case, we can make use of the option GATHER AUTO without updating the global statistics. Using GATHER AUTO means we are asking oracle to gather only the missing statistics in this table (which in this case is a partitioned level statistics).

 

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T',options=>'GATHER AUTO');

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select num_rows,blocks,last_analyzed

  2  from user_Tables

  3  where table_name ='T';

 

  NUM_ROWS     BLOCKS LAST_ANALYZED

---------- ---------- -----------------------

     61521        550 12-FEB-2018 12:53:31 pm

 

demo@ORA12C> select column_name,num_distinct,last_analyzed

  2  from user_tab_col_statistics

  3  where table_name ='T';

 

COLUMN_NAME          NUM_DISTINCT LAST_ANALYZED

-------------------- ------------ -----------------------

OWNER                          20 12-FEB-2018 12:53:31 pm

OBJECT_NAME                 51692 12-FEB-2018 12:53:31 pm

OBJECT_ID                   61521 12-FEB-2018 12:53:31 pm

CREATED                      1294 12-FEB-2018 12:53:31 pm

 

demo@ORA12C> select partition_name,num_rows,blocks,last_analyzed

  2  from user_tab_partitions

  3  where table_name ='T';

 

PARTITION_   NUM_ROWS     BLOCKS LAST_ANALYZED

---------- ---------- ---------- -----------------------

P_2013              0          0 12-FEB-2018 01:20:37 pm

SYS_P10261      61503        531 12-FEB-2018 01:20:38 pm

SYS_P10281         18         19 12-FEB-2018 01:20:38 pm

 

demo@ORA12C> select partition_name,column_name,num_distinct,last_analyzed

  2  from user_part_col_statistics

  3  where table_name ='T';

 

PARTITION_ COLUMN_NAME          NUM_DISTINCT LAST_ANALYZED

---------- -------------------- ------------ -----------------------

P_2013     OWNER                           0 12-FEB-2018 01:20:37 pm

P_2013     OBJECT_NAME                     0 12-FEB-2018 01:20:37 pm

P_2013     OBJECT_ID                       0 12-FEB-2018 01:20:37 pm

P_2013     CREATED                         0 12-FEB-2018 01:20:37 pm

SYS_P10261 OWNER                          20 12-FEB-2018 01:20:37 pm

SYS_P10261 OBJECT_NAME                 51676 12-FEB-2018 01:20:37 pm

SYS_P10261 OBJECT_ID                   61503 12-FEB-2018 01:20:37 pm

SYS_P10261 CREATED                      1278 12-FEB-2018 01:20:37 pm

SYS_P10281 OWNER                           1 12-FEB-2018 01:20:38 pm

SYS_P10281 OBJECT_NAME                    17 12-FEB-2018 01:20:38 pm

SYS_P10281 OBJECT_ID                      18 12-FEB-2018 01:20:38 pm

SYS_P10281 CREATED                        16 12-FEB-2018 01:20:38 pm

 

12 rows selected.

 

demo@ORA12C>