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

SELECT owner,job_name,state,last_start_date,last_run_duration,failure_count FROM dba_scheduler_jobs WHERE job_name = 'GATHER_STATS_JOB' ;

select PROGRAM_NAME,PROGRAM_ACTION, ENABLED from dba_scheduler_programs where PROGRAM_NAME = 'GATHER_STATS_PROG' ;

SELECT log_id, job_name, status,

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

     FROM dba_scheduler_job_run_details

    WHERE job_name = 'GATHER_STATS_JOB' ORDER BY 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）保存多组数据，用于反复测试