[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.
--可以对比以上的信息是吻合的.
