/*
环境: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)保存多组数据,用于反复测试