程序師世界是廣大編程愛好者互助、分享、學習的平台，程序師世界有你更精彩！
設為首頁 加入收藏
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle還原歷史統計信息

Oracle還原歷史統計信息

編輯：Oracle教程

統計信息是個非常有用的東東，沒有它，SQL優化器就好比巧婦難為無米之炊！良好高效的SQL執行計劃依賴於真實的統計信息。然而在有些情況下，比如對比生產環境與測試環境執行計劃，需要使用生產環境的統計信息。而有時候呢則需要還原Oracle歷史統計信息。本文基於後者即如何還原歷史統計信息來展開，同時描述了11g缺省情況下對於統計信息的調度。

有關統計信息的導入導出可以參考：

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 聯合數組與嵌套表

SQL tuning 步驟

高效SQL語句必殺技

父游標、子游標及共享游標

綁定變量及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計劃中各字段各模塊描述

使用 EXPLAIN PLAN 獲取SQL語句執行計劃

  1. 上一頁:
  2. 下一頁:
Oracle教程
ORACLE基本SQL語句-用戶及建表篇，oraclesql語句

ORACLE基本SQL語句-用戶及建表篇，oraclesql
Oracle基本數據改變原理淺析（redo與undo）--oracle核心技術讀書筆記一

Oracle基本數據改變原理淺析（redo與undo）--o
OracleROWID詳解

1.ROWID定義 ROWID：數據庫中行的全局唯一地
Oracle Database In-Memory:突破行列限制 達到實時分析

Oracle Database In-Memory:突破行列
ORA-12541:TNS:無監聽程序

ORA-12541:TNS:無監聽程序 我昨天重新安裝O
ORA-01591:lockheldbyin-doubtdistributedtransaction問題解決

ORA-01591:lockheldbyin-doubtdi
相關文章
閱讀排行榜
Oracle中簡單查詢、限定查詢、數據排序SQL語句范例和詳細注解 Linux環境上，Oracle常用命令，oracle常用命令 Oracle查詢客戶端編碼集 Oracle數據庫，忽略大小寫Like模糊查詢(SQL Server,MySql原理相同)，oracle模糊查詢 [轉]ORACLE DBA TRANSACTIONS，dbatransactions Oracle數據庫之中數據安全完全分析詳解 GoldenGate進程abend，報錯為OGG-00868ORA-02396:ExceededMaximumIdleTime,PleaseConnectAgain Oracle學習筆記18--存儲函數和存儲過程(PL/SQL子程序) Oracle監聽器服務不能啟動的解決方法，oracle監聽器 Oracle數據訪問其他Oracle數據庫數據 oracle表連接-----)哈希連接(HashJoin)
熱門圖文
Oracle性能優化之診斷latch競爭 使用mysql命令行導出sql Sybase數據庫技術（4） 《PHP與MySQL程序設計（第3版）》 Mysql¸ß¼¶Ö®Ö÷´Ó¸´ÖÆ (轉)Amoeba for MySQL 非常好用的mysql集群軟件 Microsoft Sql server2005的安裝步驟圖文詳解及常見問題解決方案 用javabean操作sqlserver2005
欄目導航
關於Oracle數據庫Oracle數據庫基礎Oracle教程
Copyright © 程式師世界 All Rights Reserved