oracle 统计信息

/*

环境：oracle 11.2 for windows

*/

统计信息描述了数据库及其对象的具体信息，查询优化器根据这些信息为sql语句选择最佳的执行计划，若统计信息不准确，可能导致错误的执行计划，此适用于cbo优化器，rbo不使用统计信息。

统计信息包括以下内容

 

表信息

Number of rows  --行数

Number of blocks  --块数

Average row length --行平均长度

字段信息

Number of distinct values (NDV) in column --列中唯一值数量

Number of nulls in column  --NULL值的数量

Data distribution (histogram)  --数据分布

Extended statistics

索引信息

Number of leaf blocks --叶块数量

Levels --等级

Clustering factor --聚簇因子

系统信息

I/O performance and utilization --I/O性能与使用率

CPU performance and utilization --CPU性能与使用率

统计信息存储在数据字典中，oracle 10g会自动收集统计信息，也可以通过anlanze 和 dbms_stats 包手动收集统计信息，同时也可以导出或锁定统计信息。

1.管理统计信息的自动收集

GATHER_STATS_JOB

GATHER_STATS_PROG

SELECTowner,job_name,state,last_start_date,last_run_duration,failure_countFROM dba_scheduler_jobs WHERE job_name = 'GATHER_STATS_JOB';

selectPROGRAM_NAME,PROGRAM_ACTION,ENABLEDfrom dba_scheduler_programs where PROGRAM_NAME = 'GATHER_STATS_PROG' ;

SELECTlog_id, job_name, status,

          TO_CHAR (log_date,'YYYY-MM-DD HH24:MI') log_date

    FROM dba_scheduler_job_run_details

   WHEREjob_name ='GATHER_STATS_JOB'ORDERBY log_date;

oracle收集那些表的统计信息

1.没有统计信息的表

2.更新超过10%的表

oracle 收集腐旧统计信息时需要用到一个modification monitoring的特性，当此特性disable时，不能收集腐旧的统计信息，STATISTICS_LEVEL为typical或all时此特性生效，默认为typical

2.手动管理统计信息

收集统计信息可以用dbms_stats 包，这个包也用于修改、查看、导入导出和删除统计信息。新收集的统计将替换旧的统计信息，旧的统计信息将被保存，以便用于恢复

 

    Procedure

Collects

GATHER_INDEX_STATS

Index statistics

GATHER_TABLE_STATS

Table, column, and index statistics

GATHER_SCHEMA_STATS

Statistics for all objects in a schema

GATHER_DICTIONARY_STATS

Statistics for all dictionary objects

GATHER_DATABASE_STATS

Statistics for all objects in a database

GATHER_DICTIONARY_STATES

Statistics for all system schema

3.锁定统计信息

统计信息被锁定后不可修改，用于保证统计信息的不可变，使执行计划固定

加锁：LOCK_SCHEMA_STATS and LOCK_TABLE_STATS

解锁：UNLOCK_SCHEMA_STATS and UNLOCK_TABLE_STATS

4.统计信息的删除

DBMS_STATS.DELETE_TABLE_STATS

5.统计信息的导入导出

新建一个表用于导出

DBMS_STATS.CREATE_STAT_TABLE

导出

DBMS_STATS.EXPORT_*_STATS

导入

DBMS_STATS.IMPORT_*_STATS

6.统计信息的恢复

统计信息会自动保存，用于恢复之前版本

视图DBA_OPTSTAT_OPERATIONS 储存了schema 和 database 级的DBMS_STATS操作

视图*_TAB_STATS_HISTORY（all，dba，user）存储对表的修改历史

PURGE_STATS：手动清除超过时间戳的旧版本。

GET_STATS_HISTORY_RETENTION：获取当前的统计历史保留价值。

GET_STATS_HISTORY_AVAILABILITY：获得最早可用统计版本的时间戳。

ANALYZE命令不能存储旧的统计信息

RESTORE 利用时间戳恢复到时间戳时的统计信息

7.统计的恢复与导入导出的对比

恢复使用的场景：

（1）想要回退到之前的执行计划

（2）删除旧的统计信息

导入导出使用场景

（1）想保留比自动保存更久的统计信息

（2）数据库迁移

（3）保存多组数据，用于反复测试