Incremental stats gathering changed up in 11.2.0.4 - Part II

 

This will be the prolongation of previous posting

Let’s start with a demo.

 

rajesh@ORA11G> create table t

  2  partition by list(x)

  3  (

  4    partition p1 values (1),

  5    partition p2 values (2)

  6  ) as

  7  select a.*, mod(rownum,2)+1 x ,

  8  (mod(rownum,2)+1)*5 as order_status

  9  from all_objects a;

 

Table created.

 

rajesh@ORA11G>

 

Next set the table level preferences for Incremental stats gathering.

 

rajesh@ORA11G> exec dbms_stats.set_table_prefs(user,'T','INCREMENTAL','TRUE');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G> exec dbms_stats.set_table_prefs(user,'T','GRANULARITY','auto');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','TRUE');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G> exec dbms_stats.set_table_prefs(user,'T','ESTIMATE_PERCENT','dbms_stats.auto_sample_size');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G>

rajesh@ORA11G> select dbms_stats.get_prefs('INCREMENTAL',user,'T') incremental,

  2         dbms_stats.get_prefs('GRANULARITY',user,'T') granularity,

  3         dbms_stats.get_prefs('PUBLISH',user,'T') publish,

  4         dbms_stats.get_prefs('ESTIMATE_PERCENT',user,'T') estimate_percent

  5  from dual ;

 

INCREMENTA GRANULARIT PUBLISH    ESTIMATE_PERCENT

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

TRUE       AUTO       TRUE       DBMS_STATS.AUTO_SAMPLE_SIZE

 

1 row selected.

 

rajesh@ORA11G>

rajesh@ORA11G> exec dbms_stats.gather_table_Stats(user,'T');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,last_analyzed

  2     from user_tab_partitions

  3     where table_name ='T' ;

 

TABLE PARTI LAST_ANALYZED

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

T     P1    02-SEP-2014 01:40:33 PM

T     P2    02-SEP-2014 01:40:32 PM

 

2 rows selected.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_TAB_col_statistics

  4  where table_name ='T'

  5  order by column_name;

 

TABLE COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

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

T     CREATED                 1318 02-SEP-2014 01:40:33 PM NONE

T     DATA_OBJECT_ID          6978 02-SEP-2014 01:40:33 PM NONE

T     EDITION_NAME               1 02-SEP-2014 01:40:33 PM NONE

T     GENERATED                  2 02-SEP-2014 01:40:33 PM NONE

T     LAST_DDL_TIME           1383 02-SEP-2014 01:40:33 PM NONE

T     NAMESPACE                 17 02-SEP-2014 01:40:33 PM NONE

T     OBJECT_ID              84677 02-SEP-2014 01:40:33 PM NONE

T     OBJECT_NAME            49892 02-SEP-2014 01:40:33 PM NONE

T     OBJECT_TYPE               39 02-SEP-2014 01:40:33 PM NONE

T     ORDER_STATUS               2 02-SEP-2014 01:40:33 PM NONE

T     OWNER                     31 02-SEP-2014 01:40:33 PM NONE

T     SECONDARY                  2 02-SEP-2014 01:40:33 PM NONE

T     STATUS                     2 02-SEP-2014 01:40:33 PM NONE

T     SUBOBJECT_NAME           183 02-SEP-2014 01:40:33 PM NONE

T     TEMPORARY                  2 02-SEP-2014 01:40:33 PM NONE

T     TIMESTAMP               1473 02-SEP-2014 01:40:33 PM NONE

T     X                          2 02-SEP-2014 01:40:33 PM NONE

 

17 rows selected.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_part_col_statistics

  4  where table_name ='T'

  5  order by  partition_name,column_name;

 

TABLE PARTI COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

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

T     P1    CREATED                 1206 02-SEP-2014 01:40:32 PM NONE

T     P1    DATA_OBJECT_ID          3517 02-SEP-2014 01:40:32 PM NONE

T     P1    EDITION_NAME               1 02-SEP-2014 01:40:32 PM NONE

T     P1    GENERATED                  2 02-SEP-2014 01:40:32 PM NONE

T     P1    LAST_DDL_TIME           1221 02-SEP-2014 01:40:32 PM NONE

T     P1    NAMESPACE                 16 02-SEP-2014 01:40:32 PM NONE

T     P1    OBJECT_ID              42338 02-SEP-2014 01:40:32 PM NONE

T     P1    OBJECT_NAME            40264 02-SEP-2014 01:40:32 PM NONE

T     P1    OBJECT_TYPE               38 02-SEP-2014 01:40:32 PM NONE

T     P1    ORDER_STATUS               1 02-SEP-2014 01:40:32 PM NONE

T     P1    OWNER                     31 02-SEP-2014 01:40:32 PM NONE

T     P1    SECONDARY                  2 02-SEP-2014 01:40:32 PM NONE

T     P1    STATUS                     2 02-SEP-2014 01:40:32 PM NONE

T     P1    SUBOBJECT_NAME           139 02-SEP-2014 01:40:32 PM NONE

T     P1    TEMPORARY                  2 02-SEP-2014 01:40:32 PM NONE

T     P1    TIMESTAMP               1292 02-SEP-2014 01:40:32 PM NONE

T     P1    X                          1 02-SEP-2014 01:40:32 PM NONE

T     P2    CREATED                 1207 02-SEP-2014 01:40:31 PM NONE

T     P2    DATA_OBJECT_ID          3494 02-SEP-2014 01:40:31 PM NONE

T     P2    EDITION_NAME               1 02-SEP-2014 01:40:31 PM NONE

T     P2    GENERATED                  2 02-SEP-2014 01:40:31 PM NONE

T     P2    LAST_DDL_TIME           1223 02-SEP-2014 01:40:31 PM NONE

T     P2    NAMESPACE                 16 02-SEP-2014 01:40:31 PM NONE

T     P2    OBJECT_ID              42339 02-SEP-2014 01:40:31 PM NONE

T     P2    OBJECT_NAME            40324 02-SEP-2014 01:40:31 PM NONE

T     P2    OBJECT_TYPE               37 02-SEP-2014 01:40:31 PM NONE

T     P2    ORDER_STATUS               1 02-SEP-2014 01:40:31 PM NONE

T     P2    OWNER                     31 02-SEP-2014 01:40:31 PM NONE

T     P2    SECONDARY                  2 02-SEP-2014 01:40:31 PM NONE

T     P2    STATUS                     2 02-SEP-2014 01:40:31 PM NONE

T     P2    SUBOBJECT_NAME           141 02-SEP-2014 01:40:31 PM NONE

T     P2    TEMPORARY                  2 02-SEP-2014 01:40:31 PM NONE

T     P2    TIMESTAMP               1282 02-SEP-2014 01:40:31 PM NONE

T     P2    X                          1 02-SEP-2014 01:40:31 PM NONE

 

34 rows selected.

 

rajesh@ORA11G>

 

Now let’s add a new partition P3 and change a couple of records in partition P1.

 

rajesh@ORA11G> update t set order_status = 77

  2      where x = 1 and rownum = 1;

 

1 row updated.

 

rajesh@ORA11G>

rajesh@ORA11G> commit;

 

Commit complete.

 

rajesh@ORA11G>

rajesh@ORA11G> alter table t add partition p3 values(3);

 

Table altered.

 

rajesh@ORA11G>

rajesh@ORA11G> insert into t

  2  select a.*, 3 as id,

  3  (mod(rownum,3)+1)*5 as order_status

  4  from all_objects a

  5  where rownum <=1000;

 

1000 rows created.

 

rajesh@ORA11G>

 

When we do stats gathering, we expect only partition P1 and P3 to be re-gathered.

 

rajesh@ORA11G> exec dbms_stats.gather_table_Stats(user,'T');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,last_analyzed

  2  from user_tab_partitions

  3  where table_name ='T' ;

 

TABLE PARTI LAST_ANALYZED

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

T     P1    02-SEP-2014 01:40:37 PM

T     P2    02-SEP-2014 01:40:36 PM

T     P3    02-SEP-2014 01:40:35 PM

 

3 rows selected.

 

rajesh@ORA11G>

 

But what happened was we re-gathered the stats on all the available partitions.

 

However, this regather is actually expected behavior---it is necessary to produce correct global table stats. The reason is that a partition being updated is not the only reason that stats may need to be regathered on it. If DBMS_STATS decides that it needs to consider a histogram on a column where it previously did not, then it needs to regather stats on the column in all partitions. That is what has happened here.

 

In our test case, METHOD_OPT is set to the default of FOR ALL COLUMNS SIZE AUTO, so the columns that are considered for histograms will depend on column usage. Create a completely new table, so DBMS_STATS starts by assuming no histograms are included. After you update the table "where x=1", DBMS_STATS decides it needs to consider a histogram on column X to support equijoins on it. This new histogram that needed to be considered forced the regather on all partitions

 

If you check *_part_col_statistics before and after your test case you will see that the histogram for X changes from NONE to FREQUENCY in all of the partitions.

 

rajesh@ORA11G> select table_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_TAB_col_statistics

  4  where table_name ='T'

  5  order by column_name;

 

TABLE COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

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

T     CREATED                 1318 02-SEP-2014 01:40:37 PM NONE

T     DATA_OBJECT_ID          6978 02-SEP-2014 01:40:37 PM NONE

T     EDITION_NAME               1 02-SEP-2014 01:40:37 PM NONE

T     GENERATED                  2 02-SEP-2014 01:40:37 PM NONE

T     LAST_DDL_TIME           1383 02-SEP-2014 01:40:37 PM NONE

T     NAMESPACE                 17 02-SEP-2014 01:40:37 PM NONE

T     OBJECT_ID              84876 02-SEP-2014 01:40:37 PM NONE

T     OBJECT_NAME            49892 02-SEP-2014 01:40:37 PM NONE

T     OBJECT_TYPE               39 02-SEP-2014 01:40:37 PM NONE

T     ORDER_STATUS               4 02-SEP-2014 01:40:37 PM NONE

T     OWNER                     31 02-SEP-2014 01:40:37 PM NONE

T     SECONDARY                  2 02-SEP-2014 01:40:37 PM NONE

T     STATUS                     2 02-SEP-2014 01:40:37 PM NONE

T     SUBOBJECT_NAME           183 02-SEP-2014 01:40:37 PM NONE

T     TEMPORARY                  2 02-SEP-2014 01:40:37 PM NONE

T     TIMESTAMP               1473 02-SEP-2014 01:40:37 PM NONE

T     X                          3 02-SEP-2014 01:40:37 PM FREQUENCY

 

17 rows selected.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_part_col_statistics

  4  where table_name ='T'

  5  order by partition_name,column_name;

 

TABLE PARTI COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

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

T     P1    CREATED                 1206 02-SEP-2014 01:40:36 PM NONE

T     P1    DATA_OBJECT_ID          3517 02-SEP-2014 01:40:36 PM NONE

T     P1    EDITION_NAME               1 02-SEP-2014 01:40:36 PM NONE

T     P1    GENERATED                  2 02-SEP-2014 01:40:36 PM NONE

T     P1    LAST_DDL_TIME           1221 02-SEP-2014 01:40:36 PM NONE

T     P1    NAMESPACE                 16 02-SEP-2014 01:40:36 PM NONE

T     P1    OBJECT_ID              42338 02-SEP-2014 01:40:36 PM NONE

T     P1    OBJECT_NAME            40264 02-SEP-2014 01:40:36 PM NONE

T     P1    OBJECT_TYPE               38 02-SEP-2014 01:40:36 PM NONE

T     P1    ORDER_STATUS               2 02-SEP-2014 01:40:36 PM NONE

T     P1    OWNER                     31 02-SEP-2014 01:40:36 PM NONE

T     P1    SECONDARY                  2 02-SEP-2014 01:40:36 PM NONE

T     P1    STATUS                     2 02-SEP-2014 01:40:36 PM NONE

T     P1    SUBOBJECT_NAME           139 02-SEP-2014 01:40:36 PM NONE

T     P1    TEMPORARY                  2 02-SEP-2014 01:40:36 PM NONE

T     P1    TIMESTAMP               1292 02-SEP-2014 01:40:36 PM NONE

T     P1    X                          1 02-SEP-2014 01:40:36 PM FREQUENCY

T     P2    CREATED                 1207 02-SEP-2014 01:40:35 PM NONE

T     P2    DATA_OBJECT_ID          3494 02-SEP-2014 01:40:35 PM NONE

T     P2    EDITION_NAME               1 02-SEP-2014 01:40:35 PM NONE

T     P2    GENERATED                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    LAST_DDL_TIME           1223 02-SEP-2014 01:40:35 PM NONE

T     P2    NAMESPACE                 16 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_ID              42339 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_NAME            40324 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_TYPE               37 02-SEP-2014 01:40:35 PM NONE

T     P2    ORDER_STATUS               1 02-SEP-2014 01:40:35 PM NONE

T     P2    OWNER                     31 02-SEP-2014 01:40:35 PM NONE

T     P2    SECONDARY                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    STATUS                     2 02-SEP-2014 01:40:35 PM NONE

T     P2    SUBOBJECT_NAME           141 02-SEP-2014 01:40:35 PM NONE

T     P2    TEMPORARY                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    TIMESTAMP               1282 02-SEP-2014 01:40:35 PM NONE

T     P2    X                          1 02-SEP-2014 01:40:35 PM FREQUENCY

T     P3    CREATED                   10 02-SEP-2014 01:40:34 PM NONE

T     P3    DATA_OBJECT_ID           907 02-SEP-2014 01:40:34 PM NONE

T     P3    EDITION_NAME               0 02-SEP-2014 01:40:34 PM NONE

T     P3    GENERATED                  2 02-SEP-2014 01:40:34 PM NONE

T     P3    LAST_DDL_TIME             30 02-SEP-2014 01:40:34 PM NONE

T     P3    NAMESPACE                  4 02-SEP-2014 01:40:34 PM NONE

T     P3    OBJECT_ID               1000 02-SEP-2014 01:40:34 PM NONE

T     P3    OBJECT_NAME              995 02-SEP-2014 01:40:34 PM NONE

T     P3    OBJECT_TYPE                6 02-SEP-2014 01:40:34 PM NONE

T     P3    ORDER_STATUS               3 02-SEP-2014 01:40:34 PM NONE

T     P3    OWNER                      4 02-SEP-2014 01:40:34 PM NONE

T     P3    SECONDARY                  1 02-SEP-2014 01:40:34 PM NONE

T     P3    STATUS                     1 02-SEP-2014 01:40:34 PM NONE

T     P3    SUBOBJECT_NAME             0 02-SEP-2014 01:40:34 PM NONE

T     P3    TEMPORARY                  2 02-SEP-2014 01:40:34 PM NONE

T     P3    TIMESTAMP                 11 02-SEP-2014 01:40:34 PM NONE

T     P3    X                          1 02-SEP-2014 01:40:34 PM FREQUENCY

 

51 rows selected.

 

rajesh@ORA11G>

 

If you drop and recreate partition P3, update partition P1 again, and regather stats, you will see that partition P2 is not gathered again.

 

rajesh@ORA11G> alter table t truncate partition p3;

 

Table truncated.

 

rajesh@ORA11G> update t set order_status = 54

  2      where x = 1 and rownum = 1;

 

1 row updated.

 

rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,last_analyzed

  2  from user_tab_partitions

  3  where table_name ='T' ;

 

TABLE PARTI LAST_ANALYZED

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

T     P1    02-SEP-2014 01:40:40 PM

T     P2    02-SEP-2014 01:40:36 PM

T     P3    02-SEP-2014 01:40:38 PM

 

3 rows selected.

 

rajesh@ORA11G>

 

This confirms P1 and P3 got re-gathered without disturbing Partition P2.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_TAB_col_statistics

  4  where table_name ='T'

  5  order by column_name;

 

TABLE COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

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

T     CREATED                 1318 02-SEP-2014 01:40:40 PM NONE

T     DATA_OBJECT_ID          6978 02-SEP-2014 01:40:40 PM NONE

T     EDITION_NAME               1 02-SEP-2014 01:40:40 PM NONE

T     GENERATED                  2 02-SEP-2014 01:40:40 PM NONE

T     LAST_DDL_TIME           1383 02-SEP-2014 01:40:40 PM NONE

T     NAMESPACE                 17 02-SEP-2014 01:40:40 PM NONE

T     OBJECT_ID              84677 02-SEP-2014 01:40:40 PM NONE

T     OBJECT_NAME            49892 02-SEP-2014 01:40:40 PM NONE

T     OBJECT_TYPE               39 02-SEP-2014 01:40:40 PM NONE

T     ORDER_STATUS               3 02-SEP-2014 01:40:40 PM NONE

T     OWNER                     31 02-SEP-2014 01:40:40 PM NONE

T     SECONDARY                  2 02-SEP-2014 01:40:40 PM NONE

T     STATUS                     2 02-SEP-2014 01:40:40 PM NONE

T     SUBOBJECT_NAME           183 02-SEP-2014 01:40:40 PM NONE

T     TEMPORARY                  2 02-SEP-2014 01:40:40 PM NONE

T     TIMESTAMP               1473 02-SEP-2014 01:40:40 PM NONE

T     X                          2 02-SEP-2014 01:40:40 PM FREQUENCY

 

17 rows selected.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_part_col_statistics

  4  where table_name ='T'

  5  order by partition_name,column_name;

 

TABLE PARTI COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

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

T     P1    CREATED                 1206 02-SEP-2014 01:40:38 PM NONE

T     P1    DATA_OBJECT_ID          3517 02-SEP-2014 01:40:38 PM NONE

T     P1    EDITION_NAME               1 02-SEP-2014 01:40:38 PM NONE

T     P1    GENERATED                  2 02-SEP-2014 01:40:38 PM NONE

T     P1    LAST_DDL_TIME           1221 02-SEP-2014 01:40:38 PM NONE

T     P1    NAMESPACE                 16 02-SEP-2014 01:40:38 PM NONE

T     P1    OBJECT_ID              42338 02-SEP-2014 01:40:38 PM NONE

T     P1    OBJECT_NAME            40264 02-SEP-2014 01:40:38 PM NONE

T     P1    OBJECT_TYPE               38 02-SEP-2014 01:40:38 PM NONE

T     P1    ORDER_STATUS               2 02-SEP-2014 01:40:38 PM NONE

T     P1    OWNER                     31 02-SEP-2014 01:40:38 PM NONE

T     P1    SECONDARY                  2 02-SEP-2014 01:40:38 PM NONE

T     P1    STATUS                     2 02-SEP-2014 01:40:38 PM NONE

T     P1    SUBOBJECT_NAME           139 02-SEP-2014 01:40:38 PM NONE

T     P1    TEMPORARY                  2 02-SEP-2014 01:40:38 PM NONE

T     P1    TIMESTAMP               1292 02-SEP-2014 01:40:38 PM NONE

T     P1    X                          1 02-SEP-2014 01:40:38 PM FREQUENCY

T     P2    CREATED                 1207 02-SEP-2014 01:40:35 PM NONE

T     P2    DATA_OBJECT_ID          3494 02-SEP-2014 01:40:35 PM NONE

T     P2    EDITION_NAME               1 02-SEP-2014 01:40:35 PM NONE

T     P2    GENERATED                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    LAST_DDL_TIME           1223 02-SEP-2014 01:40:35 PM NONE

T     P2    NAMESPACE                 16 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_ID              42339 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_NAME            40324 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_TYPE               37 02-SEP-2014 01:40:35 PM NONE

T     P2    ORDER_STATUS               1 02-SEP-2014 01:40:35 PM NONE

T     P2    OWNER                     31 02-SEP-2014 01:40:35 PM NONE

T     P2    SECONDARY                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    STATUS                     2 02-SEP-2014 01:40:35 PM NONE

T     P2    SUBOBJECT_NAME           141 02-SEP-2014 01:40:35 PM NONE

T     P2    TEMPORARY                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    TIMESTAMP               1282 02-SEP-2014 01:40:35 PM NONE

T     P2    X                          1 02-SEP-2014 01:40:35 PM FREQUENCY

T     P3    CREATED                    0 02-SEP-2014 01:40:38 PM NONE

T     P3    DATA_OBJECT_ID             0 02-SEP-2014 01:40:38 PM NONE

T     P3    EDITION_NAME               0 02-SEP-2014 01:40:38 PM NONE

T     P3    GENERATED                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    LAST_DDL_TIME              0 02-SEP-2014 01:40:38 PM NONE

T     P3    NAMESPACE                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    OBJECT_ID                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    OBJECT_NAME                0 02-SEP-2014 01:40:38 PM NONE

T     P3    OBJECT_TYPE                0 02-SEP-2014 01:40:38 PM NONE

T     P3    ORDER_STATUS               0 02-SEP-2014 01:40:38 PM NONE

T     P3    OWNER                      0 02-SEP-2014 01:40:38 PM NONE

T     P3    SECONDARY                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    STATUS                     0 02-SEP-2014 01:40:38 PM NONE

T     P3    SUBOBJECT_NAME             0 02-SEP-2014 01:40:38 PM NONE

T     P3    TEMPORARY                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    TIMESTAMP                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    X                          0 02-SEP-2014 01:40:38 PM NONE

 

51 rows selected.

 

rajesh@ORA11G>

 

This change in behavior is due to the fix for Bug 13816060 (replaced by Bug 16726844 ). Before this fix, in 11.2.0.2 for example, although partition P2 is not regathered, some stats on the global partition are gathered instead of being aggregated, which can be much more expensive.