[20180322]查看统计信息的保存历史.txt
--//链接:http://www.itpub.net/thread-2100595-1-1.html
--//提到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.