1、收集统计信息的初始化参数：STATISTICS_LEVCEL = TYPICAL or ALL, for BASIC is disable
2、自动收集统计信息：Select * from DBA_SCHEDULER Where JOB_NAME = 'GATHER_STATS_JOB';
      禁止：DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
3、动态采样的初始化参数：OPTIMIZER_DYNAMIC_SAMPLING = 2
4、收集固定对象的统计数据：DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
      系统Schema的统计数据：DBMS_STATS.GATHER_DICTIONARY_STATS
5、动态采样比例：ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE 获取最大性能增益
6、采样的并行度：DEGREE => DBMS_STATS.AUTO_DEGREE
7、分区对象的采样：GRANULARITY
8、列统计信息和直方图：METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'
9、确定Stale统计信息：Select * from USER_TAB_MODIFICATIONS;
      立即刷新内存中的监控信息：DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
10、系统统计信息System Statistics：分为Workload Statistics和NoWorkload Statistics。描述系统硬件特性，如：IO、CPU性能/利用率；提供给优化器作为执行计划的分析参数。
        过程：DBMS_STATS.GATHER_SYSTEM_STATISTICS
        权限：DBA
        角色：GATHER_SYSTEM_STATISTICS
11、负载统计信息Workload Statistics：收集单块/多块读取时间sreadtim/mreadtim、mbrc、CPU速度cpuspeed、最大系统流量、平均附加流量。

    These values are implemented through counters that change when the buffer cache completes synchronous read requests. Since the counters are in the buffer cache, they include not only I/O delays, but also waits related to latch contention and task switching. Workload statistics thus depend on the
activity the system had during the workload window.

        收集方式：DBMS_STATS.GATHER_SYSTEM_STATS('start');
                            DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval=>'分钟数‘);
                            DBMS_STATS.GATHER_SYSTEM_STATS('stop');
                            DBMS_STATS.DELETE_SYSTEM_STATS();
12、多块读取计数Multiblock Read Count mbrc：10.2使用该值进行Full Table Scan计算。优化器假设mbrc=8，而不是使用DB_FILE_MULTIBLOCK_READ_COUNT参数值。在OLTP系统中，有可能mbrc、mreadtim值不能被采集到，那么sreadtim、cpuspeed可能会被用于计算FTS的代价；或者强制FTS进入串行模式以计算代价。
13、非负载统计信息NoWorkload Statistics：收集IO传输速度、IO搜寻时间、CPU速度cpuspeednw。Workload Statistics和NoWorkload Statistics的最大不同是收集方式：Workload Statistics通过统计数据库活动的计数器来获取，NoWorkload Statistics通过随机读取数据文件来获取。
        ioseektim：定位磁盘到读取数据的时间。5ms - 15ms
        iotrfspeed：从IO系统读取数据的速度。
        收集方式：DBMS_STATS.GATHER_SYSTEM_STATS();

管理统计信息
1、收集统计信息的历史记录
      DBA_OPTSTAT_OPERATIONS：存放Database和Schema层级的统计操作记录
      USER_TAB_STATS_HISTORY：存放Table层级的统计操作记录
2、管理统计信息的历史记录：老的统计信息定期被清除（默认32天），使用过程DBMS_STATS.ALTER_STATS_HISTORY_RETENTION修改清除间隔
       DBMS_STATS.PURGE_STATS：手工清除统计信息
       DBMS_STATS.GET_STATS_HISTORY_RETENTION
       DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY
3、输出、输入统计信息
    源库：
       DBMS_STATS.CREATE_STAT_TABLE(’username‘, 'stats_table_name')
       DBMS_STATS.EXPORT_*_STATS()
    将表“username.stats_table_name”exp/imp
    目标库：
       DBMS_STATS.IMPORT_*_STATS()
       DBMS_STATS.DROP_STAT_TABLE()
ZT：http://www.oracle-base.com/articles/8i/CostBasedOptimizerAndDatabaseStatistics.php
4、基于时间点恢复先前版本的统计信息：DBMS_STATS.RESTORE_*_STATS
5、LOCK/UNLOCK统计信息：
      DBMS_STATS.LOCK(UNLOCK)_TABLE(SCHEMA)_STATS

基于动态采样方式进行统计信息估算OPTIMIZER_DYNAMIC_SAMPLING
        The purpose of dynamic sampling is to improve server performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes. The statistics for tables and indexes include table block counts, applicable index block counts, table cardinalities, and relevant join column statistics. These more accurate estimates allow the optimizer to produce better performing plans.

统计信息丢失时的默认值
Default Table Values When Statistics Are Missing

Table Statistic	Default Value Used by Optimizer
Cardinality	num_of_blocks * (block_size - cache_layer) / avg_row_len
Average row length	100 bytes
Number of blocks	100 or actual value based on the extent map
Remote cardinality	2000 rows
Remote average row length	100 bytes

 
Default Index Values When Statistics Are Missing

Index Statistic	Default Value Used by Optimizer
Levels	1
Leaf blocks	25
Leaf blocks/key	1
Data blocks/key	1
Distinct keys	100
Clustering factor	800

查看统计信息：
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS

查看直方图：
1、
Select column_name, num_distinct, num_buckets, histogram from USER_TAB_COL_STATISTICS;
Select endpoint_number, endpoint_value from USER_HISTOGRAMS;
Select endpoint_number, endpoint_value from USER_TAB_HISTOGRAMS;
2、判断是Height-Balanced Histogram Statistics还是Frequency Histogram Statistics，看USER_TAB_COL_STATISTICS.HISTOGRAM的值


3、