SQL> SQL> SQL> col PARTITION_NAME format a10 SQL> col PUBLISH format a8 SQL> col INCREMENTAL_STALENESS format a35 SQL> col INCREMENTAL_LEVEL format a12 SQL> col INCREMENTAL format a8 SQL> col STALE_PERCENT format a4 SQL> col METHOD_OPT format a22 SQL> SQL> col TABNAME format a12 SQL> col PARTNAME format a10 SQL> col COLNAME format a10 SQL> col SYNOPSIS_CREATED format a18 SQL> SQL> col NAME format a15 SQL> col INCREMENTAL format a15 SQL> SQL> col TABLE_NAME format a8 SQL> col GLOBAL_STATS format a3 SQL> col USER_STATS format a3 SQL> col HISTOGRAM format a10 SQL> SQL> col LAST_ANALYZE format a18 SQL> SQL> drop table INCSTATS 2 / Table dropped. Elapsed: 00:00:00.13 SQL> SQL> CREATE TABLE INCSTATS 2 PARTITION BY RANGE (val) 3 ( 4 PARTITION P_100 VALUES LESS THAN (100) , 5 PARTITION P_200 VALUES LESS THAN (200) , 6 PARTITION P_300 VALUES LESS THAN (300) , 7 PARTITION P_400 VALUES LESS THAN (400) , 8 PARTITION P_500 VALUES LESS THAN (500) , 9 PARTITION P_MAX VALUES LESS THAN (MAXVALUE) 10 ) 11 ENABLE ROW MOVEMENT 12 LOGGING 13 PARALLEL 8 14 as SELECT rownum as VAL 15 FROM dual CONNECT BY level < 450 16 / Table created. Elapsed: 00:00:00.23 SQL> SQL> SQL> -- Enable Incremental feature SQL> BEGIN 2 DBMS_STATS.SET_TABLE_PREFS ( 3 ownname => USER, 4 tabname => 'INCSTATS', 5 pname => 'INCREMENTAL', 6 pvalue => 'TRUE'); 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.04 SQL> SQL> -- no histograms SQL> BEGIN 2 DBMS_STATS.SET_TABLE_PREFS ( 3 ownname => USER, 4 tabname => 'INCSTATS', 5 pname => 'METHOD_OPT', 6 pvalue => 'FOR ALL COLUMNS SIZE 1'); 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> SQL> -- granularity=>'APPROX_GLOBAL AND PARTITION' SQL> BEGIN 2 DBMS_STATS.SET_TABLE_PREFS ( 3 ownname => USER, 4 tabname => 'INCSTATS', 5 pname => 'GRANULARITY', 6 pvalue => 'APPROX_GLOBAL AND PARTITION'); 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> SQL> -- Set synopsis creation at partition level SQL> BEGIN 2 DBMS_STATS.SET_TABLE_PREFS ( 3 ownname => USER, 4 tabname => 'INCSTATS', 5 pname => 'INCREMENTAL_LEVEL', 6 pvalue => 'PARTITION'); 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> SQL> SQL> BEGIN 2 DBMS_STATS.SET_TABLE_PREFS ( 3 ownname => USER, 4 tabname => 'INCSTATS', 5 pname => 'PUBLISH', 6 pvalue => 'TRUE'); 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> SQL> BEGIN 2 DBMS_STATS.SET_TABLE_PREFS ( 3 ownname => USER, 4 tabname => 'INCSTATS', 5 pname => 'INCREMENTAL_STALENESS', 6 pvalue => 'USE_STALE_PERCENT, USE_LOCKED_STATS'); 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 SQL> SQL> BEGIN 2 DBMS_STATS.SET_TABLE_PREFS ( 3 ownname => USER, 4 tabname => 'INCSTATS', 5 pname => 'STALE_PERCENT', 6 pvalue => '15'); 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> SQL> SQL> SQL> SELECT dbms_stats.get_prefs('PUBLISH',USER,'INCSTATS') "PUBLISH" 2 , dbms_stats.get_prefs('INCREMENTAL_STALENESS',USER,'INCSTATS') "INCREMENTAL_STALENESS" 3 , dbms_stats.get_prefs('INCREMENTAL_LEVEL',USER,'INCSTATS') "INCREMENTAL_LEVEL" 4 , dbms_stats.get_prefs('INCREMENTAL',USER,'INCSTATS') "INCREMENTAL" 5 , dbms_stats.get_prefs('STALE_PERCENT',USER,'INCSTATS') "STALE_PERCENT" 6 , dbms_stats.get_prefs('METHOD_OPT',USER,'INCSTATS') "METHOD_OPT" 7 FROM dual 8 / PUBLISH INCREMENTAL_STALENESS INCREMENTAL_ INCREMENTAL STAL METHOD_OPT -------- ----------------------------------- ------------ --------------- ---- ---------------------- TRUE USE_STALE_PERCENT, USE_LOCKED_STATS PARTITION TRUE 15 FOR ALL COLUMNS SIZE 1 Elapsed: 00:00:00.03 SQL> SQL> SQL> -- gather all stats SQL> -- SQL> BEGIN 2 dbms_stats.flush_database_monitoring_info; 3 dbms_stats.gather_table_stats(USER,'INCSTATS'); 4 END; 5 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.30 SQL> SQL> SQL> -- gather partition stats for all partitions SQL> -- SQL> BEGIN 2 dbms_stats.flush_database_monitoring_info; 3 dbms_stats.gather_table_stats(USER,'INCSTATS'); 4 for i in (SELECT partition_name pname 5 FROM user_tab_partitions 6 WHERE table_name = 'INCSTATS' 7 order by partition_position) loop 8 dbms_output.put_line('P-Name: '||i.pname); 9 dbms_stats.gather_table_stats(ownname => USER 10 , tabname => 'INCSTATS' 11 , partname => i.pname 12 , granularity=>'PARTITION'); 13 -- wait 2 sec so every part. will have different timestamp 14 dbms_lock.sleep(2); 15 end loop; 16 END; 17 / P-Name: P_100 P-Name: P_200 P-Name: P_300 P-Name: P_400 P-Name: P_500 P-Name: P_MAX PL/SQL procedure successfully completed. Elapsed: 00:00:12.71 SQL> SQL> SQL> -- show synopsis SQL> -- SQL> SELECT o.name "TABNAME", 2 p.subname "PARTNAME", 3 c.name "COLNAME", 4 TO_CHAR(h.analyzetime, 'DD.MM.YY HH24:MI:SS') "SYNOPSIS_CREATED" 5 FROM sys.wri$_optstat_synopsis_head$ h, 6 sys.obj$ o, 7 sys.user$ u, 8 sys.col$ c, 9 ( ( SELECT tabpart$.bo# BO#, 10 tabpart$.obj# OBJ# 11 FROM sys.tabpart$ tabpart$ ) 12 UNION ALL 13 ( SELECT tabcompart$.bo# BO#, 14 tabcompart$.obj# OBJ# 15 FROM sys.tabcompart$ tabcompart$ ) ) tp, 16 sys.obj$ p 17 WHERE u.name = USER AND 18 o.name = 'INCSTATS' AND 19 tp.obj# = p.obj# AND 20 h.bo# = tp.bo# AND 21 h.group# = tp.obj# * 2 AND 22 h.bo# = c.obj#(+) AND 23 h.intcol# = c.intcol#(+) AND 24 o.owner# = u.user# AND 25 h.bo# = o.obj# 26 ORDER BY 4,1,2,3 27 / TABNAME PARTNAME COLNAME SYNOPSIS_CREATED ------------ ---------- ---------- ------------------ INCSTATS P_100 VAL 26.11.17 19:40:33 INCSTATS P_200 VAL 26.11.17 19:40:35 INCSTATS P_300 VAL 26.11.17 19:40:37 INCSTATS P_400 VAL 26.11.17 19:40:39 INCSTATS P_500 VAL 26.11.17 19:40:41 INCSTATS P_MAX VAL 26.11.17 19:40:43 6 rows selected. Elapsed: 00:00:00.02 SQL> SQL> SQL> SELECT partition_name 2 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 3 , num_rows 4 FROM user_tab_partitions 5 WHERE table_name = 'INCSTATS' 6 union all 7 SELECT table_name 8 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 9 , num_rows 10 FROM user_tables 11 WHERE table_name = 'INCSTATS' 12 ORDER BY 2,1 13 / PARTITION_ LAST_ANALYZE NUM_ROWS ---------- ------------------ ---------- INCSTATS 26.11.17 19:40:33 449 P_100 26.11.17 19:40:33 99 P_200 26.11.17 19:40:35 100 P_300 26.11.17 19:40:37 100 P_400 26.11.17 19:40:39 100 P_500 26.11.17 19:40:41 50 P_MAX 26.11.17 19:40:43 0 7 rows selected. Elapsed: 00:00:00.12 SQL> SQL> SELECT table_name 2 , global_stats 3 , user_stats 4 , histogram 5 FROM user_tab_col_statistics 6 WHERE table_name like 'INCSTATS%' 7 ORDER BY 1 8 / TABLE_NA GLO USE HISTOGRAM -------- --- --- ---------- INCSTATS YES NO NONE Elapsed: 00:00:00.21 SQL> SQL> SQL> -- gather table stats SQL> -- no DML => no re-gather stats on PARTITION, only on GLOBALS SQL> -- SQL> BEGIN 2 dbms_stats.flush_database_monitoring_info; 3 dbms_stats.gather_table_stats(ownname => USER 4 , tabname => 'INCSTATS' 5 , granularity=>'APPROX_GLOBAL AND PARTITION'); 6 END; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.10 SQL> SQL> SELECT partition_name 2 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 3 , num_rows 4 FROM user_tab_partitions 5 WHERE table_name = 'INCSTATS' 6 union all 7 SELECT table_name 8 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 9 , num_rows 10 FROM user_tables 11 WHERE table_name = 'INCSTATS' 12 ORDER BY 2,1 13 / PARTITION_ LAST_ANALYZE NUM_ROWS ---------- ------------------ ---------- P_100 26.11.17 19:40:33 99 P_200 26.11.17 19:40:35 100 P_300 26.11.17 19:40:37 100 P_400 26.11.17 19:40:39 100 P_500 26.11.17 19:40:41 50 P_MAX 26.11.17 19:40:43 0 INCSTATS 26.11.17 19:40:46 449 7 rows selected. Elapsed: 00:00:00.14 SQL> SQL> SQL> -- show if incremental is on SQL> -- SQL> SELECT o.name as NAME 2 , decode(bitand(h.spare2, 8), 8, 'yes', 'no') INCREMENTAL 3 FROM sys.hist_head$ h 4 , sys.obj$ o 5 WHERE h.obj# = o.obj# 6 AND o.name = 'INCSTATS' 7 AND o.subname is null 8 / NAME INCREMENTAL --------------- --------------- INCSTATS yes Elapsed: 00:00:00.01 SQL> SQL> SQL> SQL> -- wait 11 secs SQL> -- SQL> BEGIN 2 dbms_lock.sleep(11); 3 END; 4 / PL/SQL procedure successfully completed. Elapsed: 00:00:11.00 SQL> SQL> -- re-gather stats w/o any changes SQL> -- no DML => no re-gather stats on PARTITION, only on GLOBALS SQL> -- SQL> BEGIN 2 dbms_stats.flush_database_monitoring_info; 3 dbms_stats.gather_table_stats(ownname => USER 4 , tabname => 'INCSTATS' 5 , granularity=>'APPROX_GLOBAL AND PARTITION'); 6 END; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.08 SQL> SQL> SQL> SELECT partition_name 2 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 3 , num_rows 4 FROM user_tab_partitions 5 WHERE table_name = 'INCSTATS' 6 union all 7 SELECT table_name 8 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 9 , num_rows 10 FROM user_tables 11 WHERE table_name = 'INCSTATS' 12 ORDER BY 2,1 13 / PARTITION_ LAST_ANALYZE NUM_ROWS ---------- ------------------ ---------- P_100 26.11.17 19:40:33 99 P_200 26.11.17 19:40:35 100 P_300 26.11.17 19:40:37 100 P_400 26.11.17 19:40:39 100 P_500 26.11.17 19:40:41 50 P_MAX 26.11.17 19:40:43 0 INCSTATS 26.11.17 19:40:57 449 7 rows selected. Elapsed: 00:00:00.01 SQL> SQL> SELECT table_name 2 , global_stats 3 , user_stats 4 , histogram 5 FROM user_tab_col_statistics 6 WHERE table_name like 'INCSTATS%' 7 ORDER BY 1 8 / TABLE_NA GLO USE HISTOGRAM -------- --- --- ---------- INCSTATS YES NO NONE Elapsed: 00:00:00.00 SQL> SQL> SQL> -- insert new rows SQL> -- into partition P_MAX SQL> -- SQL> BEGIN 2 for i in (SELECT 999 + rownum as NR 3 FROM dual CONNECT BY level < 21) loop 4 INSERT INTO INCSTATS VALUES (i.nr); 5 end loop; 6 commit; 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> SQL> SQL> -- re-gather stats SQL> -- => should only the latest and GOBALS be changed SQL> -- SQL> BEGIN 2 dbms_stats.flush_database_monitoring_info; 3 dbms_stats.gather_table_stats(ownname => USER 4 , tabname => 'INCSTATS' 5 , granularity=>'APPROX_GLOBAL AND PARTITION'); 6 END; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.11 SQL> SQL> SQL> SELECT partition_name 2 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 3 , num_rows 4 FROM user_tab_partitions 5 WHERE table_name = 'INCSTATS' 6 union all 7 SELECT table_name 8 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 9 , num_rows 10 FROM user_tables 11 WHERE table_name = 'INCSTATS' 12 ORDER BY 2,1 13 / PARTITION_ LAST_ANALYZE NUM_ROWS ---------- ------------------ ---------- P_100 26.11.17 19:40:33 99 P_200 26.11.17 19:40:35 100 P_300 26.11.17 19:40:37 100 P_400 26.11.17 19:40:39 100 P_500 26.11.17 19:40:41 50 INCSTATS 26.11.17 19:40:57 469 P_MAX 26.11.17 19:40:57 20 7 rows selected. Elapsed: 00:00:00.00 SQL> SQL> SELECT table_name 2 , global_stats 3 , user_stats 4 , histogram 5 FROM user_tab_col_statistics 6 WHERE table_name like 'INCSTATS%' 7 ORDER BY 1 8 / TABLE_NA GLO USE HISTOGRAM -------- --- --- ---------- INCSTATS YES NO NONE Elapsed: 00:00:00.22 SQL> SQL> SQL> -- update less than stale_percent rows SQL> -- => no gather stats on partition level SQL> -- SQL> update INCSTATS 2 set val=val+1 3 where val between 101 and 109 4 / 9 rows updated. Elapsed: 00:00:00.03 SQL> commit 2 / Commit complete. Elapsed: 00:00:00.01 SQL> SQL> BEGIN 2 dbms_stats.flush_database_monitoring_info; 3 dbms_stats.gather_table_stats(ownname => USER 4 , tabname => 'INCSTATS' 5 , granularity=>'APPROX_GLOBAL AND PARTITION'); 6 END; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.09 SQL> SQL> SELECT partition_name 2 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 3 , num_rows 4 FROM user_tab_partitions 5 WHERE table_name = 'INCSTATS' 6 union all 7 SELECT table_name 8 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 9 , num_rows 10 FROM user_tables 11 WHERE table_name = 'INCSTATS' 12 ORDER BY 2,1 13 / PARTITION_ LAST_ANALYZE NUM_ROWS ---------- ------------------ ---------- P_100 26.11.17 19:40:33 99 P_200 26.11.17 19:40:35 100 P_300 26.11.17 19:40:37 100 P_400 26.11.17 19:40:39 100 P_500 26.11.17 19:40:41 50 INCSTATS 26.11.17 19:40:57 469 P_MAX 26.11.17 19:40:57 20 7 rows selected. Elapsed: 00:00:00.00 SQL> SQL> SELECT table_name 2 , global_stats 3 , user_stats 4 , histogram 5 FROM user_tab_col_statistics 6 WHERE table_name like 'INCSTATS%' 7 ORDER BY 1 8 / TABLE_NA GLO USE HISTOGRAM -------- --- --- ---------- INCSTATS YES NO NONE Elapsed: 00:00:00.00 SQL> SQL> SQL> -- update greater than stale_percent rows SQL> -- => x gather stats on only ONE partition AND GLOBALS SQL> -- SQL> update INCSTATS 2 set val=val+1 3 where val between 101 and 160 4 / 60 rows updated. Elapsed: 00:00:00.03 SQL> commit 2 / Commit complete. Elapsed: 00:00:00.01 SQL> SQL> BEGIN 2 dbms_stats.flush_database_monitoring_info; 3 dbms_stats.gather_table_stats(ownname => USER 4 , tabname => 'INCSTATS' 5 , granularity=>'APPROX_GLOBAL AND PARTITION'); 6 END; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.09 SQL> SQL> SELECT partition_name 2 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 3 , num_rows 4 FROM user_tab_partitions 5 WHERE table_name = 'INCSTATS' 6 union all 7 SELECT table_name 8 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 9 , num_rows 10 FROM user_tables 11 WHERE table_name = 'INCSTATS' 12 ORDER BY 2,1 13 / PARTITION_ LAST_ANALYZE NUM_ROWS ---------- ------------------ ---------- P_100 26.11.17 19:40:33 99 P_300 26.11.17 19:40:37 100 P_400 26.11.17 19:40:39 100 P_500 26.11.17 19:40:41 50 INCSTATS 26.11.17 19:40:57 469 P_200 26.11.17 19:40:57 100 P_MAX 26.11.17 19:40:57 20 7 rows selected. Elapsed: 00:00:00.00 SQL> SQL> SELECT table_name 2 , global_stats 3 , user_stats 4 , histogram 5 FROM user_tab_col_statistics 6 WHERE table_name like 'INCSTATS%' 7 ORDER BY 1 8 / TABLE_NA GLO USE HISTOGRAM -------- --- --- ---------- INCSTATS YES NO NONE Elapsed: 00:00:00.00 SQL> SQL> -- no dml occured SQL> -- => no partition should re-gathered SQL> -- => ONLY GLOBALS SQL> -- SQL> BEGIN 2 dbms_stats.flush_database_monitoring_info; 3 dbms_stats.gather_table_stats(ownname => USER 4 , tabname => 'INCSTATS' 5 , granularity=>'APPROX_GLOBAL AND PARTITION'); 6 END; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.07 SQL> SQL> SELECT partition_name 2 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 3 , num_rows 4 FROM user_tab_partitions 5 WHERE table_name = 'INCSTATS' 6 union all 7 SELECT table_name 8 , TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze 9 , num_rows 10 FROM user_tables 11 WHERE table_name = 'INCSTATS' 12 ORDER BY 2,1; PARTITION_ LAST_ANALYZE NUM_ROWS ---------- ------------------ ---------- P_100 26.11.17 19:40:33 99 P_300 26.11.17 19:40:37 100 P_400 26.11.17 19:40:39 100 P_500 26.11.17 19:40:41 50 P_200 26.11.17 19:40:57 100 P_MAX 26.11.17 19:40:57 20 INCSTATS 26.11.17 19:40:58 469 7 rows selected. Elapsed: 00:00:00.00 SQL> SQL> SELECT table_name 2 , global_stats 3 , user_stats 4 , histogram 5 FROM user_tab_col_statistics 6 WHERE table_name like 'INCSTATS%' 7 ORDER BY 1; TABLE_NA GLO USE HISTOGRAM -------- --- --- ---------- INCSTATS YES NO NONE Elapsed: 00:00:00.00 SQL> SQL> SQL> -- show if incremental is on SQL> -- SQL> SELECT o.name as NAME 2 , decode(bitand(h.spare2, 8), 8, 'yes', 'no') INCREMENTAL 3 FROM sys.hist_head$ h 4 , sys.obj$ o 5 WHERE h.obj# = o.obj# 6 AND o.name = 'INCSTATS' 7 AND o.subname is null; NAME INCREMENTAL --------------- --------------- INCSTATS yes Elapsed: 00:00:00.01 SQL> SQL> SQL> -- show synopsis SQL> -- SQL> SELECT o.name "TABNAME", 2 p.subname "PARTNAME", 3 c.name "COLNAME", 4 TO_CHAR(h.analyzetime, 'DD.MM.YY HH24:MI:SS') "SYNOPSIS_CREATED" 5 FROM sys.wri$_optstat_synopsis_head$ h, 6 sys.obj$ o, 7 sys.user$ u, 8 sys.col$ c, 9 ( ( SELECT tabpart$.bo# BO#, 10 tabpart$.obj# OBJ# 11 FROM sys.tabpart$ tabpart$ ) 12 UNION ALL 13 ( SELECT tabcompart$.bo# BO#, 14 tabcompart$.obj# OBJ# 15 FROM sys.tabcompart$ tabcompart$ ) ) tp, 16 sys.obj$ p 17 WHERE u.name = USER AND 18 o.name = 'INCSTATS' AND 19 tp.obj# = p.obj# AND 20 h.bo# = tp.bo# AND 21 h.group# = tp.obj# * 2 AND 22 h.bo# = c.obj#(+) AND 23 h.intcol# = c.intcol#(+) AND 24 o.owner# = u.user# AND 25 h.bo# = o.obj# 26 ORDER BY 4,1,2,3 27 / TABNAME PARTNAME COLNAME SYNOPSIS_CREATED ------------ ---------- ---------- ------------------ INCSTATS P_100 VAL 26.11.17 19:40:33 INCSTATS P_300 VAL 26.11.17 19:40:37 INCSTATS P_400 VAL 26.11.17 19:40:39 INCSTATS P_500 VAL 26.11.17 19:40:41 INCSTATS P_200 VAL 26.11.17 19:40:57 INCSTATS P_MAX VAL 26.11.17 19:40:57 6 rows selected. Elapsed: 00:00:00.00 SQL> SQL> spool off