统计信息是个非常有用的东东，没有它，SQL优化器就好比巧妇难为无米之炊！良好高效的SQL执行计划依赖于真实的统计信息。然而在有些情况下，比如对比生产环境与测试环境执行计划，需要使用生产环境的统计信息。而有时候呢则需要还原Oracle历史统计信息。本文基于后者即如何还原历史统计信息来展开，同时描述了11g缺省情况下对于统计信息的调度。
有关统计信息的导入导出可以参考：
dbms_stats 导入导出 schema 级别统计信息
dbms_stats 导入导出表统计信息
1、演示环境
sys@MMBO> select * from v$version where rownum<2;
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
--查看schema HR上对象的最后analyze 的时间(注，为简化页面，部分输出行省略，下同)
sys@MMBO> select table_name,last_analyzed from dba_tables where owner='HR';
TABLE_NAME LAST_ANALYZED
------------------------------ -----------------
REGIONS 20130815 18:03:55
LOCATIONS 20130815 18:03:55
DEPARTMENTS 20130815 18:03:56
JOBS 20130815 18:03:56
--创建用于存放导出统计信息的表STATS_TABLE
sys@MMBO> exec dbms_stats.create_stat_table('HR', 'STATS_TABLE');
PL/SQL procedure successfully completed.
--导出schema HR此时的统计信息
sys@MMBO> exec dbms_stats.export_schema_stats('HR','STATS_TABLE','HR');
PL/SQL procedure successfully completed.
-- Author : Leshami
-- Blog : http://blog.csdn.net/leshami
--此时收集整个schema的统计信息
sys@MMBO> exec dbms_stats.gather_schema_stats('HR');
PL/SQL procedure successfully completed.
--收集之后，对象的LAST_ANALYZED变为20140307
sys@MMBO> select table_name,last_analyzed from dba_tables where owner='HR';
TABLE_NAME LAST_ANALYZED
------------------------------ -----------------
STATS_TABLE 20140307 10:26:31
REGIONS 20140307 10:26:30
LOCATIONS 20140307 10:26:30
DEPARTMENTS 20140307 10:26:29
2、统计信息保留的时效性及可用性
--可以保留31天以内统计信息
sys@MMBO> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
--最久的历史可用统计信息为03-FEB-14
sys@MMBO> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
03-FEB-14 10.28.31.948055000 PM +08:00
3、还原历史统计信息
--使用下面的过程来还原历史统计信息，注意以下演示的是还原schema级别的历史统计信息
sys@MMBO> exec dbms_stats.restore_schema_stats('HR',sysdate-1);
PL/SQL procedure successfully completed.
--查看还原之后scheme HR上对象的LAST_ANALYZED时间，这个与收集统计信息之前是一致的
--尽管我们指定了sysdate-1，但实际上昨天的历史统计信息的最后analyzed 也是20130815，也就是说很久没有analyze过了
sys@MMBO> select table_name,last_analyzed from dba_tables where owner='HR';
TABLE_NAME LAST_ANALYZED
------------------------------ -----------------
STATS_TABLE 20140307 10:26:31
REGIONS 20130815 18:03:55
LOCATIONS 20130815 18:03:55
DEPARTMENTS 20130815 18:03:56
JOBS 20130815 18:03:56
--接下来我们尝试导入之前备份的统计信息
sys@MMBO> exec dbms_stats.import_schema_stats('HR','STATS_TABLE','HR');
PL/SQL procedure successfully completed.
sys@MMBO> select table_name,last_analyzed from dba_tables where owner='HR';
TABLE_NAME LAST_ANALYZED
------------------------------ -----------------
STATS_TABLE 20140307 10:26:31
REGIONS 20130815 18:03:55
LOCATIONS 20130815 18:03:55
DEPARTMENTS 20130815 18:03:56
JOBS 20130815 18:03:56
--再次收集统计信息
sys@MMBO> exec dbms_stats.gather_schema_stats('HR');
PL/SQL procedure successfully completed.
--此时统计信息的时间被刷新到10:36
sys@MMBO> select table_name,last_analyzed from dba_tables where owner='HR';
TABLE_NAME LAST_ANALYZED
------------------------------ -----------------
STATS_TABLE 20140307 10:36:48
REGIONS 20140307 10:36:48
LOCATIONS 20140307 10:36:47
--此时我们指点时间点来还原历史统计信息
sys@MMBO> exec dbms_stats.restore_schema_stats('HR','07-MAR-14 10:26:32AM');
PL/SQL procedure successfully completed.
sys@MMBO> select table_name,last_analyzed from dba_tables where owner='HR';
TABLE_NAME LAST_ANALYZED
------------------------------ -----------------
STATS_TABLE 20140307 10:26:31
REGIONS 20140307 10:26:30
LOCATIONS 20140307 10:26:30
--接下来我们直接使用sysdate来还原整个schema
sys@MMBO> exec dbms_stats.restore_schema_stats('HR',sysdate);
PL/SQL procedure successfully completed.
--根据下面的这个查询可知，使用sysdate参数，缺省的会还原到当天最早收集统计信息的那一次
sys@MMBO> select table_name,last_analyzed from dba_tables where owner='HR';
TABLE_NAME LAST_ANALYZED
------------------------------ -----------------
STATS_TABLE 20140307 10:26:31
REGIONS 20140307 10:26:30
LOCATIONS 20140307 10:26:30
--再次通过指定时间点来进行还原
sys@MMBO> exec dbms_stats.restore_schema_stats('HR','07-MAR-14 10:36:50AM');
PL/SQL procedure successfully completed.
--此时统计信息被还原到最新
sys@MMBO> select table_name,last_analyzed from dba_tables where owner='HR';
TABLE_NAME LAST_ANALYZED
------------------------------ -----------------
STATS_TABLE 20140307 10:36:48
REGIONS 20140307 10:36:48
LOCATIONS 20140307 10:36:47
4、调度统计信息(11g)
缺省情况下，Oracle为我们定义了收集统计信息的scheduler，下面列出来在Oracle 11g配置的关于自动收集统计信息的scheduler。
下文调用的SQL脚本来在Oracle 性能诊断一书
sys@MMBO> @dbms_stats_job_11g
sys@MMBO>
sys@MMBO> SELECT task_name, status
2 FROM dba_autotask_task
3 WHERE client_name = 'auto optimizer stats collection';
TASK_NAME STATUS
----------------- -------
gather_stats_prog ENABLED
sys@MMBO>
sys@MMBO> PAUSE
sys@MMBO>
sys@MMBO> SELECT program_action, number_of_arguments, enabled
2 FROM dba_scheduler_programs
3 WHERE owner = 'SYS'
4 AND program_name = 'GATHER_STATS_PROG';
PROGRAM_ACTION NUMBER_OF_ARGUMENTS ENABLED
----------------------------------------- ------------------- -------
dbms_stats.gather_database_stats_job_proc 0 TRUE
sys@MMBO>
sys@MMBO> PAUSE
sys@MMBO>
sys@MMBO> SELECT window_group
2 FROM dba_autotask_client
3 WHERE client_name = 'auto optimizer stats collection';
WINDOW_GROUP
--------------
ORA$AT_WGRP_OS
sys@MMBO>
sys@MMBO> PAUSE
sys@MMBO> --以下是系统默认的调度频率
sys@MMBO> SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
2 FROM dba_autotask_window_clients c, dba_scheduler_windows w
3 WHERE c.window_name = w.window_name
4 AND c.optimizer_stats = 'ENABLED';
WINDOW_NAME REPEAT_INTERVAL DURATION ENABLED
---------------- ------------------------------------------ ------------- -------
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; +000 04:00:00 TRUE
bysecond=0
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; +000 20:00:00 TRUE
bysecond=0
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; +000 04:00:00 TRUE
bysecond=0
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; +000 04:00:00 TRUE
bysecond=0
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; +000 20:00:00 TRUE
bysecond=0
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; +000 04:00:00 TRUE
bysecond=0
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; +000 04:00:00 TRUE
bysecond=0
更多参考
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
