[20151226]統計資訊的儲存時間.txt

[20151226]統計資訊的儲存時間.txt

--前一陣子寫了一篇審計統計分析的文章
--連結:http://blog.itpub.net/267265/viewspace-1870823/

--裡面提到:檢視記錄了分析的歷史記錄DBA_TAB_STATS_HISTORY;,我提到儲存1個月.

--可以查詢SYS.OPTSTAT_HIST_CONTROL$檢視.

SYS@test> select sname,sval1,sval2 from SYS.OPTSTAT_HIST_CONTROL$ where sname='STATS_RETENTION';
SNAME                               SVAL1 SVAL2
------------------------------ ---------- ---------------------------
STATS_RETENTION                        31 2013-06-28 09:16:09.302000

--透過如下可以獲得最早的統計歷史.

SYS@test> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
-------------------------------
2015-11-25 21:48:36.585000000

--透過如下也可以查詢到資訊:
SYS@test> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
                         31

--透過如下命令修改儲存時間:

SYS@test> EXEC dbms_stats.alter_stats_history_retention(60);
PL/SQL procedure successfully completed.

SYS@test> select sname,sval1,sval2 from SYS.OPTSTAT_HIST_CONTROL$ where sname='STATS_RETENTION';
SNAME                               SVAL1 SVAL2
------------------------------ ---------- ---------------------------
STATS_RETENTION                        60 2015-12-26 22:52:44.223000

--可以透過如下類似的命令還原統計資訊:
exec dbms_stats.restore_table_stats(user,'T3','2015-07-31 22:25:33');

--一直有一個疑問,就是歷史的統計資訊儲存在哪裡呢? 也是別人問的問題:

SYS@test01p> select * from DBA_TAB_STATS_HISTORY where owner='SCOTT' and table_name='DEPT';
OWNER  TABLE_NAME STATS_UPDATE_TIME
------ ---------- ----------------------------
SCOTT  DEPT       2015-11-27 22:20:15.599000
SCOTT  DEPT       2015-12-26 22:58:49.409000

--表分析了2次.仔細檢視可以發現統計分析儲存在這裡:

WRI$_OPTSTAT_HISTHEAD_HISTORY
WRI$_OPTSTAT_HISTGRM_HISTORY

--這些統計資訊對了解當時的統計資訊很有幫助.

SELECT savtime,
       distcnt,
       density,
       lowval,
       hival,
       timestamp#
  FROM sys.WRI$_OPTSTAT_HISTHEAD_HISTORY
WHERE obj# IN (SELECT object_id
                  FROM dba_objects
                 WHERE owner = 'SCOTT' AND object_name = 'DEPT');

SYS@test01p> /
SAVTIME                         DISTCNT    DENSITY LOWVAL               HIVAL                TIMESTAMP#
---------------------------- ---------- ---------- -------------------- -------------------- -------------------
2015-11-27 22:20:15.600000            4        .25 C10B                 C129                 2015-07-04 19:15:10
2015-11-27 22:20:15.600000            4        .25 4143434F554E54494E47 53414C4553           2015-07-04 19:15:10
2015-11-27 22:20:15.600000            3       .125 4348494341474F       4E455720594F524B     2015-07-04 19:15:10
2015-12-26 22:58:49.418000            4        .25 C10B                 C129                 2015-11-27 22:20:15
2015-12-26 22:58:49.418000            4        .25 4143434F554E54494E47 53414C4553           2015-11-27 22:20:15
2015-12-26 22:58:49.418000            3       .125 4348494341474F       4E455720594F524B     2015-11-27 22:20:15
6 rows selected.

--可以對比以上的資訊是吻合的.