`
阿尔萨斯
  • 浏览: 4184115 次
社区版块
存档分类
最新评论

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

Oracle&nbsp;牛鹏社 Oracle DBsupport

更多参考

DMLError 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语句执行计划

分享到：
| 建设网络强国，反制黑客入侵
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle10g课堂练习I（2）

    前言 1 简介 课程目标 1-2 建议日程表 1-3 课程目标 1-4 ...SQL 优化指导： SQL 统计信息 13-14 SQL 优化指导：确定重复的 SQL 13-15 使用 SQL 访问指导 13-16 管理内存组件 13-18 。。。。

    万能后台管理模板javaweb

    27. 数据库还原：历史备份记录，还原数据库 or 单表 ，统计备份时间和文件大小 28. SQL编辑器：强大的SQL编辑器，支持编辑语句复杂查询语句，生成动态报表，可导出excel ------------------------------------------...

    javaOA办公系统模块设计方案.pdf

    数据库还原：历史备份记录，还原数据库 or 单表(sqlserver不⽀持)，统计备份时间和⽂件⼤⼩ 22. SQL编辑器：强⼤的SQL编辑器，⽀持编辑语句复杂查询语句，⽣成动态报表，可导出excel ----------------------------...

    vc++ 应用源码包_1

    实现了 清楚internet临时文件、Cookie的清除、游览器地址栏历史地址的清除、清楚表单自动完成历史记录、清楚自动密码历史记录、清除收藏夹中的内容、清除RAS自动拨号历史记录、清除系统临时文件夹、清空回收站、清除...

    vc++ 应用源码包_2

    实现了 清楚internet临时文件、Cookie的清除、游览器地址栏历史地址的清除、清楚表单自动完成历史记录、清楚自动密码历史记录、清除收藏夹中的内容、清除RAS自动拨号历史记录、清除系统临时文件夹、清空回收站、清除...

    vc++ 应用源码包_6

    实现了 清楚internet临时文件、Cookie的清除、游览器地址栏历史地址的清除、清楚表单自动完成历史记录、清楚自动密码历史记录、清除收藏夹中的内容、清除RAS自动拨号历史记录、清除系统临时文件夹、清空回收站、清除...

    vc++ 应用源码包_5

    实现了 清楚internet临时文件、Cookie的清除、游览器地址栏历史地址的清除、清楚表单自动完成历史记录、清楚自动密码历史记录、清除收藏夹中的内容、清除RAS自动拨号历史记录、清除系统临时文件夹、清空回收站、清除...

    vc++ 应用源码包_3

    实现了 清楚internet临时文件、Cookie的清除、游览器地址栏历史地址的清除、清楚表单自动完成历史记录、清楚自动密码历史记录、清除收藏夹中的内容、清除RAS自动拨号历史记录、清除系统临时文件夹、清空回收站、清除...

    C#.net_经典编程例子400个

    第1章　窗体与界面设计　1 1.1　菜单应用实例　2 实例001　带历史信息的菜单　2 实例002　菜单动态合并　3 实例003　像开始菜单一样漂亮的菜单　4 实例004　任务栏托盘菜单　5 实例005　可以拉伸...

    C#程序开发范例宝典（第2版）.part13

    实例001 带历史信息的菜单 2 实例002 菜单动态合并 3 实例003 像开始菜单一样漂亮的菜单 4 实例004 任务栏托盘菜单 4 实例005 可以拉伸的菜单界面 5 实例006 级联菜单 6 1.2 工具栏设计 6 实例007 带背景的...

    C#程序开发范例宝典（第2版）.part08

    实例001 带历史信息的菜单 2 实例002 菜单动态合并 3 实例003 像开始菜单一样漂亮的菜单 4 实例004 任务栏托盘菜单 4 实例005 可以拉伸的菜单界面 5 实例006 级联菜单 6 1.2 工具栏设计 6 实例007 带背景的...

    C#程序开发范例宝典（第2版）.part02

    实例001 带历史信息的菜单 2 实例002 菜单动态合并 3 实例003 像开始菜单一样漂亮的菜单 4 实例004 任务栏托盘菜单 4 实例005 可以拉伸的菜单界面 5 实例006 级联菜单 6 1.2 工具栏设计 6 实例007 带背景的...

    C#程序开发范例宝典（第2版）.part12

    实例001 带历史信息的菜单 2 实例002 菜单动态合并 3 实例003 像开始菜单一样漂亮的菜单 4 实例004 任务栏托盘菜单 4 实例005 可以拉伸的菜单界面 5 实例006 级联菜单 6 1.2 工具栏设计 6 实例007 带背景的...

Magicbox
Global site tag (gtag.js) - Google Analytics