SELECT extension_name, extension
WHERE table_name = ‘TAB1’;
When incremental statistics maintenance is enabled for a partitioned table, oracle accurately generated global level statistics by aggregating partition level statistics.
SELECT DBMS_STATS.GET_PREFS (‘INCREMENTAL’,’FRI_INDEX’,’SALES’)
select * from dba_tab_stats_prefs;
We can also confirm that we really did use incremental statistics by querying the dictionary table sys.HIST_HEAD$, which should have an entry for each column of that specific table.
The reason incremental statistics is not working in your example is because you are manually setting ESTIMATE_PERCENT to 100. In order for incremental statistics to work, the ESTIMATE_PERCENT must be set to the default ‘AUTO_SAMPLE_SIZE.
In Oracle Database 11g, we avoid scanning the whole table when computing global statistics by deriving the global statistics from the partition statistics. Some of the statistics can be derived easily and accurately from partition statistics.
ref : https://blogs.oracle.com/optimizer/entry/incremental_statistics_maintenance_what_statistics%20