Oracle还原历史统计信息 - 数据库编程

> 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还原历史统计信息(二)