Statistics granularity

DBMS_STATS.GATHER_TABLE_STATS accepts the argument GRANULARITY. The argument GRANULARITY defines the scope for partitioned tables. For example, GRANULARITY=SUBPARTITION gathers only subpartition statistics. Gathering partition statistics requires another GATHER_TABLE_STATS call with GRANULARITY=PARTITION.

This article describes a condition which leads to undocumented implicit calculation of partition statistics when GRANULARITY=SUBPARTITION. I tested the behavior with the following Oracle releases: 19.7, 19.13, 19.16 and 21.6

Subpartition statistics

To demonstrate the anomaly I’m creating a table with a partition and a subpartition:


create user u identified by Pasword_1111 ;

grant dba to u ;

connect u/Pasword_1111

drop table t ;

create table t
  (
    n1 number,
	n2 number
  )
  partition by range (n1)
     subpartition by range (n2)
     subpartition template
     (subpartition sp_1  values less than (10))
  (
     partition p_1 values less than (5)
  );

exec dbms_stats.gather_table_stats(null, 'T') ;

Gathering statistics with GRANULARITY=SUBPARTITION behaves as expected – only subpartition statistics are gathered:


exec dbms_session.sleep(2);

begin
  dbms_stats.gather_table_stats(
    ownname => 'U', tabname => 'T', partname => 'P_1', 
	granularity => 'SUBPARTITION'
  ) ;
end ;
/

alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss' ;

column partition_name format A4
column subpartition_name format A10

select partition_name,subpartition_name,last_analyzed,stale_stats,num_rows 
  from user_tab_statistics 
  where table_name='T' and partition_name = 'P_1' ;

PART SUBPARTITI LAST_ANALYZED       STALE_S   NUM_ROWS
---- ---------- ------------------- ------- ----------
P_1             26.08.2022 13:53:00 NO               0
P_1  P_1_SP_1   26.08.2022 13:53:26 NO               0

Implicit partition statistics calculation

Next, I’ll create a new partition:


exec dbms_session.sleep(2);

alter table t add partition p_2 values less than (10) ;

insert into t values (6,6);

The statistics are empty for the new partition:


select partition_name,subpartition_name,last_analyzed,stale_stats,num_rows 
  from user_tab_statistics 
  where table_name='T' and partition_name = 'P_2' ;

PART SUBPARTITI LAST_ANALYZED       STALE_S   NUM_ROWS
---- ---------- ------------------- ------- ----------
P_2
P_2  P_2_SP_1

The behavior is different for the new partition – with GRANULARITY=SUBPARTITION also partition statistics are gathered:


begin
  dbms_stats.gather_table_stats(
    ownname => 'U', tabname => 'T', partname => 'P_2', 
	granularity => 'SUBPARTITION'
  ) ;
end ;
/

select partition_name,subpartition_name,last_analyzed,stale_stats,num_rows 
  from user_tab_statistics where table_name='T' and partition_name = 'P_2' ;

PART SUBPARTITI LAST_ANALYZED       STALE_S   NUM_ROWS
---- ---------- ------------------- ------- ----------
P_2             26.08.2022 14:02:58 YES              1
P_2  P_2_SP_1   26.08.2022 14:02:58 NO               1

Interestingly, despite partition statistics were calculated, they are marked as STALE.

This behavior is consistent – also subsequent subpartition statistics calculations implicitly gather partition statistics:


begin
  dbms_stats.gather_table_stats(
    ownname => 'U', tabname => 'T', partname => 'P_2', 
	granularity => 'SUBPARTITION'
  ) ;
end ;
/

select partition_name,subpartition_name,last_analyzed,stale_stats,num_rows 
  from user_tab_statistics 
  where table_name='T' and partition_name = 'P_2' ;

PART SUBPARTITI LAST_ANALYZED       STALE_S   NUM_ROWS
---- ---------- ------------------- ------- ----------
P_2             26.08.2022 14:21:30 YES              1
P_2  P_2_SP_1   26.08.2022 14:21:30 NO               1

The implicit partition statistics gathering stops once and for all after we explicitly collect statistics on the partition or table level:


begin
  dbms_stats.gather_table_stats(
    ownname => 'U', tabname => 'T', partname => 'P_2', 
	granularity => 'PARTITION'
  ) ;

  /* or 
  dbms_stats.gather_table_stats(
    ownname => 'U', tabname => 'T'
  ) ;
  */
  
end ;
/

select partition_name,subpartition_name,last_analyzed,stale_stats,num_rows 
  from user_tab_statistics 
  where table_name='T' and partition_name = 'P_2' ;

PART SUBPARTITI LAST_ANALYZED       STALE_S   NUM_ROWS
---- ---------- ------------------- ------- ----------
P_2             26.08.2022 14:25:42 NO               1
P_2  P_2_SP_1   26.08.2022 14:21:30 NO               1

begin
  dbms_stats.gather_table_stats(
    ownname => 'U', tabname => 'T', partname => 'P_2', 
	granularity => 'SUBPARTITION'
  ) ;
end ;
/

select partition_name,subpartition_name,last_analyzed,stale_stats,num_rows 
  from user_tab_statistics 
  where table_name='T' and partition_name = 'P_2' ;

PART SUBPARTITI LAST_ANALYZED       STALE_S   NUM_ROWS
---- ---------- ------------------- ------- ----------
P_2             26.08.2022 14:25:42 NO               1
P_2  P_2_SP_1   26.08.2022 14:26:17 NO               1

Staleness

Interestingly, table modifications mark the subpartition statistics, though not the partition statistics as stale:


insert into t select 6,6 from dual connect by level <= 1000 ;

select partition_name,subpartition_name,last_analyzed,stale_stats,num_rows 
  from user_tab_statistics 
  where table_name='T' and partition_name = 'P_2' ;

PART SUBPARTITI LAST_ANALYZED       STALE_S   NUM_ROWS
---- ---------- ------------------- ------- ----------
P_2             26.08.2022 14:25:42 NO               1
P_2  P_2_SP_1   26.08.2022 14:26:17 YES              1

However, the subpartition statistics gathering updates the subpartition statistics and changes the status of the partition statistics to STALE.


begin
  dbms_stats.gather_table_stats(
    ownname => 'U', tabname => 'T', partname => 'P_2', 
	granularity => 'SUBPARTITION'
  ) ;
end ;
/

select partition_name,subpartition_name,last_analyzed,stale_stats,num_rows 
  from user_tab_statistics 
  where table_name='T' and partition_name = 'P_2' ;

PART SUBPARTITI LAST_ANALYZED       STALE_S   NUM_ROWS
---- ---------- ------------------- ------- ----------
P_2             26.08.2022 14:25:42 YES              1
P_2  P_2_SP_1   26.08.2022 14:32:14 NO            1001

Explicit partition statistics gathering

The change in behavior is permanent – the subsequent subpartition statistics jobs don’t update the partition statistics anymore:


begin
  dbms_stats.gather_table_stats(
    ownname => 'U', tabname => 'T', partname => 'P_2', 
	granularity => 'SUBPARTITION'
  ) ;
end ;
/

select partition_name,subpartition_name,last_analyzed,stale_stats,num_rows 
  from user_tab_statistics 
  where table_name='T' and partition_name = 'P_2' ;

PART SUBPARTITI LAST_ANALYZED       STALE_S   NUM_ROWS
---- ---------- ------------------- ------- ----------
P_2             26.08.2022 14:25:42 YES              1
P_2  P_2_SP_1   26.08.2022 14:33:23 NO            1001

Therefore, it needs an explicit partition statistics gather to get the partition statistics up to date:


begin
  dbms_stats.gather_table_stats(
    ownname => 'U', tabname => 'T', partname => 'P_2',
    granularity => 'PARTITION'
  ) ;
end ;
/

select partition_name,subpartition_name,last_analyzed,stale_stats,num_rows 
  from user_tab_statistics 
  where table_name='T' and partition_name = 'P_2' ;

PART SUBPARTITI LAST_ANALYZED       STALE_S   NUM_ROWS
---- ---------- ------------------- ------- ----------
P_2             26.08.2022 15:31:54 NO            1001
P_2  P_2_SP_1   26.08.2022 15:06:25 NO            1001

Conclusion

In general, subpartition statistics job (GRANULARITY=SUBPARTITION) behaves as documented – it doesn’t trigger gathering of partition statistics. Exceptionally, for new partitions, the subpartition statistic job gathers also partition statistics, but only until the statistics are explicitly gathered either on the partition or the table level. Afterwards, subpartition statistics gathering permanently reverts to the normal behavior, i.e. partition statistics aren’t gathered by the subpartition statistics jobs.

You shouldn’t rely on this dependency to get accurate partition statistics. Instead, a proper solution consists of two independent calls: one for subpartitions and another for the partition.

Further, on tables with subpartitions, row modifications set STALE_STATS to YES only for subpartitions, but not for partitions. Therefore, if your solution checks the statistics staleness of individual partitions before gathering statistics, it should check the subpartitions first. The subpartition statistics gathering updates STALE_STATS for the partitions to YES if necessary.

Anecdote

I discovered this behavior when analyzing a performance problem on a 3rd party application. The vendor was unknowingly relying on subpartition statistics for keeping partition statistics up to date. New partitions were created daily. This worked well because the table statistics weren’t collected frequently, so the subpartition statistics implicitly calculated partition statistics. But, paradoxically, the performance went south after the application started to gather table statistics more frequently. In a changed schedule, the table statistics were gathered after a new partition was created but still empty. The subpartition statistics were still gathered after the data load, but that didn’t trigger the partition statistics anymore. The performance slumped, and the temporary tablespace was filled. An explicit partition statistics call resolved the issue.

A lot can be learned by analyzing anomalies like this.

Thanks for sharing

One Comment


February 20, 2024
vibha shrivastava

This is not implicit stats collection, this is Aggregated stats. When all the subpartitions have gathered stats and partition level stats is NULL then with the collection of last subpartition, aggregated stats get calculated for partition as well as for table level. But this marks the partition as STALE however you can differentiate aggregated stats vs gathered by GLOBAL_STATS parameter, when it is aggregated it is marked as NO.

Undocumented Implicit Partition Statistics Gathering