Just added bunch of rows in partition P4 and updated 1 row in partition P1, but why Stats gathering was done on P2 and P3 (where no dml happened on that partition). Please help me to understand.
{code}
rajesh@ORA11G> create table t
2 partition by list(x)
3 ( partition p1 values (1) )
4 as
5 select a.*, 1 as x
6 from all_objects a ;
Table created.
rajesh@ORA11G>
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> column incremental format a10
rajesh@ORA11G> column granularity format a10
rajesh@ORA11G> column publish format a10
rajesh@ORA11G> column estimate_percent format a10
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_P
---------- ---------- ---------- ----------
TRUE AUTO TRUE DBMS_STATS
.AUTO_SAMP
LE_SIZE
1 row selected.
rajesh@ORA11G>
rajesh@ORA11G> exec dbms_stats.gather_table_Stats(user,'T');
PL/SQL procedure successfully completed.
rajesh@ORA11G> select table_name,partition_name,last_analyzed
2 from user_tab_partitions
3 where table_name ='T' ;
TABLE_NAME PARTITION_ LAST_ANALYZED
---------- ---------- -----------------------
T P1 11-JUL-2014 09:42:26 PM
1 row selected.
rajesh@ORA11G>
rajesh@ORA11G> select table_name,column_name,num_distinct,
2 last_analyzed
3 from user_TAB_col_statistics
4 where table_name ='T'
5 order by column_name;
TABLE_NAME COLUMN_NAME NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
T CREATED 1339 11-JUL-2014 09:42:26 PM
T DATA_OBJECT_ID 7066 11-JUL-2014 09:42:26 PM
T EDITION_NAME 0 11-JUL-2014 09:42:26 PM
T GENERATED 2 11-JUL-2014 09:42:26 PM
T LAST_DDL_TIME 1401 11-JUL-2014 09:42:26 PM
T NAMESPACE 17 11-JUL-2014 09:42:26 PM
T OBJECT_ID 84750 11-JUL-2014 09:42:26 PM
T OBJECT_NAME 49944 11-JUL-2014 09:42:26 PM
T OBJECT_TYPE 39 11-JUL-2014 09:42:26 PM
T OWNER 32 11-JUL-2014 09:42:26 PM
T SECONDARY 2 11-JUL-2014 09:42:26 PM
T STATUS 2 11-JUL-2014 09:42:26 PM
T SUBOBJECT_NAME 195 11-JUL-2014 09:42:26 PM
T TEMPORARY 2 11-JUL-2014 09:42:26 PM
T TIMESTAMP 1494 11-JUL-2014 09:42:26 PM
T X 1 11-JUL-2014 09:42:26 PM
16 rows selected.
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> alter table t add partition p2 values(2);
Table altered.
rajesh@ORA11G> alter table t add partition p3 values(3);
Table altered.
rajesh@ORA11G> insert into t
2 select a.*, 2 as x
3 from all_objects a ;
84752 rows created.
rajesh@ORA11G>
rajesh@ORA11G> insert into t
2 select a.*, 3 as x
3 from all_objects a ;
84752 rows created.
rajesh@ORA11G>
rajesh@ORA11G> commit;
Commit complete.
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_NAME PARTITION_ LAST_ANALYZED
---------- ---------- -----------------------
T P1 11-JUL-2014 09:42:26 PM
T P2 11-JUL-2014 09:45:35 PM
T P3 11-JUL-2014 09:45:35 PM
3 rows selected.
rajesh@ORA11G>
rajesh@ORA11G> select table_name,column_name,num_distinct,
2 last_analyzed
3 from user_TAB_col_statistics
4 where table_name ='T'
5 order by column_name;
TABLE_NAME COLUMN_NAME NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
T CREATED 1341 11-JUL-2014 09:45:35 PM
T DATA_OBJECT_ID 7101 11-JUL-2014 09:45:35 PM
T EDITION_NAME 0 11-JUL-2014 09:45:35 PM
T GENERATED 2 11-JUL-2014 09:45:35 PM
T LAST_DDL_TIME 1404 11-JUL-2014 09:45:35 PM
T NAMESPACE 17 11-JUL-2014 09:45:35 PM
T OBJECT_ID 85920 11-JUL-2014 09:45:35 PM
T OBJECT_NAME 49944 11-JUL-2014 09:45:35 PM
T OBJECT_TYPE 39 11-JUL-2014 09:45:35 PM
T OWNER 32 11-JUL-2014 09:45:35 PM
T SECONDARY 2 11-JUL-2014 09:45:35 PM
T STATUS 2 11-JUL-2014 09:45:35 PM
T SUBOBJECT_NAME 230 11-JUL-2014 09:45:35 PM
T TEMPORARY 2 11-JUL-2014 09:45:35 PM
T TIMESTAMP 1497 11-JUL-2014 09:45:35 PM
T X 3 11-JUL-2014 09:45:35 PM
16 rows selected.
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> select dbms_stats.get_prefs('STALE_PERCENT',user,'T') stale_percent
2 from dual ;
STALE_PERCENT
----------------------------------------------------------------------------------------------------------------
10
1 row selected.
rajesh@ORA11G> column stale_percent format a10
rajesh@ORA11G> select dbms_stats.get_prefs('STALE_PERCENT',user,'T') stale_percent
2 from dual ;
STALE_PERC
----------
10
1 row selected.
rajesh@ORA11G> select dbms_stats.get_prefs('STALE_PERCENT',user,'T')
2 as stale_percent
3 from dual ;
STALE_PERC
----------
10
1 row selected.
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> alter table t add partition p4 values(4);
Table altered.
rajesh@ORA11G> update t set owner = lower(owner) where x = 1 and rownum = 1 ;
1 row updated.
rajesh@ORA11G>
rajesh@ORA11G> insert into t
2 select a.*, 4 as x
3 from all_objects a ;
84753 rows created.
rajesh@ORA11G>
rajesh@ORA11G> commit;
Commit complete.
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_NAME PARTITION_ LAST_ANALYZED
---------- ---------- -----------------------
T P1 11-JUL-2014 09:55:17 PM
T P2 11-JUL-2014 09:55:13 PM
T P3 11-JUL-2014 09:55:10 PM
T P4 11-JUL-2014 09:55:21 PM
4 rows selected.
rajesh@ORA11G> select table_name,column_name,num_distinct,
2 last_analyzed
3 from user_TAB_col_statistics
4 where table_name ='T'
5 order by column_name;
TABLE_NAME COLUMN_NAME NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
T CREATED 1342 11-JUL-2014 09:55:22 PM
T DATA_OBJECT_ID 7102 11-JUL-2014 09:55:22 PM
T EDITION_NAME 0 11-JUL-2014 09:55:22 PM
T GENERATED 2 11-JUL-2014 09:55:22 PM
T LAST_DDL_TIME 1405 11-JUL-2014 09:55:22 PM
T NAMESPACE 17 11-JUL-2014 09:55:22 PM
T OBJECT_ID 85920 11-JUL-2014 09:55:22 PM
T OBJECT_NAME 49944 11-JUL-2014 09:55:22 PM
T OBJECT_TYPE 39 11-JUL-2014 09:55:22 PM
T OWNER 33 11-JUL-2014 09:55:22 PM
T SECONDARY 2 11-JUL-2014 09:55:22 PM
T STATUS 2 11-JUL-2014 09:55:22 PM
T SUBOBJECT_NAME 230 11-JUL-2014 09:55:22 PM
T TEMPORARY 2 11-JUL-2014 09:55:22 PM
T TIMESTAMP 1498 11-JUL-2014 09:55:22 PM
T X 4 11-JUL-2014 09:55:22 PM
16 rows selected.
rajesh@ORA11G>
{code}