Oracle 还原历史统计信息

      统计信息是个非常有用的东东，没有它，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

更多参考

DML Error Logging 特性 

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划