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

Character Set Migration

Migrate your existing database to AL32UTF8

Any Single Byte Characterset (like US7ASCII, WE8DEC)

In general :

= The DMU tool is considered more user-friendly, can do the conversion without exp/imp and is easier when data needs correction and when downtime is limited.

= Full exp/imp into a new AL32UTF8 DB is noramlly used when migration to a new server/OS or when downtime is not huge issue.

= csscan / csalter (10g/11g) or Alter Database Character Set (8i/9i) / partial exp/imp is normally preferred by people who like to script things and when downtime is limited.

Subsets and Supersets

Subset  Superset

US7ASCII
UTF8  AL32UTF8

Current Character Set    New Character Set    New Character Set is strict superset?  
US7ASCII             WE8ISO8859P1       yes  
US7ASCII             ALT24UTFFSS        yes  
US7ASCII             UTF8           yes

ref : https://oracle-base.com/articles/10g/character-set-migration