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:55LOCATIONS                      20130815 18:03:55DEPARTMENTS                    20130815 18:03:56JOBS                           20130815 18:03:56--建立用於存放匯出統計資訊的表STATS_TABLEsys@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變為20140307sys@MMBO> select table_name,last_analyzed from dba_tables where owner='HR';TABLE_NAME                     LAST_ANALYZED------------------------------ -----------------STATS_TABLE                    20140307 10:26:31REGIONS                        20140307 10:26:30LOCATIONS                      20140307 10:26:30DEPARTMENTS                    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:31REGIONS                        20130815 18:03:55LOCATIONS                      20130815 18:03:55DEPARTMENTS                    20130815 18:03:56JOBS                           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:31REGIONS                        20130815 18:03:55LOCATIONS                      20130815 18:03:55DEPARTMENTS                    20130815 18:03:56JOBS                           20130815 18:03:56--再次收集統計資訊sys@MMBO> exec dbms_stats.gather_schema_stats('HR');PL/SQL procedure successfully completed.--此時統計資訊的時間被重新整理到10:36sys@MMBO> select table_name,last_analyzed from dba_tables where owner='HR';TABLE_NAME                     LAST_ANALYZED------------------------------ -----------------STATS_TABLE                    20140307 10:36:48REGIONS                        20140307 10:36:48LOCATIONS                      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:31REGIONS                        20140307 10:26:30LOCATIONS                      20140307 10:26:30--接下來我們直接使用sysdate來還原整個schemasys@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:31REGIONS                        20140307 10:26:30LOCATIONS                      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:48REGIONS                        20140307 10:36:48LOCATIONS                      20140307 10:36:47

4、調度統計資訊(11g)

預設情況下，Oracle為我們定義了收集統計資訊的scheduler，下面列出來在Oracle 11g配置的關於自動收集統計資訊的scheduler。下文調用的SQL指令碼來在Oracle 效能診斷一書sys@MMBO> @dbms_stats_job_11gsys@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 ENABLEDsys@MMBO> sys@MMBO> PAUSEsys@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 TRUEsys@MMBO> sys@MMBO> PAUSEsys@MMBO> sys@MMBO> SELECT window_group  2  FROM dba_autotask_client  3  WHERE client_name = 'auto optimizer stats collection';WINDOW_GROUP--------------ORA$AT_WGRP_OSsys@MMBO> sys@MMBO> PAUSEsys@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=0SATURDAY_WINDOW  freq=daily;byday=SAT;byhour=6;byminute=0;  +000 20:00:00 TRUE                 bysecond=0THURSDAY_WINDOW  freq=daily;byday=THU;byhour=22;byminute=0; +000 04:00:00 TRUE                  bysecond=0TUESDAY_WINDOW   freq=daily;byday=TUE;byhour=22;byminute=0; +000 04:00:00 TRUE                  bysecond=0SUNDAY_WINDOW    freq=daily;byday=SUN;byhour=6;byminute=0;  +000 20:00:00 TRUE                 bysecond=0MONDAY_WINDOW    freq=daily;byday=MON;byhour=22;byminute=0; +000 04:00:00 TRUE                  bysecond=0FRIDAY_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語句執行計畫