Stats Gathering on the Oracle Table Partition Script

 One of the developer recently converted several large tables in Oracle database to using daily interval range partitioning on a date field and partitioned indexes. As part of the data load process, they ran the gather stats procedure on the table at the end after the data has been inserted and committed. 

Interval partitioning makes it easier as new partitions are automatically created when new data inserted does not belong in any of the existing partitions' upper limits. However, I noticed that the time it takes to execute the gather stats procedure is significant, taking many hours for tables that have a row count in the hundred millions. 

With incremental statistics, Oracle will only gather partition statistics for partitions that have changed. Synopses are built for each partition, and those synopses are quickly combined to create global statistics without having to re-scan the whole table.

Here you go. Script for gathering statistic by partition value:

set linesize 80 pagesize 1000 echo off

prompt

prompt Partition Stats gathering durations by date:

prompt

set serveroutput on size 1000000

declare

 sum_duration interval day to second :=to_dsinterval('+000000000 00:00:00');

 prior_start_time  DATE;

 prior_rnum number;

begin

  dbms_output.put_line(rpad('DATE',12,' ')||' '||rpad('DURATION',19,' '));

  dbms_output.put_line(rpad('-',12,'-')||' '||rpad('-',19,'-'));

  for c in ( select /*+NOMERGE*/ rownum rnum, 

                    a.object_name, 

                    a.start_time,

                    a. duration

               from (select substr(target,1,instr(target,'.',-1)-1) object_name,

                            trunc(start_time,'DD') start_time, 

                            end_time - start_time duration

                       from DBA_OPTSTAT_OPERATIONS 

                      where operation='gather_table_stats'

--                        and target like 'ODS.TRANSACTION.%'

                        and TRUNC(Start_Time)>trunc(sysdate-30)

                        and target in (select owner||'.'||table_name||'.'||partition_name 

                                         from dba_tab_stats_history

                                        where partition_name is not null

                                          and stats_update_time > trunc(sysdate-30))

                      order by start_time) a

           ) loop

--       dbms_output.put_line('data: '||c.rnum||' '||c.object_name||' '||c.start_time||' '||c.duration);

       if c.rnum = 1 then 

          prior_start_time := c.start_time;

          prior_rnum := 0;

       end if;

       if c.start_time = prior_start_time and (c.rnum-1 = prior_rnum ) then

          sum_duration := sum_duration + c.duration;

--          dbms_output.put_line('sum: '||c.start_time||' '||sum_duration);

       else

          dbms_output.put_line(to_char(prior_start_time,'DD-MON-YY')||' '||sum_duration);

          sum_duration := c.duration;

          prior_start_time := c.start_time;

       end if;

       prior_rnum := c.rnum;

  end loop;

  dbms_output.put_line(to_char(prior_start_time,'DD-MON-YY')||' '||sum_duration);

end;

/