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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

undo retention的思考(一)

最近有个网友咨询我一个问题,是关于undo_retention的,对于这个参数没有过多关注,只是知道需要设置undo_retention搭配使用undotabl...

49550
来自专栏杨建荣的学习笔记

一条看似平常的报警邮件所做的分析(r8笔记第9天)

今天留意到一封报警邮件。内容如下: ZABBIX-监控系统: ------------------------------------ 报警内容: CPU u...

26240
来自专栏数据和云

从商用到开源:15个维度,全面剖析DB2与MySQL数据库的差异

编辑手记 MySQL是目前最流行的开源数据库,由于其部署方便,运维简单,被广泛用于互联网的各个领域。随着整体IT架构的变更,传统的金融,电信业务,也逐渐走上从商...

70270
来自专栏乐沙弥的世界

SQL*PLus 帮助手册(SP2-0171)

    对于经常在SQL*Plus 下工作的大师们而言,总是时不时查询SQL*Plus的帮助命令。着实太多了,记不住。SQL*Plus下直接提供了help命令来...

36730
来自专栏数据和云

举一反三-分区裁剪作用的“新”发现

作者介绍 ? 赵勇 云和恩墨北区技术工程师 专注于SQL审核和优化相关工作。曾经服务的客户涉及金融保险、电信运营商、政府、生产制造等行业。 分区裁剪的定义 分区...

301100
来自专栏沃趣科技

MVCC原理探究及MySQL源码实现分析

目录预览 数据库多版本读场景 MVCC实现原理 1、通过DB_ROLL_PT 回溯查找数据历史版本 2、通过read view判断行...

68380
来自专栏企鹅号快讯

带你认识一下mysql中数据库information

information_schema 大家在安装或使用MYSQL时,会发现除了自己安装的数据库以外,还有一个information_schema数据库。 inf...

23080
来自专栏乐沙弥的世界

Oracle 表空间与数据文件

SYSAUX --->10g 高并发系统繁忙时,会造成system争用,将工具放到SYSAUX,减轻system的压力,SYSAUX不影响系统(影响性能)

20350
来自专栏idba

insert 语句加锁机制

之前的文章里面总结了很多死锁案例,其实里面有几篇文章对于insert加锁流程表述的不准确,而且微信公众号又无法修改,所以通过本文重新梳理insert...

15630
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(71)-微信公众平台开发-公众号管理

思维导图 下面我们来看一个思维导图,这样就可以更快了解所需要的功能: ? 上一节我们利用了一个简单的代码例子,完成了与微信公众号的对话(给公众号发一条信息...

47670

扫码关注云+社区

领取腾讯云代金券