[20180322]檢視統計資訊的儲存歷史.txt

[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.