Extended and Incremental Statistics

EXTENDED STATISTICS

DBMS_STATS.seed_col_usage()
DBMS_STATS.report_col_usage()

DBMS_STATS.create_extended_stats()
DBMS_STATS.gather_table_stats()

SELECT extension_name, extension
FROM   dba_stat_extensions
WHERE  table_name = ‘TAB1’;
ref :
http://dbaora.com/statistics-improvements-oracle-database-11g-release-2-11-2/
https://iiotzov.wordpress.com/tag/dbms_stats-report_col_usage/
https://oracle-base.com/articles/11g/extended-statistics-enhancements-11gr2

INCREMENTAL STATISTICS

When incremental statistics maintenance is enabled for a partitioned table, oracle accurately generated global level  statistics by aggregating partition level statistics.
dbms_stats.set_table_prefs(‘FRI_INDEX’,’SALES’,’INCREMENTAL’,’TRUE’);

SELECT DBMS_STATS.GET_PREFS (‘INCREMENTAL’,’FRI_INDEX’,’SALES’)
FROM DUAL
;

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

https://blogs.oracle.com/optimizer/entry/maintaining_statistics_on_large_partitioned_tables

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s