[20180322]查看统计信息的保存历史.txt

--//链接:

--//提到sysaux表空间暴涨.

select owner, segment_name, bytes/1024/1024/1024

from dba_segments   where tablespace_name = 'SYSAUX'   order by bytes/1024/1024/1024  desc;

OWNER SEGMENT_NAME                     BYTES/1024/1024/1024

----- -------------------------------- --------------------- SYS     WRI$_OPTSTAT_HISTGRM_HISTORY   0.896484375 SYS     I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 0.60003662109375 SYS     I_WRI$_OPTSTAT_H_ST            0.5478515625

--//很明显这些信息是保存直方图信息历史的信息.突然想不起来那个表记录这些信息保存多长时间.

--//看了看文档做一个记录:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select dbms_stats.get_stats_history_retention() N10 ,dbms_stats.get_stats_history_availability c40 from dual ;

                  N10 C40 --------------------- ----------------------------------------                    31 2018-02-19 09:40:29.768301000 +08:00

--//一些统计的缺省值保存在sys.OPTSTAT_HIST_CONTROL$.

SCOTT@book> select * from sys.OPTSTAT_HIST_CONTROL$; SNAME                               SVAL1 SVAL2                             SPARE1 SPARE2 SPARE3 SPARE4                      SPARE5 SPARE6 ------------------------------ ---------- --------------------------------- ------ ------ ------ --------------------------- ------ ------- SKIP_TIME                                 2018-02-19 09:40:29.768301 +08:00 STATS_RETENTION                        31 2013-08-24 11:42:59.378542 -07:00      1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ TRACE                                     2013-08-24 11:42:59.378542 -07:00      1               0 DEBUG                                     2013-08-24 11:42:59.378542 -07:00      1               0 SYS_FLAGS                                 2016-12-12 22:00:00.816608 +08:00                      1 APPROXIMATE_NDV                           2013-08-24 11:42:59.378542 -07:00      1               TRUE CASCADE                                   2013-08-24 11:42:59.378542 -07:00      1               DBMS_STATS.AUTO_CASCADE ESTIMATE_PERCENT                          2013-08-24 11:42:59.378542 -07:00      1               DBMS_STATS.AUTO_SAMPLE_SIZE DEGREE                                    2013-08-24 11:42:59.378542 -07:00      1               NULL METHOD_OPT                                2013-08-24 11:42:59.378542 -07:00      1               FOR ALL COLUMNS SIZE AUTO NO_INVALIDATE                             2013-08-24 11:42:59.378542 -07:00      1               DBMS_STATS.AUTO_INVALIDATE GRANULARITY                               2013-08-24 11:42:59.378542 -07:00      1               AUTO PUBLISH                                   2013-08-24 11:42:59.378542 -07:00      1               TRUE STALE_PERCENT                             2013-08-24 11:42:59.378542 -07:00      1               10 INCREMENTAL                               2013-08-24 11:42:59.378542 -07:00      1               FALSE INCREMENTAL_INTERNAL_CONTROL              2013-08-24 11:42:59.378542 -07:00      1               TRUE AUTOSTATS_TARGET                          2013-08-24 11:42:59.378542 -07:00      1               AUTO CONCURRENT                                2013-08-24 11:42:59.378542 -07:00      1               FALSE TABLE_CACHED_BLOCKS                       2013-08-24 11:42:59.378542 -07:00      1               1 19 rows selected.