[20151226]统计信息的保存时间.txt

[20151226]统计信息的保存时间.txt

--前一阵子写了一篇审计统计分析的文章

--里面提到:视图记录了分析的历史记录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.

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.

--可以对比以上的信息是吻合的.