Incremental Statistics in 11G

Gathering statistics on partitioned tables consists of gathering statistics at both the table level and partition level. Prior to Oracle Database 11g, adding a new partition or modifying data in a few partitions required scanning the entire table to refresh table-level statistics. Scanning the entire table can be very expensive as partitioned tables are generally very large. However, in Oracle Database 11g this issue has been addressed with the introduction of incremental global statistics, Where Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:

1) the INCREMENTAL value for the partitioned table is set to TRUE;
2) the PUBLISH value for the partitioned table is set to TRUE;
3) the user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.


rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t(
  2     x ,
  3     y ,
  4     z
  5  )
  6  partition by range(x)
  7  (
  8     partition p1 values less than(2),
  9     partition p2 values less than(3),
 10     partition p3 values less than(4),
 11     partition pmax values less than(maxvalue)
 12  )
 13  as
 14  select level, rpad('*',100,'*'),sysdate
 15  from dual
 16  connect by level <= 5;

Table created.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.set_table_prefs(
  3             ownname =>user,
  4             tabname=>'T',
  5             pname=>'INCREMENTAL',
  6             pvalue=>'TRUE');
  7     dbms_stats.set_table_prefs(
  8             ownname =>user,
  9             tabname=>'T',
 10             pname=>'GRANULARITY',
 11             pvalue=>'AUTO');
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.32
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name, partition_name,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by partition_name nulls first;

TABLE_NAME                     PARTITION_NAME                 LAST_ANALYZED
------------------------------ ------------------------------ -----------------------
T                                                             13-apr-2011 01:14:16 pm
T                              P1                             13-apr-2011 01:14:16 pm
T                              P2                             13-apr-2011 01:14:16 pm
T                              P3                             13-apr-2011 01:14:16 pm
T                              PMAX                           13-apr-2011 01:14:16 pm

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select dbms_stats.get_prefs('INCREMENTAL',user,'T') as incremental,
  2        dbms_stats.get_prefs('GRANULARITY',user,'T') as granularity
  3  from dual;

INCREMENTAL          GRANULARITY
-------------------- --------------------
TRUE                 AUTO

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t split partition pmax at (100) into (partition p_100, partition pmax);

Table altered.
Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert into t(x,y,z)
  2  select 50,rpad('*',100,'*'),sysdate
  3  from all_objects;

71478 rows created.

Elapsed: 00:00:04.45
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name, partition_name,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by partition_name nulls first;

TABLE_NAME                     PARTITION_NAME                 LAST_ANALYZED
------------------------------ ------------------------------ -----------------------
T                                                             13-apr-2011 01:14:40 pm
T                              P1                             13-apr-2011 01:14:16 pm
T                              P2                             13-apr-2011 01:14:16 pm
T                              P3                             13-apr-2011 01:14:16 pm
T                              PMAX                           13-apr-2011 01:14:40 pm
T                              P_100                          13-apr-2011 01:14:40 pm

6 rows selected.

Elapsed: 00:00:00.04
rajesh@ORA11GR2>